Amazon·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

Amazon Data Scientist technical screen, one meaty SQL problem that covered window functions, retention logic, and revenue ranking all in one shot. The question was well-constructed and felt like something you'd actually write on the job, which made it slightly more stressful than a pure puzzle.

Questions Asked (1)

Given a game_sessions table and an ad_impressions table, write a SQL query that returns one row per user with their first play date, a 7-day retention flag, total ad revenue in the first 7 days from their first session, their country, and a dense rank of users within each country by that revenue figure.

Product Analytics & MetricsData Modeling

Author's notes

This one had a lot of moving parts and I almost forgot to handle the case where a user has zero ad impressions.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by computing each user's first play date from the game_sessions table, then join to ad_impressions to sum revenue within the first 7 days. Use a 7-day retention flag based on whether the user had a session on day 7 after their first play date, and finally apply DENSE_RANK() partitioned by country ordered by revenue descending.

Pro tip: Clarify the definition of '7-day retention' upfront—whether it means a session exactly on day 7 or any session within days 1-7—as this ambiguity can significantly affect the metric and shows you think like a product analyst.

1. Identify first play date per user

Use a subquery or CTE with MIN(session_date) grouped by user_id from game_sessions to get each user's first play date.

2. Compute 7-day retention flag

Join back to game_sessions and check if the user has a session on the 7th day after their first play date (or within the 7-day window, depending on definition). Use a CASE statement to flag retention.

3. Aggregate ad revenue in first 7 days

Join ad_impressions to the first play date and sum revenue where impression_date is between first_play_date and first_play_date + 6 days (or +7 days, depending on inclusive/exclusive).

4. Include country and rank users

Bring in country from a users table (or game_sessions if available). Use DENSE_RANK() OVER (PARTITION BY country ORDER BY total_revenue DESC) to rank users within each country.

5. Combine into final output

Select user_id, first_play_date, retention_flag, total_revenue, country, and dense_rank. Ensure one row per user by using appropriate GROUP BY or DISTINCT.

Key Points to Mention

Use of CTEs or subqueries to break down the problem into manageable steps.
Handling date ranges correctly: inclusive vs exclusive boundaries for the 7-day window.
Definition of 7-day retention: exact day 7 vs any activity within 7 days.
Use of DENSE_RANK() vs RANK() and why DENSE_RANK is appropriate for ranking without gaps.
Ensuring one row per user by aggregating revenue and retention flags properly.
Considering NULLs or users with no ad impressions (e.g., using LEFT JOIN and COALESCE).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.