Calculating Daily Active Users in SQL
Metric definition plus dedup SQL.
Need per-event user_id and timestamp and a clear active definition; count distinct user_id within the day in a fixed timezone.
Counting rows or fuzzy date-boundary and timezone handling.
WHAT THIS TESTS Whether you nail the definition of a deceptively simple metric and write SQL that counts each user once with correct date boundaries.
A GOOD ANSWER COVERS First define active: usually any user who fired at least one qualifying event that day, and you might exclude passive pings or background events. The raw data needed is minimal: an events table with a user_id and an event timestamp, optionally an event_name to filter what counts as activity. DAU for a given day is the count of distinct users whose events fall within that day. Two correctness details dominate. One, count distinct users, not rows, since a user generates many events per day. Two, handle the date boundary and timezone explicitly: use a half-open interval, timestamp greater than or equal to the day start and less than the next day start, in a declared timezone, rather than BETWEEN which can include both midnights and double count.
PSEUDO-SQL SELECT COUNT(DISTINCT user_id) AS dau FROM events WHERE event_timestamp >= 2026-01-15 00:00:00 AND event_timestamp < 2026-01-16 00:00:00. To produce a DAU trend, SELECT DATE_TRUNC(day, event_timestamp) AS day, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY day ORDER BY day, again being explicit about timezone in DATE_TRUNC.
COMMON WRONG ANSWERS COUNT(*) which counts events not users. Using BETWEEN with the same midnight on both ends, which overlaps boundaries. Leaving timezone implicit so UTC versus local shifts the count. Forgetting to define what active means, conflating logins with any event.
LIKELY FOLLOW-UPS How would you compute the DAU over MAU stickiness ratio? How do you count distinct efficiently at scale, for example with HyperLogLog approximations? How to handle anonymous users?
ONE CONCRETE EXAMPLE For January 15, the query counts distinct user_ids with events in the half-open day window in your reporting timezone, returning 42,310 even though those users produced 600,000 events, because each user is collapsed to one via COUNT(DISTINCT).
Interview question
Why is COUNT(DISTINCT user_id) with a half-open date range the correct way to compute DAU rather than COUNT(*) with BETWEEN?
- a.BETWEEN cannot be used with timestamp columns at all
- b.COUNT(DISTINCT user_id) automatically converts timezones
- c.DISTINCT counts each user once and a half-open range avoids double counting the midnight boundaryCorrect
- d.COUNT(*) is slower so DISTINCT is chosen for performance
Why? this is the answer
Users fire many events, so DISTINCT collapses them to one, and a half-open interval prevents counting both midnights. The issue is correctness not speed, BETWEEN works syntactically, and DISTINCT does no timezone conversion.
Just read this? Test yourself on what you have been reading.
Read the original → popsql.com
- #sql
- #dau
- #active-users
- #metrics
- #analytics
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on sql — each one lists the topics its interview covers.
See open roles