Coding questions · Meta
Write SQL for daily chats and fast replies
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.
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.
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).
Table messages
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| sender_id | BIGINT | NOT NULL |
| receiver_id | BIGINT | NOT NULL |
Sample data:
| date | receiver_id | sender_id | ts |
|---|---|---|---|
| 2025-05-31 | 202 | 101 | 2025-05-31 23:59:50 |
| 2025-06-01 | 101 | 202 | 2025-06-01 00:00:30 |
| 2025-06-01 | 303 | 101 | 2025-06-01 09:00:00 |
| 2025-06-01 | 101 | 303 | 2025-06-01 09:00:45 |
| 2025-06-01 | 404 | 101 | 2025-06-01 10:15:00 |
| 2025-06-01 | 505 | 101 | 2025-06-01 11:00:00 |
| 2025-06-01 | 606 | 101 | 2025-06-01 11:05:00 |
| 2025-06-01 | 707 | 101 | 2025-06-01 12:00:00 |
| 2025-06-01 | 101 | 707 | 2025-06-01 12:01:10 |
| 2025-06-01 | 101 | 808 | 2025-06-01 13:00:00 |
| 2025-06-01 | 808 | 101 | 2025-06-01 13:00:20 |
Expected output
All users who had conversations with more than 5 distinct other users on 2025-06-01. For the sample data, user 101 exchanged at least one message with 7 distinct users (202, 303, 404, 505, 606, 707, 808) on that date, and no other user met the >5 threshold.
| partner_cnt | user_id |
|---|---|
| 7 | 101 |
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.
Table messages
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| sender_id | BIGINT | NOT NULL |
| receiver_id | BIGINT | NOT NULL |
Sample data:
| date | receiver_id | sender_id | ts |
|---|---|---|---|
| 2025-05-31 | 202 | 101 | 2025-05-31 23:59:50 |
| 2025-06-01 | 101 | 202 | 2025-06-01 00:00:30 |
| 2025-06-01 | 303 | 101 | 2025-06-01 09:00:00 |
| 2025-06-01 | 101 | 303 | 2025-06-01 09:00:45 |
| 2025-06-01 | 404 | 101 | 2025-06-01 10:15:00 |
| 2025-06-01 | 505 | 101 | 2025-06-01 11:00:00 |
| 2025-06-01 | 606 | 101 | 2025-06-01 11:05:00 |
| 2025-06-01 | 707 | 101 | 2025-06-01 12:00:00 |
| 2025-06-01 | 101 | 707 | 2025-06-01 12:01:10 |
| 2025-06-01 | 101 | 808 | 2025-06-01 13:00:00 |
| 2025-06-01 | 808 | 101 | 2025-06-01 13:00:20 |
Expected output
Each row represents a sender_id who, for at least one message sent on 2025-06-01, received a reply from the original receiver within 60 seconds, with no intervening messages between the two users. For the sample data:
- 101 sent a message to 303 at 09:00:00 and received a reply at 09:00:45 (45 seconds, no messages in between) → counts as one fast reply.
- 808 sent a message to 101 at 13:00:00 and received a reply at 13:00:20 (20 seconds, no messages in between) → counts as one fast reply.
- Other messages either have no reply, or the reply is slower than 60 seconds, or the original send was not on 2025-06-01. Thus the output has two rows: sender 101 with 1 fast reply, and sender 808 with 1 fast reply.
| fast_reply_cnt | sender_id |
|---|---|
| 1 | 101 |
| 1 | 808 |
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.