Daily Active Users
How many unique users performed at least one search on each day?
- Aggregation
- Date analysis
- Sorting
- Distinct values
Challenge brief
Understand the request
Search Analytics is computing Daily Active User (DAU) metrics for Search to track engagement trends over the measurement period.
Return search_date, dau in the declared deterministic order.
Return
- search_date
- dau (distinct active users)
Constraints
- Count each searching user once per date
- Order chronologically
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
search_queries
query_idINTEGERuser_idINTEGERquery_textVARCHAR(200)search_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A user can contribute at most once to a daily-active count.
Hint 2
Aggregate by search date while deduplicating user identifiers.
Hint 3
Return the dates in chronological order.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT search_date, COUNT(DISTINCT user_id) AS dau FROM search_queries GROUP BY search_date ORDER BY search_dateWhy this works
COUNT(DISTINCT user_id) deduplicates: if user 1 searches 3 times on Jan 26, they count as 1 DAU, not 3. Jan 15 has the highest DAU (3) because users 7, 8, and 17 each search that day. Jan 26-30 have DAU=1 because only user 1 searches on those days.
Success check
Returns the complete deterministic result for daily active users
Expected result
Use this output to verify values, aliases, ordering, and row count.
| search_date | dau |
|---|---|
| 2024-01-05 | 1 |
| 2024-01-10 | 2 |
| 2024-01-11 | 1 |
| 2024-01-12 | 2 |
| 2024-01-13 | 1 |
| 2024-01-14 | 2 |
| 2024-01-15 | 3 |
| 2024-01-16 | 2 |
| 2024-01-17 | 2 |
| 2024-01-18 | 2 |
Previewing 10 of 22 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Netflix
Independent Netflix-style streaming, subscription, catalog, ratings, and engagement SQL practice.
Return to the complete interview preparation experience.