SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Analytics Patterns · Distinct user-days · days in month · ratios

53. DAU/MAU stickiness

Concept: Distinct user-days · days in month · ratiosshow

Stickiness is average daily active users divided by monthly active users. Average DAU is distinct (user, day) pairs divided by the days in the month, so quiet days count as zero; MAU is distinct users in the month. (m + INTERVAL '1 month')::date - m gives the number of days in month m.

Example (different data):

SELECT to_char(played_on, 'YYYY-MM') AS month,
  COUNT(DISTINCT player_id) AS monthly_players
FROM game_plays
GROUP BY 1;

Your task

Investors asked for DAU/MAU, the standard measure of how habit-forming an app is.

From app_opens, for each month return month (YYYY-MM), avg_dau (distinct user-days divided by the number of days in that month, rounded to 2 decimals), mau (distinct users that month) and stickiness_pct (100 * user-days / (days in month * mau), rounded to 1 decimal). Oldest first.

Starting Postgres in your browser…

Checking your access…