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.
