Coding questions · Meta
Analyze Conversation Engagement and Reaction Usage Effectively
This question evaluates data manipulation and analytical skills in SQL and Python, focusing on deduplication of unordered sender-receiver pairs, temporal aggregation and differences, computation of counts and percentages, and the design of query-friendly engagement metrics.
messages
+-----------+--------+----------+--------------+---------------------+
| messageid | sender | receiver | has_reaction | timestamp |
+-----------+--------+----------+--------------+---------------------+
| 1 | 101 | 202 | 0 | 2023-08-01 10:01:00 |
| 2 | 202 | 101 | 1 | 2023-08-01 10:02:10 |
| 3 | 303 | 404 | 0 | 2023-08-05 14:11:33 |
| 4 | 404 | 303 | 1 | 2023-08-05 14:11:55 |
| 5 | 101 | 303 | 0 | 2023-08-07 08:45:12 |
+-----------+--------+----------+--------------+---------------------+
Scenario
Messaging platform wants to understand conversation engagement and reaction usage over the last week.
Question
Write SQL to count unique conversations (unordered sender-receiver pairs) that started in the past 7 days. 2. Calculate the percentage of those conversations that contain at least one message with has_reaction = 1. 3. Compute the average number of days from the first message in a conversation to the first reacted message. 4. Suggest a query-friendly metric and analysis to test whether conversations with reactions are more active than those without.
Hints
Define a conversation as all messages between the same two users, regardless of direction. Use MIN(timestamp) and DATEDIFF for timing.
Conversation Starts in a 7-Day Window
You are given a messages table from a messaging platform. A conversation is all messages exchanged between the same two users, regardless of direction, so (sender, receiver) and (receiver, sender) are the same conversation. For the half-open 7-day window [2023-08-01, 2023-08-08), count unique conversations whose first-ever message occurred in that window. Return one row with conversations_started_7d.
Table messages
| column | type | constraints |
|---|---|---|
| messageid | INTEGER | PRIMARY KEY, NOT NULL |
| sender | INTEGER | NOT NULL |
| receiver | INTEGER | NOT NULL |
| has_reaction | INTEGER | NOT NULL, CHECK (has_reaction IN (0,1)) |
| timestamp | DATETIME | NOT NULL |
Sample data:
| has_reaction | messageid | receiver | sender | timestamp |
|---|---|---|---|---|
| 0 | 1 | 202 | 101 | 2023-08-01 10:01:00 |
| 1 | 2 | 101 | 202 | 2023-08-01 10:02:10 |
| 0 | 3 | 404 | 303 | 2023-08-05 14:11:33 |
| 1 | 4 | 303 | 404 | 2023-08-05 14:11:55 |
| 0 | 5 | 303 | 101 | 2023-08-07 08:45:12 |
Expected output
One row showing how many unique conversations have their first-ever message in the 7-day window [2023-08-01, 2023-08-08).
| conversations_started_7d |
|---|
| 3 |
Hints
- Normalize unordered sender/receiver pairs using LEAST(sender, receiver) and GREATEST(sender, receiver).
- Define conversation start as MIN(timestamp) per normalized user pair.
- Use a fixed filter window: timestamp >= 2023-08-01 AND timestamp < 2023-08-08.
Percentage of Conversations with Reactions
Using the same unordered-pair conversation definition and the same half-open 7-day window [2023-08-01, 2023-08-08), compute the percentage of conversations that started in the window and contain at least one message with has_reaction = 1. Return one row with pct_with_reaction, rounded to 2 decimal places.
Table messages
| column | type | constraints |
|---|---|---|
| messageid | INTEGER | PRIMARY KEY, NOT NULL |
| sender | INTEGER | NOT NULL |
| receiver | INTEGER | NOT NULL |
| has_reaction | INTEGER | NOT NULL, CHECK (has_reaction IN (0,1)) |
| timestamp | DATETIME | NOT NULL |
Sample data:
| has_reaction | messageid | receiver | sender | timestamp |
|---|---|---|---|---|
| 0 | 1 | 202 | 101 | 2023-08-01 10:01:00 |
| 1 | 2 | 101 | 202 | 2023-08-01 10:02:10 |
| 0 | 3 | 404 | 303 | 2023-08-05 14:11:33 |
| 1 | 4 | 303 | 404 | 2023-08-05 14:11:55 |
| 0 | 5 | 303 | 101 | 2023-08-07 08:45:12 |
Expected output
One row with the percentage of conversations started in the 7-day window that contain at least one reacted message.
| pct_with_reaction |
|---|
| 66.67 |
Hints
- Normalize unordered sender/receiver pairs using LEAST(sender, receiver) and GREATEST(sender, receiver).
- Aggregate MAX(has_reaction) per conversation to detect whether it ever had a reaction.
- Filter to conversations whose first message falls in the 7-day window.
- Compute (conversations with any reaction) / (all conversations that started in the window).
Time to First Reaction for New Conversations
For conversations whose first-ever message occurred in [2023-08-01, 2023-08-08) and that have at least one reacted message (has_reaction = 1), compute the average number of days from the first message to the first reacted message. Return one row with avg_days_to_first_reaction, rounded to 2 decimal places.
Table messages
| column | type | constraints |
|---|---|---|
| messageid | INTEGER | PRIMARY KEY, NOT NULL |
| sender | INTEGER | NOT NULL |
| receiver | INTEGER | NOT NULL |
| has_reaction | INTEGER | NOT NULL, CHECK (has_reaction IN (0,1)) |
| timestamp | DATETIME | NOT NULL |
Sample data:
| has_reaction | messageid | receiver | sender | timestamp |
|---|---|---|---|---|
| 0 | 1 | 202 | 101 | 2023-08-01 10:01:00 |
| 1 | 2 | 101 | 202 | 2023-08-01 10:02:10 |
| 0 | 3 | 404 | 303 | 2023-08-05 14:11:33 |
| 1 | 4 | 303 | 404 | 2023-08-05 14:11:55 |
| 0 | 5 | 303 | 101 | 2023-08-07 08:45:12 |
Expected output
One row giving the average number of days from conversation start to first reaction, among conversations that started in the window and had a reaction.
| avg_days_to_first_reaction |
|---|
| 0 |
Hints
- Normalize unordered sender/receiver pairs using LEAST(sender, receiver) and GREATEST(sender, receiver).
- For each conversation, compute MIN(timestamp) as the first message and MIN(CASE WHEN has_reaction = 1 THEN timestamp END) as the first reacted message.
- Filter to conversations whose first message is in the 7-day window.
- Compute the time difference (in days) between first message and first reacted message, then take the average.
Activity Comparison for Conversations With vs Without Reactions
For conversations whose first-ever message occurred in [2023-08-01, 2023-08-08), examine the first 7 days after each conversation starts. Group conversations by whether any message in the conversation had has_reaction = 1, and return the group flag, average message count in the first 7 days, average messages per day over those 7 days, and the number of conversations in each group.
Table messages
| column | type | constraints |
|---|---|---|
| messageid | INTEGER | PRIMARY KEY, NOT NULL |
| sender | INTEGER | NOT NULL |
| receiver | INTEGER | NOT NULL |
| has_reaction | INTEGER | NOT NULL, CHECK (has_reaction IN (0,1)) |
| timestamp | DATETIME | NOT NULL |
Sample data:
| has_reaction | messageid | receiver | sender | timestamp |
|---|---|---|---|---|
| 0 | 1 | 202 | 101 | 2023-08-01 10:01:00 |
| 1 | 2 | 101 | 202 | 2023-08-01 10:02:10 |
| 0 | 3 | 404 | 303 | 2023-08-05 14:11:33 |
| 1 | 4 | 303 | 404 | 2023-08-05 14:11:55 |
| 0 | 5 | 303 | 101 | 2023-08-07 08:45:12 |
Expected output
Two rows: one for conversations that ever had a reaction and one for those that did not, comparing activity in the first 7 days.
| avg_messages_in_first_7_days | avg_messages_per_day_first_7_days | conversations_count | has_reaction_in_conversation |
|---|---|---|---|
| 2 | 0.286 | 2 | 1 |
| 1 | 0.143 | 1 | 0 |
Hints
- Normalize unordered sender/receiver pairs using LEAST(sender, receiver) and GREATEST(sender, receiver).
- For each conversation, compute whether it ever had a reaction using MAX(has_reaction).
- Filter to conversations whose first message occurs in the 7-day window.
- Join messages back to conversations and restrict to messages in the first 7 days after conversation start (timestamp >= first_message_at AND < first_message_at + INTERVAL '7 days').
- Group by reaction flag to compute average message counts and average messages per day.