How to calculate active creators in SQL

Why active creators is a different metric

If you work on a UGC platform — TikTok, YouTube, Instagram, Notion, Figma, Reddit, anywhere some users produce content and the rest consume it — your MAU number is almost useless on its own. It tells you how many people opened the app last month, not whether the supply side of the marketplace is healthy. That is the job of active creators.

The hook scenario: it is Sunday evening, your PM is panicking because consumer engagement has been flat for two weeks, and the leadership review is Tuesday. They want to know whether the slowdown is a demand problem (fewer viewers) or a supply problem (fewer people uploading). MAU will not split that. You need DAC, WAC, MAC, and the weekly trend — by Tuesday morning. This guide walks through the queries that come up in product-analyst and data-scientist interviews at platforms like YouTube, TikTok, Snap, Spotify, Pinterest, Figma, and Notion. The patterns are the same on Snowflake, BigQuery, Redshift, or vanilla Postgres.

DAC, WAC, MAC defined

Three windows, same underlying question: did this account post at least one piece of qualifying content during the window?

DAC is daily active creators — accounts that published at least one post today. WAC is weekly active creators — at least one post in the trailing seven days. MAC is monthly active creators — at least one post in the trailing 28 or 30 days. The hierarchy is strict: every DAC for the day is in that day's WAC, every WAC is in the corresponding MAC. If you ever see WAC greater than MAC for the same anchor day, you have a bug — almost always a window-boundary error in the SQL.

"One post" is a placeholder. YouTube counts a video upload. TikTok counts a video post and, on some dashboards, a Live session. Spotify counts a track release. Figma counts a file created. Notion counts a published page. Ask the PM what counts as a creation before writing any SQL; if they cannot answer, write one down and circulate it. We will use a posts table with one row per qualifying creation — substitute your own event when you adapt.

Active creators in SQL

The canonical query. One row per anchor day, three trailing-window distinct counts.

SELECT
    d.day,
    (
        SELECT COUNT(DISTINCT p.author_id)
        FROM posts p
        WHERE p.created_at >= d.day
          AND p.created_at <  d.day + INTERVAL '1 day'
    ) AS dac,
    (
        SELECT COUNT(DISTINCT p.author_id)
        FROM posts p
        WHERE p.created_at >= d.day - INTERVAL '6 day'
          AND p.created_at <  d.day + INTERVAL '1 day'
    ) AS wac,
    (
        SELECT COUNT(DISTINCT p.author_id)
        FROM posts p
        WHERE p.created_at >= d.day - INTERVAL '27 day'
          AND p.created_at <  d.day + INTERVAL '1 day'
    ) AS mac
FROM (
    SELECT DISTINCT created_at::DATE AS day FROM posts
    WHERE created_at >= CURRENT_DATE - INTERVAL '60 day'
) d
ORDER BY d.day;

The windows are 1, 7, and 28 days — not 30 — because 28 cancels weekly seasonality, which is large on creator publishing (upload Mondays, batch on weekends). The right boundary is exclusive to avoid double-counting midnight posts. And COUNT(DISTINCT ...) OVER (...) is not supported as a window function in Postgres or Redshift; the correlated subquery is the cross-vendor portable pattern.

For a single ad-hoc number — "how many creators last month" — collapse to one scalar:

SELECT COUNT(DISTINCT author_id) AS mac
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '30 day';

The mistake here is to write WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE) instead, which gives you a month-to-date count that is structurally smaller than a trailing 30-day count for the first 27 days of the month.

Creator stickiness

Stickiness for creators is the share of monthly creators who create on any given day. High creator stickiness means a small group is doing most of the work — power users carrying the platform. Low creator stickiness means the monthly creator base is broad but shallow, with most accounts posting once a month and then disappearing.

