Write SQL window functions for D7 retention
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in SQL window functions, time-based aggregations, joins, null handling, and dense ranking for retention and revenue attribution within the Data Manipulation (SQL/Python) domain, emphasizing practical application of SQL for analytics.
Community answers
Answer by maxizhao9
WITH first_sessions AS (
SELECT
user_id,
country,
MIN(session_start_ts) AS first_play_ts,
MIN(session_start_ts)::DATE AS first_play_date
FROM game_sessions
GROUP BY user_id, country
),
retained AS (
SELECT
fs.user_id,
MAX(
CASE
WHEN gs.session_start_ts::DATE = fs.first_play_date + INTERVAL '7 days'
THEN 1 ELSE 0
END
) AS retained_d7
FROM first_sessions fs
LEFT JOIN game_sessions gs
ON gs.user_id = fs.user_id
AND gs.session_start_ts::DATE = fs.first_play_date + INTERVAL '7 days'
GROUP BY fs.user_id
),
ad_revenue AS (
SELECT
fs.user_id,
COALESCE(SUM(ai.revenue_usd), 0) AS ad_revenue_first_7d
FROM first_sessions fs
LEFT JOIN ad_impressions ai
ON ai.user_id = fs.user_id
AND ai.impression_ts >= fs.first_play_ts
AND ai.impression_ts < fs.first_play_ts + INTERVAL '7 days'
GROUP BY fs.user_id
)
SELECT
fs.user_id,
fs.first_play_date,
r.retained_d7,
ar.ad_revenue_first_7d,
fs.country,
DENSE_RANK() OVER (
PARTITION BY fs.country
ORDER BY ar.ad_revenue_first_7d DESC
) AS revenue_rank_in_country
FROM first_sessions fs
JOIN retained r ON r.user_id = fs.user_id
JOIN ad_revenue ar ON ar.user_id = fs.user_id
ORDER BY fs.country, revenue_rank_in_country;
Answer by carolinewei888
WITH user_first AS (
SELECT
user_id,
session_start_ts AS first_play_ts,
session_start_ts::date AS first_play_date,
country
FROM (
SELECT
user_id,
session_start_ts,
country,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY session_start_ts ASC
) AS rn
FROM game_sessions
) t
WHERE rn = 1
)
SELECT
f.user_id,
f.first_play_date,
MAX(CASE WHEN s.session_start_ts::date = f.first_play_date + INTERVAL '7 days' THEN 1 ELSE 0 END) AS retained_d7,
COALESCE(SUM(a.revenue_usd), 0) AS ad_revenue_first_7d,
f.country,
DENSE_RANK() OVER (
PARTITION BY f.country
ORDER BY COALESCE(SUM(a.revenue_usd), 0) DESC
) AS revenue_rank_in_country
FROM user_first f
LEFT JOIN game_sessions s
ON f.user_id = s.user_id
LEFT JOIN ad_impressions a
ON f.user_id = a.user_id
AND a.impression_ts >= f.first_play_ts
AND a.impression_ts < f.first_play_ts + INTERVAL '7 days'
GROUP BY
f.user_id,
f.first_play_date,
f.country;
