Coding questions · Meta
Analyze User Engagement and Spammer Read-Rate in SQL
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.
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.
Heavy receivers and spammer read-rate over a fixed week
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.
Table messages
| column | type | constraints |
|---|---|---|
| sender_id | INTEGER | NOT NULL |
| receiver_id | INTEGER | NOT NULL |
| message_id | INTEGER | PRIMARY KEY, NOT NULL |
| sent_date | DATE | NOT NULL |
| read_date | DATE |
Sample data:
| message_id | read_date | receiver_id | sender_id | sent_date |
|---|---|---|---|---|
| 1 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 2 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 3 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 4 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 5 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 6 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 7 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 8 | 2025-05-28 | 1001 | 1232412 | 2025-05-28 |
| 9 | NULL | 1001 | 1232412 | 2025-05-28 |
| 10 | NULL | 1001 | 1232412 | 2025-05-28 |
| 11 | NULL | 1001 | 1232412 | 2025-05-28 |
| 12 | NULL | 1002 | 1232412 | 2025-05-30 |
| 13 | 2025-05-30 | 1003 | 6534354 | 2025-05-29 |
| 14 | 2025-05-31 | 1001 | 1234412 | 2025-05-30 |
Table violating_scores
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY, NOT NULL |
| violation_type | VARCHAR(50) | NOT NULL |
| p_violating | DECIMAL(3,2) | NOT NULL |
Sample data:
| p_violating | user_id | violation_type |
|---|---|---|
| 0.4 | 1232412 | spam |
| 0.9 | 6534354 | scam |
| 0.1 | 1234412 | impersonation |
Expected output
Single-row result with two columns: heavy_receivers_count (the number of distinct receiver_id values that had more than 10 messages on at least one sent_date between 2025-05-26 and 2025-06-01, inclusive) and spammer_read_rate (the fraction of messages sent by users with violation_type = 'spam' during that same date range that were read; a message is considered read if read_date is not NULL). For the sample data, receiver_id 1001 is the only heavy receiver (11 messages on 2025-05-28), and the spammer (user_id 1232412) sent 12 messages, 8 of which were read, giving a read-rate of 8/12 ≈ 0.67.
| heavy_receivers_count | spammer_read_rate |
|---|---|
| 1 | 0.67 |
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'.
- Group by receiver_id and sent_date, use HAVING COUNT(*) > 10 to find heavy-receiver days, then count distinct receivers.
- Compute read-rate as AVG(CASE WHEN read_date IS NOT NULL THEN 1.0 ELSE 0.0 END) over spammer-sent messages, and handle the case with no spammer messages.