WITH last_30 AS (
    SELECT DISTINCT author_id, created_at::DATE AS day
    FROM posts
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 day'
),
mac AS (
    SELECT COUNT(DISTINCT author_id) AS mac FROM last_30
),
dac_avg AS (
    SELECT AVG(daily_count)::NUMERIC AS dac_avg
    FROM (
        SELECT day, COUNT(DISTINCT author_id) AS daily_count
        FROM last_30
        GROUP BY day
    ) t
)
SELECT
    dac_avg.dac_avg,
    mac.mac,
    ROUND(dac_avg.dac_avg / NULLIF(mac.mac, 0), 3) AS creator_stickiness
FROM dac_avg, mac;

Benchmarks vary by category. For a daily-cadence platform like Twitter or TikTok, around 0.20 is healthy. For a weekly-cadence platform like YouTube or Substack, 0.05 to 0.10 is normal because most creators publish once or twice a week. Benchmark against your own category, not the global "good" number. The interview follow-up is almost always "what does a falling creator stickiness mean?" — either the broader creator base is becoming less engaged (bad), or new creators are flooding in faster than they activate to a regular cadence (often fine, the denominator is growing). To tell which, decompose MAC into new versus returning.

Weekly creator trend

WAC by week is the cleanest single chart for a leadership deck. It removes daily noise, captures the weekly publishing rhythm, and is comparable across the year if you bucket on the same weekday boundary.

SELECT
    DATE_TRUNC('week', created_at)::DATE AS week,
    COUNT(DISTINCT author_id)            AS wac
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '26 weeks'
GROUP BY 1
ORDER BY 1;

Rising WAC means the supply side is healthy. Flat WAC with growing MAU is the classic warning sign: audience broadening, creator base not keeping up, content saturation will hit retention in a quarter or two. Falling WAC is a five-alarm fire and almost always points to a payouts change, a recommendation-algorithm change that depressed reach, or a moderation event that scared creators off.

New vs returning creators

Splitting MAC into new and returning is the supply-side counterpart of the new-versus-returning MAU split. A platform with rising MAC driven entirely by new creators looks great on the surface and is one acquisition slowdown away from collapse.

WITH author_first AS (
    SELECT author_id, MIN(created_at)::DATE AS first_post_date
    FROM posts
    GROUP BY author_id
),
last_30 AS (
    SELECT DISTINCT author_id
    FROM posts
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 day'
)
SELECT
    COUNT(*) FILTER (WHERE first_post_date >= CURRENT_DATE - INTERVAL '30 day') AS new_mac,
    COUNT(*) FILTER (WHERE first_post_date <  CURRENT_DATE - INTERVAL '30 day') AS returning_mac
FROM last_30
JOIN author_first USING (author_id);

In a healthy growing creator economy, both new and returning grow week over week. In a saturating platform, new flattens and returning grows. In a decaying platform, new looks fine on the back of a creator-acquisition push but returning quietly drops — headline MAC holds for a few months, then falls off a cliff when the acquisition budget gets cut. This failure mode is more abrupt than on the consumer side, because creators are a smaller and more concentrated population.

Common pitfalls

The first trap is reporting MAU as if it were MAC. They are different populations. On a typical platform MAC is two to five percent of MAU — sometimes one percent on a heavily consumer-skewed product, sometimes twenty percent on a creator-first product like Substack. Name the columns explicitly (mau_app_open, mac_post_created) and never use the bare word "active" on a dashboard without a qualifier.

The second trap is including spam bots and inauthentic accounts in the active-creator count. Bot networks generate massive post volume, often more than human creators in raw count, but they are not real creators. If trust-and-safety is removing bots in the background and you do not join against the removal list, your MAC will lurch around as bot waves come and go. Join against a trusted_authors table at the very first step of the query, before any window or distinct count.

The third trap is treating WAC and MAC as interchangeable in a chart legend. MAC is always greater than or equal to WAC, which is always greater than or equal to DAC, for the same anchor date. If a chart shows WAC above MAC, the window boundaries are wrong — usually an off-by-one on the trailing interval. Sanity-check the strict hierarchy on a single date before you ship the dashboard.

