Calculate Weekly Thread Engagement with Reactions in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
messages
+------------+--------+----------+--------------+---------------------+
| message_id | sender | receiver | has_reaction | timestamp |
+------------+--------+----------+--------------+---------------------+
| 101 | 12 | 34 | 1 | 2023-04-01 10:00:00 |
| 102 | 34 | 12 | 0 | 2023-04-01 10:02:00 |
| 103 | 56 | 78 | 1 | 2023-04-02 14:35:00 |
| 104 | 78 | 56 | 1 | 2023-04-02 14:40:00 |
| 105 | 90 | 91 | 0 | 2023-04-03 09:12:00 |
+------------+--------+----------+--------------+---------------------+
##### Scenario
Messaging platform stores every message with sender, receiver, reaction flag, and timestamp. Analysts need weekly thread-level engagement insights.
##### Question
Write SQL to count the number of unique threads that had at least one message during a given week. Write SQL to count how many of those threads contain at least one message where has_reaction = 1. For threads that eventually get a reaction, compute the average time (in minutes) from the first message in the thread to the first reacted message. Describe how you would use the presence or absence of reactions to define an "active" thread for an A/B test.
##### Hints
Treat a thread as an unordered sender-receiver pair; use DISTINCT, aggregation, window functions, and TIMESTAMPDIFF.
Overview: This question evaluates SQL and data-manipulation competency for computing time-based engagement metrics, specifically forming thread-level aggregates and measuring reaction occurrence and timing.
You are given a single table `messages` that logs direct messages between users.
A **thread** is an unordered conversation between two users: the message `(sender = 12, receiver = 34)` belongs to the same thread as `(sender = 34, receiver = 12)`. Treat each thread as the unordered pair `{LEAST(sender, receiver), GREATEST(sender, receiver)}`.
For the **week beginning `2023-04-01 00:00:00` and ending `2023-04-08 00:00:00`** (a message belongs to the week if `timestamp >= 2023-04-01 00:00:00` AND `timestamp < 2023-04-08 00:00:00`), write **one** PostgreSQL query that returns exactly **one row** with the following columns, in this order:
1. `week_start` — the literal timestamp `2023-04-01 00:00:00`.
2. `week_end` — the literal timestamp `2023-04-08 00:00:00`.
3. `total_threads_in_week` — the number of distinct threads that had **at least one** message during the week.
4. `active_threads_in_week` — how many of those threads had **at least one** message with `has_reaction = 1` during the week.
5. `active_rate` — `active_threads_in_week / total_threads_in_week`, rounded to **2 decimal places** (guard against division by zero).
6. `avg_minutes_to_first_reaction` — the average, in minutes, of the gap between a thread's **first-ever** message (over all time, not just the week) and that thread's **first-ever** reacted message (over all time). Average **only** over threads that (a) had at least one message during the week AND (b) eventually receive a reaction at some point. Round to **2 decimal places**.
Note: a thread's first-ever message may be **before** the analysis week, so column 6 must consider all rows in `messages`, not only those inside the week window.
Tables
messages(message_id INTEGER, sender INTEGER, receiver INTEGER, has_reaction INTEGER, timestamp TIMESTAMP)
Hints
- Build an unordered thread key with LEAST(sender, receiver) and GREATEST(sender, receiver) so message direction is ignored.
- Compute first-ever message and first-ever reaction per thread with MIN(timestamp) over the WHOLE table, then INNER JOIN so only reacted threads enter the average.