Creator Engagement Rate by Month

Medium
432.

Creator Engagement Rate by Month

MediumAggregationDates

MySQL

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

ColumnType
post_idint
creator_idint
posted_atdatetime
impressionsint
likesint
commentsint
sharesint

Primary key: post_id.

One row per post with its lifetime counters (never NULL).

Example

The rows must come back in the order shown.

post_stats

post_idcreator_idposted_atimpressionslikescommentsshares
172025-01-03 19:00:001200090012060
272025-01-20 08:30:0080003504010
372025-02-11 21:15:00150001200300150
492025-01-31 23:59:0040001602515
592025-02-01 00:05:000000
692025-03-14 12:00:005000410337

Output

creator_idmonthpostsengagement_rate
72025-0127.4
72025-02111
92025-0115
92025-021NULL
92025-0319
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
post_idint
creator_idint
posted_atdatetime
impressionsint
likesint
commentsint
sharesint

Primary key: post_id.

One row per post with its lifetime counters (never NULL).

Examples

Example 1

post_stats

post_idcreator_idposted_atimpressionslikescommentsshares
172025-01-03 19:00:001200090012060
272025-01-20 08:30:0080003504010
372025-02-11 21:15:00150001200300150
492025-01-31 23:59:0040001602515
592025-02-01 00:05:000000
692025-03-14 12:00:005000410337

Output

creator_idmonthpostsengagement_rate
72025-0127.4
72025-02111
92025-0115
92025-021NULL
92025-0319

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, month

Another 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 →