At Meta, a Product Growth Analyst may need to monitor recent messaging activity in Messenger as part of broader AARRR engagement analysis for Facebook Groups and sharing surfaces. Write a SQL query to find the top 3 users who sent the most messages in the last 7 days.
sender_user_id sent in the last 7 days relative to CURRENT_DATE.users table to return the sender's username.user_id, username, and messages_sent.messages_sent descending, then user_id ascending to break ties, and return only the top 3 rows.users| column | type | description |
|---|---|---|
| user_id | INT | Unique Meta user ID |
| username | VARCHAR(50) | Messenger username |
| account_status | VARCHAR(20) | User account status |
| signup_date | DATE | Account creation date |
messages| column | type | description |
|---|---|---|
| message_id | INT | Unique message ID |
| sender_user_id | INT | User who sent the message |
| recipient_user_id | INT | User who received the message |
| sent_at | TIMESTAMP | Message send timestamp |
| surface | VARCHAR(30) | Meta surface where the message originated |
users| user_id | username | account_status | signup_date |
|---|---|---|---|
| 104 | zhang_ig | active | 2023-11-20 |
| 101 | ava_reels | active | 2023-01-15 |
| 108 | groups_growth | active | 2024-04-18 |
| 103 | maya_save | active | 2022-12-01 |
| 110 | null_case | deactivated | 2024-06-01 |
| 102 | leo_fb | active | 2023-05-10 |
| 106 | cuped_test | active | 2024-01-09 |
| 105 | no_messages | active | 2024-02-14 |
| 109 | srm_watch | active | 2024-05-12 |
| 107 | kfactor_pm | suspended | 2023-08-08 |
messages| message_id | sender_user_id | recipient_user_id | sent_at | surface |
|---|---|---|---|---|
| 11 | 103 | 101 | 2024-06-09 08:00:00 | instagram_save_share |
| 2 | 101 | 103 | 2024-06-12 09:00:00 | instagram_reels |
| 7 | 102 | 101 | 2024-06-11 18:00:00 | fb_groups |
| 1 | 101 | 102 | 2024-06-13 10:15:00 | messenger |
| 14 | 106 | 103 | 2024-06-12 15:00:00 | experiments_dashboard |
| 5 | 101 | 106 | 2024-06-10 14:00:00 | messenger |
| 3 | 101 | 104 | 2024-06-08 20:30:00 | instagram_reels |
| 9 | 103 | 102 | 2024-06-13 07:45:00 | messenger |
| 12 | 103 | 104 | 2024-06-07 23:59:00 | messenger |
| 4 | 102 | 101 | 2024-06-13 11:00:00 | fb_groups |
| 10 | 104 | 103 | 2024-06-13 12:30:00 | instagram_reels |
| 6 | 102 | 105 | 2024-06-09 16:00:00 | messenger |
| 8 | 102 | 106 | 2024-06-05 10:00:00 | fb_groups |
| 13 | null | 101 | 2024-06-13 09:30:00 | messenger |
Assume CURRENT_DATE = '2024-06-13'.
| user_id | username | messages_sent |
|---|---|---|
| 101 | ava_reels | 3 |
| 102 | leo_fb | 3 |
| 103 | maya_save | 3 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Meta user ID |
| username | VARCHAR(50) | Messenger username |
| account_status | VARCHAR(20) | Current account status |
| signup_date | DATE | Account creation date |
| Column | Type | Description |
|---|---|---|
| message_idPK | INT | Unique message ID |
| sender_user_id | INT | User who sent the message |
| recipient_user_id | INT | User who received the message |
| sent_at | TIMESTAMP | Timestamp when the message was sent |
| surface | VARCHAR(30) | Meta product surface where the message originated |
| user_id | username | account_status | signup_date |
|---|---|---|---|
| 104 | zhang_ig | active | 2023-11-20 |
| 101 | ava_reels | active | 2023-01-15 |
| 108 | groups_growth | active | 2024-04-18 |
| 103 | maya_save | active | 2022-12-01 |
| 110 | null_case | deactivated | 2024-06-01 |
| 102 | leo_fb | active | 2023-05-10 |
| 106 | cuped_test | active | 2024-01-09 |
| 105 | no_messages | active | 2024-02-14 |
| 109 | srm_watch | active | 2024-05-12 |
| 107 | kfactor_pm | suspended | 2023-08-08 |
| 111 | zhang_ig | active | 2023-02-17 |
| 112 | maya_save | high_risk | 2023-11-20 |
| 113 | viral_loop_pm | returning | 2024-05-01 |
| 114 | cuped_runner | shadow_banned | 2024-04-28 |
| 115 | ig_save_power | suspended | 2023-04-14 |
| 116 | groups_growth | student | 2023-01-14 |
| 117 | null_case | inactive | 2024-04-03 |
| 118 | messenger_ops | limited | 2023-08-30 |
| 119 | engagement_lab | contractor | 2024-01-15 |
| 120 | no_messages | internal | 2024-04-27 |
| 121 | group_admin_22 | contractor | 2024-04-10 |
| 122 | meta_data_eng | employee | 2022-12-07 |
| 123 | creator_growth | null | 2023-10-12 |
| 124 | reels_viewer | active | 2023-09-11 |
| 125 | novelty_check | null | 2023-03-19 |
| 126 | cuped_test | contractor | 2023-09-01 |
| 127 | viral_loop_pm | on_hold | 2022-12-09 |
| 128 | save_metric_fan | new | 2024-03-12 |
| 129 | meta_data_eng | active | 2023-11-06 |
| message_id | sender_user_id | recipient_user_id | sent_at | surface |
|---|---|---|---|---|
| 11 | 103 | 101 | 2024-06-09 08:00:00 | instagram_save_share |
| 2 | 101 | 103 | 2024-06-12 09:00:00 | instagram_reels |
| 7 | 102 | 101 | 2024-06-11 18:00:00 | fb_groups |
| 1 | 101 | 102 | 2024-06-13 10:15:00 | messenger |
| 14 | 106 | 103 | 2024-06-12 15:00:00 | experiments_dashboard |
| 5 | 101 | 106 | 2024-06-10 14:00:00 | messenger |
| 3 | 101 | 104 | 2024-06-08 20:30:00 | instagram_reels |
| 9 | 103 | 102 | 2024-06-13 07:45:00 | messenger |
| 12 | 103 | 104 | 2024-06-07 23:59:00 | messenger |
| 4 | 102 | 101 | 2024-06-13 11:00:00 | fb_groups |
| 10 | 104 | 103 | 2024-06-13 12:30:00 | instagram_reels |
| 6 | 102 | 105 | 2024-06-09 16:00:00 | messenger |
| 8 | 102 | 106 | 2024-06-05 10:00:00 | fb_groups |
| 13 | 101 | 2024-06-13 09:30:00 | messenger | |
| 15 | 103 | 104 | 2024-06-03 07:17:09 | instagram_save_share |
| 16 | 106 | 101 | 2024-06-12 03:00:27 | family_center |
| 17 | 103 | 101 | 2024-06-06 04:05:37 | instagram_reels |
| 18 | 103 | 104 | 2024-06-15 10:32:24 | facebook_feed |
| 19 | 101 | 101 | 2024-06-16 08:29:36 | search |
| 20 | 101 | 106 | 2024-06-03 03:40:24 | facebook_feed |
| 21 | 103 | 106 | 2024-06-04 01:15:23 | instagram_reels |
| 22 | 104 | 101 | 2024-06-12 09:58:33 | creator_studio |
| 23 | 103 | 101 | 2024-06-11 05:56:41 | lite_messenger |
| 24 | 102 | 106 | 2024-06-10 04:27:01 | community_help |
| 25 | 101 | 103 | 2024-06-04 07:08:45 | groups_chat |
| 26 | 106 | 102 | 2024-06-08 07:56:19 | marketplace |
| 27 | 101 | 104 | 2024-06-14 07:14:01 | business_inbox |
| 28 | 101 | 101 | 2024-06-16 02:52:32 | instagram_dm |
| 29 | 101 | 104 | 2024-06-11 05:13:41 | instagram_save_share |
| 30 | 102 | 101 | 2024-06-11 11:44:28 | instagram_save_share |
| user_id | username | messages_sent |
|---|---|---|
| 101 | ava_reels | 5 |
| 102 | leo_fb | 5 |
| 103 | maya_save | 4 |