← ByteDance Interview Insights

ByteDance·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

A SQL question from a Bytedance data scientist round, medium difficulty, with a small but important wrinkle around duplicate handling.

Questions Asked (1)

Q1

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. Note that a single user can publish multiple posts in one day.

Data ModelingProduct Analytics & Metrics
Author's notes

The interviewer nudged me about duplicates partway through, which made me realize I'd written something that would count the same user multiple times if they posted more than once that day.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify users who registered on a given day and users who published at least one post on a given day, then join these two sets on user_id and date to find users who did both on the same day. Finally, count the distinct user_ids from that intersection.

Pro tip: Mention that you would deduplicate posts per user per day before joining to avoid fan-out, and clarify whether 'same day' means calendar day in a specific timezone—this shows attention to data granularity and business context.

1. Clarify definitions and assumptions

Confirm what 'registered' and 'published' mean (e.g., registration timestamp, post creation timestamp) and whether 'same day' is based on UTC or local timezone. State assumptions explicitly.

2. Aggregate registrations to user-day level

Select user_id and the date of registration from the users table, ensuring one row per user per registration day (typically one registration per user).

3. Aggregate posts to distinct user-day level

From the posts table, select distinct user_id and post date to get one row per user per day they published at least one post, collapsing multiple posts.

4. Join on user_id and date

Inner join the registration user-day set with the post user-day set on user_id and date to keep only users who registered and posted on the same day.

5. Count distinct users

Count distinct user_id from the joined result to get the total number of unique users who meet the condition.

Key Points to Mention

  • Use DISTINCT or GROUP BY to deduplicate posts per user per day before joining to prevent row multiplication.
  • Join on both user_id and date to enforce the 'same day' condition.
  • Count DISTINCT user_id in the final aggregation to avoid double-counting users across multiple days.
  • Consider timezone handling and date truncation (e.g., DATE(timestamp)) for defining 'same day'.
  • Discuss performance: filtering early, using appropriate indexes, and avoiding unnecessary joins.
  • Validate edge cases: users with multiple posts, users who registered but never posted, and users who posted before registering (if possible).

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