Bytedance·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

SQL round for a Data Scientist role at Bytedance. One question, medium difficulty, and the interviewer nudged me about duplicates partway through which I probably should've caught myself.

Questions Asked (1)

Write a SQL query to find the total number of distinct users who both registered and published at least one post on the same day. Keep in mind a user can publish multiple posts in a single day.

Data ModelingProduct Analytics & Metrics

Author's notes

The interviewer had to remind me about duplicates, which was a bit embarrassing.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and definitions: assume a users table with registration dates and a posts table with user IDs and post timestamps. Then, join the tables on user ID and filter for rows where the registration date matches the post date, and finally count distinct users to get the answer.

Pro tip: Mention that you'd validate the result by checking edge cases, such as users who registered and posted on the same day but in different time zones, and discuss how to handle time zone conversions if necessary.

1. Clarify the schema and definitions

Ask or state assumptions about the tables: users (user_id, registration_date) and posts (post_id, user_id, post_date). Define 'same day' as the date part of the timestamp, ignoring time.

2. Join users and posts on user_id

Use an inner join to combine users with their posts, ensuring we only consider users who have posted at least once.

3. Filter for same-day registration and posting

Add a WHERE clause comparing the registration date to the post date (e.g., DATE(registration_date) = DATE(post_date)).

4. Count distinct users

Use COUNT(DISTINCT user_id) to get the total number of unique users who meet the condition, handling multiple posts per user per day.

5. Write the final SQL query

Combine the steps into a single query, ensuring proper date truncation and distinct counting.

Key Points to Mention

Use of INNER JOIN to combine users and posts tables.
Date truncation or casting to compare only the date part (e.g., DATE() or CAST AS DATE).
COUNT(DISTINCT user_id) to avoid double-counting users with multiple posts.
Handling of time zones if timestamps are stored in UTC and registration/post dates are in local time.
Assumption about schema and data types (e.g., registration_date and post_date are timestamps).
Potential need for indexing on user_id and date columns for performance.

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