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.
Quick Answer: 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.