Task
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.
Requirements
- Count only messages with a non-null
sender_user_id sent in the last 7 days relative to CURRENT_DATE.
- Join to the
users table to return the sender's username.
- Return
user_id, username, and messages_sent.
- Sort by
messages_sent descending, then user_id ascending to break ties, and return only the top 3 rows.
Table Definitions
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 |
Sample Data
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 |
Expected Output
Assume CURRENT_DATE = '2024-06-13'.
| user_id | username | messages_sent |
|---|
| 101 | ava_reels | 3 |
| 102 | leo_fb | 3 |
| 103 | maya_save | 3 |
