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.
sender_id, recipient_id, and calendar day derived from sent_at.message_date, sender_id, and recipient_id.| 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 |
| 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 |
| sender_id | recipient_id | message_date | message_count |
|---|---|---|---|
| 101 | 202 | 2024-06-01 | 4 |
| 103 | 204 | 2024-06-02 | 4 |
| Column | Type | Description |
|---|---|---|
| message_idPK | INT | Unique message event identifier |
| sender_id | INT | User who sent the message |
| recipient_id | INT | User who received the message |
| sent_at | TIMESTAMP | Timestamp when the message was sent |
| message_surface | VARCHAR(50) | Meta surface that generated the message event |
| message_text | TEXT | Message body text |
| 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 |
| 6 | 101 | 203 | 2024-06-01 12:05:00 | ig_reels_share | |
| 4 | 101 | 202 | 2024-06-01 16:20:00 | messenger | |
| 9 | 103 | 204 | 2024-06-02 08:00:00 | messenger | gm |
| 10 | 103 | 204 | 2024-06-02 08:05:00 | messenger | gm again |
| 12 | 103 | 204 | 2024-06-02 09:00:00 | ig_reels_share | save this |
| 13 | 103 | 204 | 2024-06-02 21:00:00 | messenger | last ping |
| 7 | 101 | 202 | 2024-06-02 09:00:00 | messenger | next day |
| 14 | 105 | 2024-06-02 10:00:00 | messenger | orphan event | |
| 15 | 106 | 207 | messenger | unsent draft | |
| 1 | 6 | 39 | 2024-05-31 02:44:20 | group_chat | last ping |
| 2 | 88 | 25 | 2024-06-01 18:18:40 | instagram_profile_share | sent from fb groups |
| 3 | 73 | 96 | 2024-06-02 19:04:51 | reels_share_sheet | follow up |
| 4 | 95 | 37 | 2024-05-29 16:28:46 | business_inbox | attachment only |
| 5 | 34 | 61 | 2024-06-02 06:04:00 | facebook_feed_share | ping |
| 6 | 13 | 41 | 2024-05-31 08:02:37 | creator_inbox | sent from fb groups |
| 7 | 49 | 79 | 2024-06-04 10:33:57 | facebook_search_share | are you there |
| 8 | 46 | 100 | 2024-06-04 17:22:34 | lite_messenger | null |
| 9 | 85 | 55 | null | marketplace_chat | group link |
| 10 | 71 | 69 | 2024-06-05 06:39:11 | group_chat | did you watch the reels clip |
| 11 | 26 | 12 | 2024-05-31 15:49:55 | ads_click_to_message | see you later |
| 12 | 45 | 31 | null | fb_groups_share | can you join the group |
| 13 | 70 | 89 | null | creator_inbox | unsent draft |
| 14 | 44 | 84 | null | ig_reels_share | possible srm issue |
| 15 | 98 | 58 | 2024-05-30 22:26:28 | instagram_save_share | thanks |
| sender_id | recipient_id | message_date | message_count |
|---|---|---|---|
| 101 | 202 | 2024-06-01 | 5 |
| 103 | 204 | 2024-06-02 | 4 |