195. MoM user growth

hard snap

  • ********************************#194
  • *******************************************************************************************************************************************
  • **************************************************************************************
  • ***********************************************************************************

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

Solution postgres

WITH mau AS (
    SELECT D.year, D.month, count(DISTINCT S.user_id) active_users
    FROM dates D
    LEFT JOIN snap_session S
    ON D.date = S.dt
    WHERE D.date >= '2021-03-01'
    AND D.date <= '2022-04-30'
    GROUP BY 1, 2
), mau_growth AS (
    SELECT year, month, (active_users - LAG(active_users, 1) OVER (ORDER BY year, month)) * 100.0/ LAG(active_users, 1) OVER (ORDER BY year, month) AS mom_growth
    FROM mau
SELECT year, month, mom_growth
FROM mau_growth
WHERE mom_growth IS NOT NULL;


This query is calculating the monthly active user (MAU) growth rate for a specific date range.

The query first creates a Common Table Expression (CTE) called "mau" which counts the number of distinct active users for each month between March 2021 and April 2022. It does this by joining a table called "snap_session" with a table called "dates", using the date column as the join key. It then groups the results by year and month.

The second CTE called "mau_growth" uses the previous CTE "mau" to calculate the month-over-month (MoM) growth rate of active users. It does this by subtracting the previous month's active user count from the current month's count, dividing that difference by the previous month's count, and multiplying by 100 to get a percentage growth rate. It uses the LAG function to get the previous month's count for the division.

Finally, the main query selects the year, month, and MoM growth rate from the "mau_growth" CTE for all months with a non-null growth rate. This result set can be used to track the growth rate of active users over time and identify any trends or changes in user behavior.

Expected results

More Snap questions

ID Title Level FTPR
194 Daily active users report for new market snap easy
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 hard