The fourth trap is comparing windows of different lengths and calling it a trend. Plotting DAC and MAC on a single time-series chart gives you a stickiness pattern, not a trend. If you want a trend, plot DAC over time and MAC over time as separate series.

The fifth trap, the most subtle, is ignoring the power-law distribution when you set the "one post" threshold. The median creator on most platforms posts well under once a month. If your active-creator definition requires one post in 30 days, you are counting almost the entire registered creator base as active, which makes the metric meaningless. Three or more qualifying posts in 30 days is a better threshold — it filters one-and-done accounts and concentrates the metric on creators actually building a habit.

Optimization tips

Rolling-window distinct counts on a posts table are the most expensive analytics workload on most creator platforms. Materialize a daily snapshot — daily_creator_metrics(day, dac, wac, mac, new_mac, returning_mac, stickiness) — refreshed once a day by a scheduled job in your transformation tool. A correlated subquery over a year of daily values on a billion-row posts table will burn warehouse credits; a sub-second read from the snapshot will not.

Partition the posts table by date. On Snowflake, cluster by created_date; on BigQuery, use partitioned tables with _PARTITIONDATE; on Postgres, use declarative partitioning by month. The 28-day window scan should touch one or two partitions, not the whole table. If a MAC query is scanning the entire history, partition pruning is not firing — check the EXPLAIN plan and the predicate types.

For the distinct count itself, HyperLogLog approximations (APPROX_COUNT_DISTINCT on Snowflake and BigQuery) are an order of magnitude faster than exact distinct counts and accurate to within roughly one percent. Operational dashboards do not need exact precision. Reserve the exact COUNT(DISTINCT author_id) for financial reporting where the number gets audited.

If you want to drill SQL questions like this until rolling-window creator metrics become muscle memory, NAILDD is launching with 500+ SQL problems built around exactly this pattern.

Related reading

• How to calculate MAU in SQL
• How to calculate churn in SQL
• Actions per session in SQL
• SQL window functions interview questions
• A/B testing peeking mistake

FAQ

What is the definition of an active creator?

An account that published at least one qualifying piece of content during the window — daily for DAC, seven days for WAC, twenty-eight or thirty days for MAC. The "at least one" threshold is a starting point. On platforms with a long tail of one-and-done accounts, three or more posts in thirty days is a more useful threshold because it filters drive-by uploaders and concentrates the metric on creators who are actually building a publishing habit.

How does DAC differ from DAU?

DAU counts everyone who opened the app today — consumers and creators alike. DAC counts only the subset of accounts that posted today. On most large platforms DAC is five to ten percent of DAU. A news reader might have DAC at one percent of DAU; a creator-first product like Substack might have DAC closer to twenty or thirty percent. DAU is demand, DAC is supply.

What is a good MAC growth rate?

For a healthy creator platform, twenty to thirty percent year over year is normal. Below ten percent usually signals saturation in the existing creator base and a need to expand into new creator segments — new geographies, content formats, tools — or push monetization to retain the existing base. Sustained negative growth almost always points to a payouts or moderation issue.

What is a reasonable creator stickiness benchmark?

Between 0.10 and 0.15 is the broad middle for daily-cadence platforms like TikTok or Twitter. Above 0.20 indicates a power-user-heavy distribution where a small group is doing most of the work — fine when intentional, fragile when accidental. Below 0.05 is typical of weekly-cadence platforms like YouTube or Substack, and that is healthy for the category. Benchmark against your own platform's history first and the category average second.

Should I track new versus returning creators separately?

Yes, and the answer to the panicky Sunday-evening question almost always lives in that decomposition. New-creator MAC growth tells you whether acquisition and onboarding are working. Returning-creator MAC growth tells you whether the existing base is staying engaged. A platform that grows headline MAC entirely on new creators is one acquisition slowdown away from collapse. You want both growing — and you need the split to know which problem to solve first.