Detect 5-Day Active Streaks

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Detect 5-Day Active Streaks. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Adobe Express wants to identify creators who used the product on at least one event per day for a continuous 5-day streak. Write a PostgreSQL query that returns every qualifying streak for each creator.

Requirements

Deduplicate activity to one row per creator per calendar day.
Detect continuous date sequences using DENSE_RANK() and LAG().
Return only streaks with exactly 5 consecutive active days.
Output the creator, streak start date, streak end date, and streak length.

Schema

express_creator_activity

Column	Type	Description
activity_idPK	INT	Primary key for the activity event
creator_id	INT	Adobe Express creator identifier
creator_name	VARCHAR(255)	Creator display name
activity_ts	TIMESTAMP	Timestamp when the creator generated an activity event
surface	VARCHAR(50)	Adobe Express surface where the event occurred
event_type	VARCHAR(50)	Type of activity event

Tablesexpress_creator_activity

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results