Write SQL window functions for D7 retention

Assume you have the following tables (timestamps are in UTC).

1) game_sessions

• user_id (STRING)
• session_start_ts (TIMESTAMP)
• country (STRING)

Each row is one play session.

2) ad_impressions

• user_id (STRING)
• impression_ts (TIMESTAMP)
• ad_type (STRING) -- e.g., 'rewarded', 'interstitial'
• revenue_usd (DECIMAL(10,4))

Each row is one ad impression.

Write a SQL query (window functions encouraged) that outputs one row per user with:

• user_id
• first_play_date (DATE) = the user’s first session date
• retained_d7 (INT) = 1 if the user had any session on (first_play_date + 7 days), else 0
• ad_revenue_first_7d (DECIMAL) = total ad revenue from impressions with impression_ts in [first_play_ts, first_play_ts + 7 days)
• country
• revenue_rank_in_country (INT) = rank users within each country by ad_revenue_first_7d (highest revenue rank = 1)

Notes:

• If a user has no ad impressions, ad_revenue_first_7d should be 0.
• Define ties in ranking using dense rank.