SQL and Python

Interview question

seniorlevel
15 minsuggested time
3Data analyst, Data scientist, ML engineer

An analyst's daily-active-users query runs on events(user_id, ts, event_name), a table of about 4 billion rows covering four years and laid out by date of ts (hypothetical sizes):

SELECT date(ts) AS d, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE date(ts) BETWEEN '2026-09-01' AND '2026-09-30'
  AND event_name <> 'heartbeat'
GROUP BY date(ts);

It takes 40 minutes although September is only about 2% of the table. A colleague proposes changing the filter to ts BETWEEN '2026-09-01' AND '2026-09-30'. What is wrong with the original, is the proposed fix correct, and how would you verify the fix and keep the hourly dashboard cheap?

Tests: Predicate sargability and partition pruning, half-open timestamp ranges, verifying a performance fix with the query plan and with the result, and incremental pre-aggregation at a stable grain.

day 7 day 2