Compute per-player daily cumulative games with windows

Q: Compute per-player daily cumulative games with windows

This is a Data Manipulation (SQL/Python) interview question from Tencent for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Compute per-player daily cumulative games with windows

Comments (0)