Analyze User Engagement and Spammer Read-Rate in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
messages
+------------+------------+-------------+--------------+-----------+
| sender_id | receiver_id| message_id | sent_date | read_date |
+------------+------------+-------------+--------------+-----------+
| 1232412 | 12342 | 124 | 2024-01-01 | 2024-01-01|
| 6534354 | 423452 | 342 | 2024-01-02 | NULL |
| 1234412 | 34214 | 425 | 2024-01-01 | 2024-01-05|
+------------+------------+-------------+--------------+-----------+
violating_scores
+---------+----------------+-------------+
| user_id | violation_type | p_violating |
+---------+----------------+-------------+
| 1232412 | spam | 0.4 |
| 6534354 | scam | 0.9 |
| 1234412 | impersonation | 0.1 |
+---------+----------------+-------------+
##### Scenario
Messaging platform needs SQL analyses of user engagement and spammer behavior in the past week.
##### Question
How many users received more than 10 messages in a single day during the last 7 days? What is the average read-rate for spammers over the last week? Read-rate = (read messages sent by spammers) / (all messages sent by spammers).
##### Hints
Filter by last 7 days; join spammers from violating_scores; aggregate counts and ratios.
Overview: This question evaluates SQL-based data manipulation and analytics skills—specifically joins, time-window filtering, aggregation, and metric calculation for measuring user engagement and spammer read-rate—within the Data Manipulation (SQL/Python) domain.
Using `messages` and `violating_scores`, return one row for 2025-05-26 through 2025-06-01 inclusive based on `sent_date`: `heavy_receivers_count`, the number of distinct receivers who received more than 10 messages on at least one day in the range; and `spammer_read_rate`, the fraction of messages sent by users whose `violation_type = 'spam'` that have `read_date IS NOT NULL`, rounded to 2 decimal places.
Tables
messages(sender_id INTEGER, receiver_id INTEGER, message_id INTEGER, sent_date DATE, read_date DATE)
violating_scores(user_id INTEGER, violation_type VARCHAR(50), p_violating DECIMAL(3,2))
Hints
- Filter messages to the fixed date range 2025-05-26 through 2025-06-01 using sent_date.
- Identify spammers by joining messages to violating_scores where violation_type = 'spam'.