Drill 13 of 15 in Analytics Patterns
0

Analytics Patterns · Gaps and islands · duplicate days

13. 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…