Calculate Daily Active Users (DAU) with SQL
This tests your ability to translate a business metric into a precise technical definition and query. A good answer defines "active," specifies the event data needed (user_id, timestamp, event_name), and uses COUNT(DISTINCT user_id).
What's really being asked
This question tests your product sense and data literacy. The interviewer wants to see if you can move beyond a simple query to critically define a business metric. They're evaluating your ability to translate a vague business need ("how many active users?") into a concrete, defensible technical implementation. It also tests your fundamental SQL skills, specifically aggregation and handling uniqueness.
The full answer
A strong answer has three parts. First, you must clarify the definition of "active." You should state that this varies by company and propose a concrete action, like 'Edit Canvas' for a SaaS tool, as the key event. Second, describe the necessary data schema: an events table with at least a user_id, a timestamp, and an event_name column. Third, write the SQL query. It should filter for the specific event name, use DATE_TRUNC('day', timestamp_column) to group events by day, and crucially, use COUNT(DISTINCT user_id) to count each user only once per day.
The mistakes people make
The biggest red flag is jumping straight to the SQL without defining "active." This shows a lack of business and product acumen. Another common mistake is using COUNT(user_id) instead of COUNT(DISTINCT user_id), which would incorrectly count every single event instead of unique users. A less severe but still weak answer would be to forget to group by day, providing only a total count over a time period. Finally, not specifying the required raw data schema is an omission for a senior candidate.
What usually comes next
Expect questions that push beyond the basic metric. "How would you calculate Monthly Active Users (MAU)?" (Change DATE_TRUNC to 'month'). "How would you calculate the DAU/MAU ratio and what does it tell you?" (It measures stickiness). "What are the limitations of DAU as a metric?" (It's binary and doesn't capture engagement depth). "How would you identify users at risk of churning based on their activity?" (Look for a drop-off in key events).
A concrete example
For a B2B SaaS whiteboarding tool, we'll define "active" as a user who performs an 'Edit Canvas' action. Given an events table with user_id, time, and name columns, the query to find DAU for the last 4 weeks would be: SELECT DATE_TRUNC('day', time), COUNT(DISTINCT user_id) FROM events WHERE name = 'Edit Canvas' AND time > NOW() - INTERVAL '4 weeks' GROUP BY 1 ORDER BY 1;. This query correctly groups by day and counts each user once per day.
Interview question
When calculating Daily Active Users (DAU), what is the primary logical error of using `COUNT(user_id)` instead of `COUNT(DISTINCT user_id)` in a daily grouped query?
- a.The query will fail because COUNT must always be used with DISTINCT on ID columns.
- b.It will undercount active users by only counting their first action of the day.
- c.It incorrectly measures total daily events rather than the number of unique users performing those events.Correct
- d.It fails to account for users who are active on multiple consecutive days.
Why? this is the answer
COUNT(user_id) counts every row (event), so a user who performs an action 5 times is counted 5 times. To get DAU, you must use COUNT(DISTINCT user_id) to count each unique user only once per day.
Just read this? Test yourself on what you have been reading.
Read the original → popsql.com
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
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