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…