Katkat
Back

Count Same-Day Registered Posters

ByteDanceData Scientist · Technical Screen
MediumData ScientistTechnical ScreenCoding & Algorithms

Last updated

This question evaluates SQL querying skills, including joining relational tables, date handling, aggregation, and deduplicating users across events. Commonly asked in Coding & Algorithms interviews for data scientists, it assesses practical relational database querying and analytics tasks—filtering, grouping, and counting distinct users across tables—and tests practical application of SQL rather than purely conceptual database theory; this summary is in English.

You are given two tables:

users
- user_id: unique identifier for a user
- registered_at: timestamp when the user registered

posts
- post_id: unique identifier for a post
- user_id: identifier of the user who published the post
- created_at: timestamp when the post was published

Write a SQL query that returns, for each calendar date, the number of distinct users who registered on that date and also published at least one post on that same date.

Important detail: a user may publish multiple posts on the same day, but that user should be counted only once for that date.

Expected output:

registration_date | user_count