Creator Engagement Rate by Month — SQL Aggregation Problem
Brands judge creators by engagement rate: interactions (likes + comments + shares) as a percentage of impressions.
- Difficulty: Medium
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #432
Problem statement
Brands judge creators by engagement rate: interactions (likes + comments + shares) as a percentage of impressions. Compute it per creator per calendar month of posted_at, over all of that month's posts together (total interactions × 100 ÷ total impressions), rounded to 2 decimals. If a month's posts have 0 impressions in total, its rate is NULL.
Return the columns creator_id, month (as 'YYYY-MM'), posts (the number of posts that month) and engagement_rate, ordered by creator_id, then month.
Tables
Table: post_stats
| Column | Type |
|---|---|
| post_id | int |
| creator_id | int |
| posted_at | datetime |
| impressions | int |
| likes | int |
| comments | int |
| shares | int |
Primary key: post_id.
One row per post with its lifetime counters (never NULL).
Examples
Example 1
post_stats
| post_id | creator_id | posted_at | impressions | likes | comments | shares |
|---|---|---|---|---|---|---|
| 1 | 7 | 2025-01-03 19:00:00 | 12000 | 900 | 120 | 60 |
| 2 | 7 | 2025-01-20 08:30:00 | 8000 | 350 | 40 | 10 |
| 3 | 7 | 2025-02-11 21:15:00 | 15000 | 1200 | 300 | 150 |
| 4 | 9 | 2025-01-31 23:59:00 | 4000 | 160 | 25 | 15 |
| 5 | 9 | 2025-02-01 00:05:00 | 0 | 0 | 0 | 0 |
| 6 | 9 | 2025-03-14 12:00:00 | 5000 | 410 | 33 | 7 |
Output
| creator_id | month | posts | engagement_rate |
|---|---|---|---|
| 7 | 2025-01 | 2 | 7.4 |
| 7 | 2025-02 | 1 | 11 |
| 9 | 2025-01 | 1 | 5 |
| 9 | 2025-02 | 1 | NULL |
| 9 | 2025-03 | 1 | 9 |
How to solve Creator Engagement Rate by Month
The month key is the first seven characters of the timestamp — DATE_FORMAT(posted_at, '%Y-%m') produces '2025-01' directly. Grouping by (creator_id, month) gives one row per creator-month, and COUNT(*) is that month's post count.
The rate must be a ratio of sums: total interactions over total impressions. Averaging each post's own rate would give a viral post with 50,000 impressions the same weight as one with 500, which is not what a brand means by the month's engagement.
A month whose posts all have 0 impressions would divide by zero. MySQL returns NULL for that and SQLite errors or returns NULL depending on settings, so make it explicit: NULLIF(SUM(impressions), 0) makes the denominator NULL, and the whole expression is NULL. The alternative uses CASE for the same guard and builds the month key with LEFT in a derived table, so the outer GROUP BY uses a plain column. The cost is one scan and one grouping.
Reference solution (MySQL)
SELECT creator_id,
DATE_FORMAT(posted_at, '%Y-%m') AS month,
COUNT(*) AS posts,
ROUND(SUM(likes + comments + shares) * 100 / NULLIF(SUM(impressions), 0), 2) AS engagement_rate
FROM post_stats
GROUP BY creator_id, DATE_FORMAT(posted_at, '%Y-%m')
ORDER BY creator_id, monthAnother way
SELECT creator_id, month, COUNT(*) AS posts,
CASE WHEN SUM(impressions) = 0 THEN NULL
ELSE ROUND((SUM(likes) + SUM(comments) + SUM(shares)) * 100 / SUM(impressions), 2) END AS engagement_rate
FROM (SELECT creator_id, LEFT(posted_at, 7) AS month, impressions, likes, comments, shares FROM post_stats) t
GROUP BY creator_id, month
ORDER BY creator_id, month← Mutual Follows Between Active Accounts · Each Creator's Most-Liked Post →