Calculating Daily Active Users in SQL

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).