Task
On Instagram Reels, the growth team wants to understand posting consistency for active creators in the AARRR activation stage. Write a SQL query to find, for each user who posted at least 2 times in 2024, the number of days between their first and last post in 2024.
Requirements
- Use only posts created in calendar year 2024.
- Return one row per user with at least 2 posts in 2024.
- Output the user's
user_id, first_post_date, last_post_date, and days_between_posts.
- Calculate
days_between_posts as the difference in days between the first and last 2024 post.
- Order results by
days_between_posts descending, then user_id ascending.
Table Definition
| column_name | type | description |
|---|
| post_id | INT | Unique post identifier |
| user_id | INT | Instagram user identifier |
| post_surface | VARCHAR(50) | Surface where the post was created, such as reels or fb_groups |
| created_at | TIMESTAMP | Post creation timestamp |
| caption_text | TEXT | Optional post caption |
Sample Data
| post_id | user_id | post_surface | created_at | caption_text |
|---|
| 1008 | 104 | reels | 2024-12-31 23:50:00 | year end reel |
| 1002 | 101 | reels | 2024-03-05 09:30:00 | first spring reel |
| 1005 | 102 | fb_groups | 2024-07-20 08:00:00 | group update |
| 1001 | 101 | reels | 2024-01-10 10:00:00 | new year reel |
| 1010 | 105 | reels | 2023-12-31 23:59:00 | pre-2024 post |
| 1004 | 102 | reels | 2024-02-14 12:00:00 | valentine reel |
| 1007 | 104 | reels | 2024-01-01 00:05:00 | kickoff reel |
| 1003 | 101 | reels | 2024-11-01 18:45:00 | halloween recap |
| 1009 | 105 | reels | 2024-01-01 00:01:00 | first 2024 reel |
| 1006 | 103 | reels | 2024-06-01 14:00:00 | single post only |
| 1011 | 105 | reels | 2024-01-01 08:00:00 | morning reel |
| 1012 | 106 | reels | NULL | missing timestamp |
Expected Output
| user_id | first_post_date | last_post_date | days_between_posts |
|---|
| 104 | 2024-01-01 | 2024-12-31 | 365 |
| 101 | 2024-01-10 | 2024-11-01 | 296 |
| 105 | 2024-01-01 | 2024-01-01 | 0 |
