Calculate Weekly Thread Engagement with Reactions in SQL

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.

Weekly thread engagement and active-rate metric

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.

Table messages

column	type	constraints
message_id	INTEGER	PRIMARY KEY, NOT NULL
sender	INTEGER	NOT NULL
receiver	INTEGER	NOT NULL
has_reaction	INTEGER	NOT NULL
timestamp	TIMESTAMP	NOT NULL

Sample data:

has_reaction	message_id	receiver	sender	timestamp
1	101	34	12	2023-04-01 10:00:00
0	102	12	34	2023-04-01 10:02:00
1	103	78	56	2023-04-02 14:35:00
1	104	56	78	2023-04-02 14:40:00
0	105	91	90	2023-04-03 09:12:00
0	99	78	56	2023-03-30 08:00:00

Expected output

One row for the week 2023-04-01 to 2023-04-08: 3 active threads, 2 of them reacted (active_rate 0.67), and avg 2357.50 minutes from each eventually-reacted thread's first-ever message to its first-ever reaction.

active_rate	active_threads_in_week	avg_minutes_to_first_reaction	total_threads_in_week	week_end	week_start
0.67	2	2357.50	3	2023-04-08T00:00:00	2023-04-01T00:00:00

Hints

1. Build an unordered thread key with LEAST(sender, receiver) and GREATEST(sender, receiver) so message direction is ignored.
2. 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.
3. In PostgreSQL, get minutes between two timestamps with EXTRACT(EPOCH FROM (b - a)) / 60.0; cast to numeric and ROUND(..., 2), and use NULLIF(denominator, 0) for the rate.