SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Analytics Patterns · Gaps and islands · duplicate days

58. Activity streaks as date ranges

Concept: Gaps and islands · duplicate daysshow

The gaps-and-islands trick (date minus ROW_NUMBER is constant within a streak) breaks when a date appears twice, because the duplicate bumps the row number. Dedupe to one row per user and day first, then GROUP BY the anchor and report MIN and MAX date as the range.

Example (different data):

WITH d AS (SELECT DISTINCT shop_id, sale_day FROM sales)
SELECT shop_id, sale_day,
  sale_day - (ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY sale_day))::int AS anchor
FROM d;

Your task

Lifecycle marketing wants to reward power users for every run of 3 or more consecutive days with an app open.

Return user_id, start_date, end_date and days (number of distinct days in the streak) for every streak of at least 3 consecutive days in app_opens. Sort by user_id, start_date.

Starting Postgres in your browser…

Checking your access…