Analyze Conversation Engagement and Reaction Usage Effectively
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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`.
Tables
messages(messageid INTEGER, sender INTEGER, receiver INTEGER, has_reaction INTEGER, timestamp DATETIME)
Hints
- Normalize unordered sender/receiver pairs using LEAST(sender, receiver) and GREATEST(sender, receiver).
- Define conversation start as MIN(timestamp) per normalized user pair.
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.
Tables
messages(messageid INTEGER, sender INTEGER, receiver INTEGER, has_reaction INTEGER, timestamp DATETIME)
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.
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.
Tables
messages(messageid INTEGER, sender INTEGER, receiver INTEGER, has_reaction INTEGER, timestamp DATETIME)
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.
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.
Tables
messages(messageid INTEGER, sender INTEGER, receiver INTEGER, has_reaction INTEGER, timestamp DATETIME)
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).
Community answers
Answer by corpana
SELECT COUNT(*) AS conversations_started_7d
FROM (
SELECT
LEAST(sender, receiver) AS user_a,
GREATEST(sender, receiver) AS user_b,
MIN(timestamp) AS first_msg_time
FROM messages
GROUP BY LEAST(sender, receiver), GREATEST(sender, receiver)
HAVING MIN(timestamp) >= '2023-08-01' AND MIN(timestamp) < '2023-08-08'
)
Answer by Jay123
hope you enjoy the questions, pls feel free to reply this comment to share your feedback