How would you use SQL window functions to calculate rolling 7-day engagement and rank clients within each segment?
Use the provided client, segment, and engagement event data. Return results for clients assigned to a valid segment and dates on which they have engagement events.
Output
- One row per client, segment, and activity date.
- Columns:
activity_date, segment_name, client_id, client_name, rolling_7_day_engagement, and segment_rank.
- Rank clients within the same segment and activity date by rolling engagement descending, with ties sharing a rank and
client_id as the final ordering key.
