← Tencent Interview Insights

Tencent·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Tencent. Two-part window function problem, the second part had a wrinkle I didn't fully see coming.

Questions Asked (2)

Q1

Write a SQL query that returns, for each player and each date they appear in the data, the number of sessions that day and the running total of sessions up to that date. Use window functions for the cumulative part, no UDFs.

Data ModelingTechnical Trade-offs
Author's notes

Part A was fine.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate the raw session data to get the number of sessions per player per date. Then, use a window function like SUM() OVER (PARTITION BY player ORDER BY date) to compute the running total. Ensure the query handles multiple sessions per player per date correctly by grouping before applying the window function.

Pro tip: Mention that you would validate the results by checking edge cases, such as players with multiple sessions on the same day and ensuring the running total resets for each player. Also, discuss the importance of indexing on (player_id, date) for performance.

1. Clarify requirements and assumptions

Confirm that 'sessions' refers to individual session records and that the running total should be cumulative per player over time. Ask about date granularity (e.g., daily) and whether ties in dates need special handling.

2. Aggregate sessions per player per date

Use a GROUP BY on player_id and date to count the number of sessions for each player on each date. This yields one row per player per date with a session count.

3. Apply window function for running total

Use SUM(session_count) OVER (PARTITION BY player_id ORDER BY date) to compute the cumulative sum of sessions up to each date. Ensure the window frame is default (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) or explicitly set to avoid errors.

4. Handle potential duplicates or ties

If multiple rows per player per date exist after aggregation (which they shouldn't), ensure the window function still works. Consider using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if dates are unique per player.

5. Optimize and validate

Add an index on (player_id, date) for performance. Validate the output by checking a few players manually, ensuring the running total is correct and resets per player.

Key Points to Mention

  • Use of GROUP BY to aggregate sessions per player per date before window function.
  • Window function syntax: SUM() OVER (PARTITION BY player_id ORDER BY date).
  • Default window frame behavior (RANGE vs ROWS) and when to specify explicitly.
  • Handling of ties in dates: if multiple sessions on same date, aggregation ensures one row per date.
  • Performance considerations: indexing on (player_id, date) for efficient sorting and partitioning.
  • Validation techniques: manual checks, edge cases like players with single session, and ensuring cumulative sum resets per player.

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

Q2

Modify the query so that dates with zero activity between a player's first and last session still appear in the output, using a provided calendar table. No recursive CTEs allowed.

Data ModelingTechnical Trade-offsAlgorithms & Data Structures
Author's notes

This is where I slowed down.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use the calendar table as the driving table to generate all dates, then LEFT JOIN the session activity data to fill in zeros. Filter the calendar to only dates between each player's first and last session using a subquery or window functions, and aggregate to get daily activity per player.

Pro tip: Mention that this approach avoids recursive CTEs and scales well, but be prepared to discuss the trade-off: if the calendar table is large, filtering it early with player-specific date ranges is crucial for performance.

1. Identify player date ranges

Compute each player's first and last session dates using MIN and MAX on the session table, grouped by player.

2. Join calendar with player ranges

Join the calendar table to the player date ranges so that only dates within each player's active period are kept.

3. Left join session activity

LEFT JOIN the session activity data (aggregated per player per date) to the calendar-player combination, ensuring all dates appear even if no activity.

4. Replace nulls with zeros

Use COALESCE or IFNULL to convert NULL activity counts to 0 for dates with no sessions.

5. Aggregate and order results

Group by player and date, and order by player and date to produce the final output with zero-activity dates included.

Key Points to Mention

  • Use of a calendar table as a date spine to generate all dates without recursion.
  • LEFT JOIN to preserve all dates and fill missing activity with zeros.
  • Filtering the calendar to each player's first and last session dates to avoid irrelevant dates.
  • Aggregation of session data per player per date before joining to avoid duplication.
  • Performance considerations: indexing on date columns and filtering early.
  • Alternative approaches like using a numbers table or generating series, but noting the restriction on recursive CTEs.

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