Task
At Meta, the Messaging team wants to flag unusually high repeat sends between the same sender-recipient pair within a day. Write a SQL query to identify users who sent more than 3 messages to the same recipient in a single day.
Requirements
- Group messages by
sender_id, recipient_id, and calendar day derived from sent_at.
- Count only rows that represent actual sent messages.
- Return the sender, recipient, message date, and total messages sent that day.
- Include only sender-recipient-day combinations where the count is greater than 3.
- Order the output by
message_date, sender_id, and recipient_id.
Table Definition
| column | type | description |
|---|
| message_id | INT | Unique message event ID |
| sender_id | INT | Meta user who sent the message |
| recipient_id | INT | Meta user who received the message |
| sent_at | TIMESTAMP | Time the message was sent |
| message_surface | VARCHAR(50) | Surface where the message originated, such as Messenger or IG Reels share |
| message_text | TEXT | Message body; may be NULL for attachment-only sends |
Sample Data
| message_id | sender_id | recipient_id | sent_at | message_surface | message_text |
|---|
| 8 | 101 | 202 | 2024-06-01 18:45:00 | messenger | check this reel |
| 1 | 101 | 202 | 2024-06-01 09:00:00 | messenger | hey |
| 11 | 104 | 205 | 2024-06-01 13:00:00 | fb_groups_share | group link |
| 3 | 101 | 202 | 2024-06-01 11:30:00 | messenger | ping |
| 5 | 101 | 203 | 2024-06-01 12:00:00 | ig_reels_share | reel |
| 2 | 101 | 202 | 2024-06-01 10:15:00 | messenger | are you there |
Expected Output
| sender_id | recipient_id | message_date | message_count |
|---|
| 101 | 202 | 2024-06-01 | 4 |
| 103 | 204 | 2024-06-02 | 4 |
