Compute per-player daily cumulative games with windows
Company: Tencent
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Write a SQL query to output, for each player_id and each calendar date present in the data, the cumulative number of games played up to and including that date. Use window functions. Avoid user‑defined functions. Schema and sample data:
Tables:
- game_sessions(session_id INT PRIMARY KEY, player_id INT, game_id INT, session_date DATE)
Sample rows (game_sessions):
+------------+-----------+---------+--------------+
| session_id | player_id | game_id | session_date |
+------------+-----------+---------+--------------+
| 1 | 101 | 1 | 2025-08-29 |
| 2 | 101 | 2 | 2025-08-29 |
| 3 | 101 | 1 | 2025-08-30 |
| 4 | 102 | 2 | 2025-08-30 |
| 5 | 101 | 3 | 2025-09-01 |
| 6 | 102 | 2 | 2025-09-01 |
+------------+-----------+---------+--------------+
Part A: Treat each row as one game played. Output columns: player_id, session_date, daily_games (count of sessions that day for that player), cum_games (cumulative count per player ordered by date). Use only window functions for the cumulative part.
Part B (harder): If you must include zero‑activity dates between a player’s first and last session, show how you would modify the query assuming a calendar(d DATE) table that covers 2025-08-29 through 2025-09-01. Do not use recursive CTEs.
Quick Answer: This question evaluates proficiency with SQL window functions, aggregation, date-aware grouping and joins for computing per-player daily counts and running totals, and it falls under Data Manipulation (SQL/Python) for Data Scientist roles, emphasizing practical application rather than purely theoretical concepts.