SQL Joins, CTEs, and Intervals | Dataford Interview Questions

Your question is SQL Joins, CTEs, and Intervals. Start with the requirements and the three tables 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.

Sberbank wants to identify continuous customer activity periods in Sberbank Online. Activity records can be duplicated during ingestion, and separate events belong to the same interval when the next event starts no later than one day after the running end of the current interval.

Write a PostgreSQL query that joins all three tables and returns only intervals lasting at least two calendar days and containing at least two distinct Sberbank Online activity types.

Requirements

Deduplicate events by customer, activity type, start date, and end date, keeping the row with the latest ingested_at.
Join customers, activity events, and activity types, retaining only channel = 'Sberbank Online'.
Use CTEs and window functions to merge overlapping or adjacent intervals. The chaining logic must compare each event with the running maximum end date, not only the immediately preceding event.
Return the customer, merged interval boundaries, inclusive active days, distinct activity type count, and deduplicated event count.
Keep intervals meeting both thresholds and order the result by customer and interval start.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Sberbank customer identifier
customer_name	VARCHAR(100)	Customer display name

activity_events

Column	Type	Description
event_idPK	INT	Unique ingested event record
customer_id	INT	References customers.customer_id
activity_type_id	INT	References activity_types.activity_type_id
start_date	DATE	Activity interval start date
end_date	DATE	Activity interval end date
ingested_at	TIMESTAMP	Timestamp when the record was ingested

activity_types

Column	Type	Description
activity_type_idPK	INT	Unique activity type identifier
activity_name	VARCHAR(80)	Readable activity name
channel	VARCHAR(40)	Sberbank product or channel where the activity occurred