Write SQL for daily chats and fast replies
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given a messaging events table that records one row per message sent.
Schema
- messages(
date DATE, -- calendar date of event (UTC)
ts TIMESTAMP, -- event timestamp (UTC)
sender_id BIGINT,
receiver_id BIGINT
)
Sample data (ASCII)
messages
+------------+---------------------+-----------+-------------+
| date | ts | sender_id | receiver_id |
+------------+---------------------+-----------+-------------+
| 2025-08-31 | 2025-08-31 23:59:50 | 101 | 202 |
| 2025-09-01 | 2025-09-01 00:00:30 | 202 | 101 |
| 2025-09-01 | 2025-09-01 09:00:00 | 101 | 303 |
| 2025-09-01 | 2025-09-01 09:00:45 | 303 | 101 |
| 2025-09-01 | 2025-09-01 10:15:00 | 101 | 404 |
| 2025-09-01 | 2025-09-01 11:00:00 | 101 | 505 |
| 2025-09-01 | 2025-09-01 11:05:00 | 101 | 606 |
| 2025-09-01 | 2025-09-01 12:00:00 | 101 | 707 |
| 2025-09-01 | 2025-09-01 12:01:10 | 707 | 101 |
| 2025-09-01 | 2025-09-01 13:00:00 | 808 | 101 |
| 2025-09-01 | 2025-09-01 13:00:20 | 101 | 808 |
+------------+---------------------+-----------+-------------+
Assume "today" is 2025-09-01.
Q1. Return all user_id who chatted with >5 distinct other users on 2025-09-01. Define "chatted with" as exchanging at least one message in either direction on that date. Output: user_id, partner_cnt. Requirements:
- Count distinct counterparties per user considering both sent and received messages.
- Be robust to multiple messages between the same pair and to users both sending and receiving with the same counterparty.
- Avoid double-counting a counterparty.
- Single SQL query preferred; standard SQL (window functions allowed).
Q2. Return distinct sender_id who received at least one reply within 60 seconds for a message they sent on 2025-09-01. Define a reply as a message from the original receiver back to the original sender with reply.ts - sent.ts between 0 and 60 seconds inclusive, and there is no other message between these two users that occurs after the sent message and before the reply. Output: sender_id and optionally count of such fast replies. Requirements:
- Use the exact timestamps (ts), not just the date column.
- Ensure directionality (receiver must reply to sender).
- Correctly handle multiple candidate replies—only the earliest opposing-direction message after each sent message can qualify.
- Edge cases: cross-midnight boundaries, identical timestamps, duplicate rows. Briefly state how your query handles these.
Overview: This question evaluates a candidate's ability to manipulate relational data and reason about temporal event sequences, specifically testing skills in SQL aggregation, distinct counting, deduplication, bidirectional relationship handling, and time-windowed event matching.
Read the full Meta Data Scientist interview experience this question came from
Daily distinct chat partners per user
You are given a messaging events table that records one row per message sent.
Table:
- messages(date, ts, sender_id, receiver_id)
The columns are:
- date: calendar date of event (UTC)
- ts: exact event timestamp (UTC)
- sender_id: user who sent the message
- receiver_id: user who received the message
For the calendar date 2025-06-01, return all user_id values who chatted with more than 5 distinct other users on that date.
Definition:
- A user "chatted with" another user on 2025-06-01 if at least one message was sent between them in either direction on that date (sender → receiver or receiver → sender).
Requirements:
- Count distinct counterparties per user considering both sent and received messages on 2025-06-01.
- Be robust to multiple messages between the same pair and to users both sending and receiving with the same counterparty.
- Do not double-count the same counterparty.
- Output columns: user_id, partner_cnt (number of distinct counterparties on 2025-06-01).
- Use a single SQL query; standard SQL is sufficient (window functions are allowed but not required).
Tables
messages(date DATE, ts TIMESTAMP, sender_id BIGINT, receiver_id BIGINT)
Hints
- You need to count partners for both sent and received messages, not just sent ones.
- Consider normalizing each interaction into user/partner pairs and then de-duplicating before counting.
Fast replies within a conversation
Using the same messages table:
- messages(date, ts, sender_id, receiver_id)
For messages sent on 2025-06-01, return the distinct sender_id values who received at least one reply within 60 seconds for a message they sent that day.
Definitions:
- A reply to an original message (sent_ts, sender_id = A, receiver_id = B) is a message from B back to A such that:
- reply.ts - sent_ts is between 0 and 60 seconds inclusive, and
- there is no other message between these two users (A and B, in either direction) that occurs after the original message and before the reply.
- Use the exact timestamps (ts), not just the date column, to determine timing.
Requirements:
- Only consider original messages where date = '2025-06-01'.
- Ensure directionality: the reply must be from the original receiver back to the original sender.
- If there are multiple subsequent messages between the same two users, only the earliest message after the original send may qualify as the reply candidate.
- Edge cases to handle correctly:
- Cross-midnight boundaries (e.g., an earlier message just before midnight and a reply just after midnight).
- Identical timestamps for messages.
- Duplicate rows.
- Output: sender_id and a count of such fast replies (fast_reply_cnt) they received for messages they sent on 2025-06-01.
- Use a single SQL query; window functions are allowed and may simplify the solution.
Tables
messages(date DATE, ts TIMESTAMP, sender_id BIGINT, receiver_id BIGINT)
Hints
- Think in terms of conversations between unordered user pairs and the next message between the same two users.
- A window function like LEAD over each user pair can give you the next message; then filter for opposite direction and a time difference of at most 60 seconds.