Coding questions · Tencent
Compute per-player daily cumulative games with windows
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.
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.
Daily and cumulative games per player (non-zero days only)
You are given a game_sessions table that records individual game sessions. Each row represents one game played by a player on a specific date.
Write a SQL query to output, for each player_id and each session_date on which that player has at least one session, the cumulative number of games played up to and including that date.
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 of games per player, ordered by session_date
Use window functions for the cumulative part (cum_games). Do not use user-defined functions.
Table game_sessions
| column | type | constraints |
|---|---|---|
| session_id | INT | PRIMARY KEY, NOT NULL |
| player_id | INT | NOT NULL |
| game_id | INT | NOT NULL |
| session_date | DATE | NOT NULL |
Sample data:
| game_id | player_id | session_date | session_id |
|---|---|---|---|
| 1 | 101 | 2025-08-29 | 1 |
| 2 | 101 | 2025-08-29 | 2 |
| 1 | 101 | 2025-08-30 | 3 |
| 2 | 102 | 2025-08-30 | 4 |
| 3 | 101 | 2025-09-01 | 5 |
| 2 | 102 | 2025-09-01 | 6 |
Expected output
One row per player_id and session_date on which they played at least one game, with daily and cumulative counts per player ordered by date.
| cum_games | daily_games | player_id | session_date |
|---|---|---|---|
| 2 | 2 | 101 | 2025-08-29 |
| 3 | 1 | 101 | 2025-08-30 |
| 4 | 1 | 101 | 2025-09-01 |
| 1 | 1 | 102 | 2025-08-30 |
| 2 | 1 | 102 | 2025-09-01 |
Hints
- First aggregate game_sessions by player_id and session_date to get daily_games.
- Use SUM(daily_games) as a window function partitioned by player_id and ordered by session_date for the cumulative total.
Daily and cumulative games per player including zero-activity dates
Extend the previous task so that zero-activity dates between a player’s first and last session are included.
You are given two tables:
- game_sessions: records individual game sessions (each row is one game played).
- calendar: a date dimension table that contains one row per calendar date and covers the full range from 2025-08-29 through 2025-09-01.
For each player_id, consider the period from their first session_date to their last session_date (inclusive). For every date in this range, output a row, even if the player did not play any games on that date (in which case daily_games should be 0).
Output columns:
- player_id
- session_date
- daily_games: count of sessions that day for that player (0 if none)
- cum_games: cumulative count of games per player, ordered by session_date
Use window functions for the cumulative part (cum_games). Do not use user-defined functions. Do not use recursive CTEs. Use the calendar table to generate the missing dates.
Table game_sessions
| column | type | constraints |
|---|---|---|
| session_id | INT | PRIMARY KEY, NOT NULL |
| player_id | INT | NOT NULL |
| game_id | INT | NOT NULL |
| session_date | DATE | NOT NULL |
Sample data:
| game_id | player_id | session_date | session_id |
|---|---|---|---|
| 1 | 101 | 2025-08-29 | 1 |
| 2 | 101 | 2025-08-29 | 2 |
| 1 | 101 | 2025-08-30 | 3 |
| 2 | 102 | 2025-08-30 | 4 |
| 3 | 101 | 2025-09-01 | 5 |
| 2 | 102 | 2025-09-01 | 6 |
Table calendar
| column | type | constraints |
|---|---|---|
| d | DATE | PRIMARY KEY, NOT NULL |
Sample data:
| d |
|---|
| 2025-08-29 |
| 2025-08-30 |
| 2025-08-31 |
| 2025-09-01 |
Expected output
One row per player_id and per calendar date between that player’s first and last session (inclusive), including dates with zero games, with daily and cumulative counts per player ordered by date.
| cum_games | daily_games | player_id | session_date |
|---|---|---|---|
| 2 | 2 | 101 | 2025-08-29 |
| 3 | 1 | 101 | 2025-08-30 |
| 3 | 0 | 101 | 2025-08-31 |
| 4 | 1 | 101 | 2025-09-01 |
| 1 | 1 | 102 | 2025-08-30 |
| 1 | 0 | 102 | 2025-08-31 |
| 2 | 1 | 102 | 2025-09-01 |
Hints
- Compute each player’s first and last session_date, then join their date range to the calendar table.
- Left join the per-player-per-day counts to the generated player/date grid, COALESCE missing counts to 0, then apply a window SUM for the cumulative total.