Calculate Daily Active Users (DAU) with SQL
This tests product sense and SQL fundamentals. Define 'active' with a core product action, describe the event data needed, then write a COUNT(DISTINCT user_id) query. A red flag is writing SQL before defining the business logic for 'active'.
What's really being asked
This question isn't just about SQL. It's a test of your product thinking and ability to connect business metrics to underlying data. The interviewer wants to see if you start with 'why' (what does 'active' mean for this specific product?) before jumping to 'how' (the SQL query). They are evaluating your ability to handle ambiguity, define requirements, and demonstrate business acumen.
The full answer
A great answer has three parts, in order. First, it starts by clarifying the definition of 'active.' You should state that 'active' is business-dependent and propose a concrete definition for a hypothetical product, like 'a user who performs the Edit Canvas action' for a collaboration tool. Second, you describe the necessary raw data: an events table with at least a unique user identifier (user_id), a precise event timestamp, and an event name. Third, you write the SQL query. The query must correctly filter for the defined active event, count only unique users with COUNT(DISTINCT user_id), and group the results by day using a function like DATE_TRUNC.
The mistakes people make
The biggest red flag is jumping immediately into writing a SQL query without discussing the definition of 'active.' This shows a lack of product sense and strategic thinking. Another common mistake is writing a query that double-counts users, for example by using COUNT(user_id) or COUNT(*) instead of COUNT(DISTINCT user_id). A less severe but still weak answer would be forgetting to filter by the specific event name that defines activity, leading to an over-inflated and meaningless count of anyone who generated any event.
What usually comes next
Be ready for 'How would you modify this for Weekly Active Users (WAU) or Monthly Active Users (MAU)?' (Answer: change the DATE_TRUNC argument and time window). Also, 'What if the events table is massive, say 10 billion rows per day? How would you optimize this?' (Answer: discuss pre-aggregation, materialized views, or using specialized analytics databases). Another is 'How would you exclude the current, incomplete day/week from the results?'
A concrete example
For a B2B whiteboarding app, a core value event is editing a canvas. 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 identifies users who performed the key action, ensures they are counted only once per day, and groups the counts by day.
Interview question
When tasked with calculating Daily Active Users (DAU) for a product, which of the following represents the most crucial initial step according to best practices?
- a.Ensuring the events table contains user_id, timestamp, and event_name columns.
- b.Verifying the availability of a pre-aggregated DAU metric in existing dashboards.
- c.Defining what "active" means for the specific product, linking it to a core user action.Correct
- d.Writing a SQL query to count all unique user IDs from the events table for each day.
Why? this is the answer
The card emphasizes that the most crucial first step is to clarify the definition of 'active' based on business logic, as jumping straight to SQL without this definition is a major red flag. While data availability is important, defining 'active' guides the specific data and filters needed.
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