Context
We recently launched the Snap app in this new country on 2022-03-01, and now we want to run some data analysis.
Instruction
- Write a query to return DAU from 2022-03-01 to 2022-03-31
- If there is no DAU on that date, return 0.
- DAU: a user who has at least one event on that day.
Table 1: dates
Calendar dates from 01/01/2019 to 12/31/2025.
col_name | col_type ----------+---------- year | smallint month | smallint date | date
Table 2: snap_session
col_name | col_type ----------------+-------------------------- user_id | bigint event | text dt | date -- date of the event
Sample results
date | count ------------+------- 2022-03-01 | 9 2022-03-02 | 7 2022-03-03 | 0 2022-03-04 | 6 2022-03-05 | 15
Expected results
Solution postgres
SELECT D.date, count(DISTINCT S.user_id) dau
FROM dates D
LEFT JOIN snap_session S
ON D.date = S.dt
WHERE D.date >= '2022-03-01'
AND D.date <= '2022-03-31'
GROUP BY 1
ORDER BY 1;
Explanation
This query is selecting data from two tables, "dates" and "snap_session". It is counting the number of unique user IDs that occurred on each day between March 1, 2022, and March 31, 2022.
The "LEFT JOIN" statement ensures that all dates from the "dates" table are included in the final result, even if there are no matching records in the "snap_session" table.
The "WHERE" clause is used to filter the results to only include dates within the specified time frame.
The "GROUP BY" clause groups the results by date.
Finally, the "ORDER BY" clause orders the results by date in ascending order.
More Snap questions
ID | Title | Level | FTPR |
---|---|---|---|
195 | MoM user growth snap | hard | - |
196 | User segments report snap | hard | - |
197 | Students who didn't finish all subjects snap | easy | - |
198 | Average score per subject snap | easy | - |
199 | Students improvement snap | hard | - |
200 | Last week's absent students snap | medium | - |
201 | Top 3 students for each subject snap | medium | - |