Bytedance·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

Bytedance Data Scientist interview with a SQL question that looked straightforward but had a subtle deduplication requirement baked in. The kind of problem where you can get a working query pretty fast and still miss the point entirely.

Questions Asked (1)

Given a users table with registration timestamps and a posts table with post creation timestamps, write a SQL query that returns, for each calendar date, the count of distinct users who both registered on that date and published at least one post on that same date.

Product Analytics & MetricsData Modeling

Author's notes

The dedup part is where people probably slip up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the exact requirement: for each date, count distinct users who registered on that date and also posted on that date. Then write a query that joins the users and posts tables on both user_id and date, and groups by date to count distinct users. Consider edge cases like timezone handling and users with multiple posts.

Pro tip: Mention that you would check for timezone consistency between registration and post timestamps, as misalignment can skew daily counts. Also, discuss whether to use a subquery or a join, and the performance implications for large datasets.

1. Clarify requirements and schema

Confirm the table structures, column names, and whether timestamps include timezone. Ask if the date should be based on UTC or local time, and if there are any constraints like excluding deleted posts.

2. Identify relevant columns and filters

Determine that you need users.user_id, users.registration_timestamp, posts.user_id, and posts.post_timestamp. Decide to filter posts to only those on the same date as registration.

3. Design the join and aggregation

Join users and posts on user_id and on the date extracted from both timestamps. Then group by that date and count distinct users.

4. Write the SQL query

Use DATE() or CAST to extract date, join on user_id and date equality, and use COUNT(DISTINCT user_id) grouped by date. Ensure proper handling of timezones if needed.

5. Validate and optimize

Check for edge cases like users with multiple posts (distinct count handles it). Discuss indexing on user_id and timestamps for performance, and consider if a subquery might be more efficient.

Key Points to Mention

Use of COUNT(DISTINCT user_id) to avoid double-counting users with multiple posts.
Joining on both user_id and date to ensure registration and post occur on the same day.
Handling of timestamps: extracting date part and considering timezone conversions.
Performance considerations: indexing on user_id and timestamps, and potential use of subqueries or CTEs.
Edge cases: users who register and post on the same day but across midnight in different timezones.
Clarifying whether to include dates with zero counts (usually not required unless specified).

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