SQLPad SQLPad Sign In

194. Daily active users report for new market

easy snap

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 -