May 18, 2026·11 min read

How to calculate CTR in SQL

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

Contents:

What CTR is and why it matters

CTR stands for Click-Through Rate, the share of impressions that turned into clicks. It is the lever that quietly controls CPC, CAC, ROAS, and payback. Plug it into CAC = CPM / (CTR * CR) and every percentage point of CTR you lose raises customer acquisition cost. If CTR drops, CPC rises through the auction, CAC inflates, and the marketing lead starts asking uncomfortable questions on the Monday standup.

In an interview, a hiring manager at Meta, Google, Stripe, or DoorDash will ask you to compute CTR by channel, then week over week, then add a confidence interval, then compare two creatives from a live A/B test. Each step is a single GROUP BY, but traps in the denominator, casting, and weighted versus simple aggregation trip up candidates inside ten minutes. Typical schema is event-grained ad_events(user_id, event_type, channel, campaign, event_at) or daily aggregated ad_stats(channel, campaign, day, impressions, clicks).

The SQL formula

The arithmetic is trivial. CTR is clicks divided by impressions, multiplied by 100 for a percentage. The interesting part is everything around the division — which clicks count, which impressions count, how you guard against zero, and at what grain you aggregate before you divide.

CTR = clicks / impressions * 100%

Always wrap the divisor in NULLIF(SUM(impressions), 0) so a zero-impression campaign returns NULL rather than crashing. Cast at least one side to numeric — 100.0 * SUM(clicks) or SUM(clicks)::NUMERIC — because integer division silently floors a 3 percent CTR to zero.

Overall CTR for a period

This is the dashboard tile when leadership asks for one number. Run it against ad_events for raw events.

SELECT
    COUNT(*) FILTER (WHERE event_type = 'click')::FLOAT /
    NULLIF(COUNT(*) FILTER (WHERE event_type = 'impression'), 0) AS ctr
FROM ad_events
WHERE event_at >= CURRENT_DATE - INTERVAL '30 days';

If your warehouse does not support FILTER — older Redshift, for example — the portable form uses a conditional sum. If CTR reads zero on a campaign that is delivering, check the cast — without 100.0 or ::FLOAT, integer division rounds any rate below 1 percent down to nothing.

SELECT
    100.0 * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0) AS ctr_pct
FROM ad_events
WHERE event_at >= CURRENT_DATE - INTERVAL '30 days';

CTR by channel

A single blended number hides the diagnostic signal. To see which channel is dragging the average, break the calculation down with a GROUP BY on channel, ordered by CTR descending.

SELECT
    channel,
    SUM(impressions) AS imps,
    SUM(clicks) AS clicks,
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr_pct
FROM ad_stats
WHERE day >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY channel
ORDER BY ctr_pct DESC;

Sort a second view by spend so you can tell which channels move the blended number versus which have weird CTR on tiny budgets. A 12 percent CTR on a 50 dollar branded campaign is operationally irrelevant. A 0.4 percent CTR on a 200 thousand dollar prospecting campaign wakes the CMO at 2am.

Week-over-week change

Daily numbers are noisy. Week-over-week is what the executive team actually reads, and LAG gives you both the prior week and the delta in one row.

WITH weekly AS (
    SELECT
        DATE_TRUNC('week', day) AS week,
        100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr
    FROM ad_stats
    GROUP BY 1
)
SELECT
    week,
    ctr,
    LAG(ctr) OVER (ORDER BY week) AS prev_ctr,
    ctr - LAG(ctr) OVER (ORDER BY week) AS wow_diff,
    (ctr - LAG(ctr) OVER (ORDER BY week)) /
        NULLIF(LAG(ctr) OVER (ORDER BY week), 0) * 100 AS wow_pct
FROM weekly
ORDER BY week;

The percent-change column lets the reader tell whether a 0.3 percentage-point swing is a 5 percent shift on a high-CTR search campaign or a 60 percent collapse on a low-CTR display network.

CTR by campaign with traffic share

For paid media reviews, CTR alone is not enough — you also need to know how much of the impression budget each campaign consumed. A 0.6 percent CTR on a campaign carrying 40 percent of impressions is the report you bring to weekly review.

SELECT
    campaign,
    SUM(impressions) AS imps,
    SUM(clicks) AS clicks,
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr_pct,
    100.0 * SUM(impressions) /
        SUM(SUM(impressions)) OVER () AS traffic_share_pct
FROM ad_stats
WHERE day >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY campaign
ORDER BY imps DESC;

The nested SUM(SUM(impressions)) OVER () is the standard share-of-total pattern with no second pass, and it works on Postgres, Snowflake, BigQuery, and Redshift.

Blended CTR weighted by impressions

This is the question that most often separates senior from junior in an interview. A simple average of campaign-level CTRs weights every campaign equally regardless of traffic. Blended CTR weights by impression volume.

-- WRONG: simple mean of campaign CTRs
SELECT AVG(ctr_pct) AS naive_avg_ctr
FROM (
    SELECT campaign,
           100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr_pct
    FROM ad_stats
    GROUP BY campaign
) t;

-- RIGHT: weighted by impressions
SELECT
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS blended_ctr_pct
FROM ad_stats;

Imagine one remarketing campaign at 10 percent CTR carrying 80 percent of impressions, plus ten experiments at 0.5 percent CTR with the remaining 20 percent. Simple mean is about 1.4 percent. Weighted is about 8.1 percent. Same data, factor of five apart.

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

CTR with a confidence interval

For any campaign comparison where you have to defend "is the difference real," CTR needs a confidence interval. The Wald approximation works above a few thousand impressions, which most paid campaigns clear inside a day.

SELECT
    channel,
    clicks,
    impressions,
    ctr,
    ctr - 1.96 * SQRT(ctr * (1 - ctr) / impressions) AS ci_lower,
    ctr + 1.96 * SQRT(ctr * (1 - ctr) / impressions) AS ci_upper
FROM (
    SELECT
        channel,
        SUM(clicks) AS clicks,
        SUM(impressions) AS impressions,
        SUM(clicks)::FLOAT / NULLIF(SUM(impressions), 0) AS ctr
    FROM ad_stats
    WHERE day >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY channel
) t;

Two channels with overlapping confidence intervals are statistically indistinguishable, no matter what the point estimates say. For very low CTRs near 0.1 percent where the normal approximation breaks, switch to Wilson or Clopper-Pearson.

A/B test comparison

For A/B testing creatives, the query mirrors the channel breakdown with variant in place of channel. The interesting follow-up is the significance test.

SELECT
    variant,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr_pct
FROM experiment
WHERE day >= '2026-05-01'
GROUP BY variant;

For significance in pure SQL, compute pooled rate p = (c1 + c2) / (n1 + n2), standard error sqrt(p * (1 - p) * (1/n1 + 1/n2)), and z statistic (p1 - p2) / SE. Compare absolute z to 1.96 for a 95 percent two-sided test. The A/B testing peeking mistake writeup covers why you should not call winners mid-experiment.

Typical CTR benchmarks

Search ads run 3 to 5 percent on Google, branded queries routinely above 10 percent. Display ads sit in 0.1 to 0.5 percent because users are not in active search mode. Paid social — Meta, TikTok, LinkedIn — usually lands in 1 to 2 percent. Email on delivered runs 2 to 5 percent on a healthy list. Push notifications reach 1 to 7 percent on a well-segmented audience. If your display CTR sits at 3 percent, that is almost certainly bot traffic.

Common pitfalls

The first trap is dividing without a NULL guard. A campaign with zero impressions crashes a Postgres query with a division-by-zero error and silently returns infinity or NaN in BigQuery and Snowflake depending on the version. The fix is NULLIF(SUM(impressions), 0) so an empty campaign returns NULL and the dashboard renders an em dash. This single idiom catches more production bugs than any other pattern in marketing analytics.

The second trap is integer division. In Postgres, 100 * 5 / 200 evaluates to zero because both sides are integers and the result is floored. Use 100.0 * 5 / 200 or cast one side to NUMERIC and you get 2.5 percent. This bug almost never throws an error, so it can sit in production for months quietly flattening every CTR below 1 percent to zero.

The third trap is the average of averages. Computing the simple mean of campaign-level CTRs and calling it "the blended CTR" is the most common interview mistake at the senior data analyst level. Always weight by impressions, which reduces to total clicks divided by total impressions. The simple-mean answer is only correct when every campaign has identical impression volume — almost never true in production.

The fourth trap is invalid traffic. Bot clicks, click farms, and accidental swipes inflate the numerator and push reported CTR above the real engagement rate. Google Ads and Meta filter most invalid traffic before final reports, but the lag is two to three days on Google and up to seven on smaller networks. CTR from same-day data will later be revised downward. Label same-day numbers as preliminary.

The fifth trap is the impression definition. Meta reports "impressions" — times your ad was served — and "reach" — unique people who saw it. The two can differ by a factor of two or three on retargeting campaigns. Dividing clicks by reach instead of impressions inflates CTR by the average frequency, which makes the number look great and the downstream CAC math fall apart.

Optimization tips

For daily and channel queries on a 30 to 90 day window, the dominant cost is the scan of ad_stats. Partition by day and cluster by channel on Snowflake or BigQuery — this drops a 90-day scan from seconds to milliseconds on multi-billion-row tables. On Redshift, set the same fields as the sort key.

If the CTR dashboard refreshes daily, materialize the channel-day aggregate as a separate table or a Snowflake dynamic table refreshed hourly. Downstream queries for blended, weekly trends, and campaign rollups then run against thousands of pre-aggregated rows instead of billions of raw events. On Postgres, index ad_stats on (channel, day) and (campaign, day).

If you want to drill marketing analytics SQL questions like this every day before your next interview, NAILDD is launching with 500+ SQL problems covering exactly this pattern.

FAQ

Is CTR measured on impressions or on delivered?

For paid advertising on Google, Meta, LinkedIn, TikTok, and the display networks, CTR is measured on impressions — the count of times your ad rendered on a user's screen. For email the convention shifts. CTR there is almost always reported on delivered, meaning emails that reached the inbox and were not bounced or filtered as spam. The conventions exist because impression measurement is platform-controlled on paid ads, while email deliverability is itself a meaningful denominator.

How do I report blended CTR correctly?

Blended CTR is always a weighted average by impressions, not a simple mean of campaign-level rates. Mathematically this reduces to SUM(clicks) / SUM(impressions) over the dataset. The simple-mean approach over-weights small campaigns and under-weights large ones, which can move the reported number by a factor of two or more in a real portfolio.

Branded search keywords run 10 to 20 percent CTR because the user already knew what they wanted. Non-branded keywords land in the 3 to 5 percent range on Google. CTR below 1 percent on a non-branded campaign usually signals one of three things — ad copy that does not match search intent, ad rank too low to appear above the fold, or audience targeting pulling in mismatched queries.

How long does an A/B test on CTR need to run?

Long enough for confidence intervals on the two variants to stop overlapping. For a baseline CTR around 2 percent and a target lift of 10 percent, you typically need 30 to 50 thousand impressions per variant for a clean 95 percent significant result. For a 0.5 percent baseline on display, the same relative lift needs hundreds of thousands per variant. Use a power calculator before launching.

Meta reports two distinct click metrics. "All clicks" includes every interaction on the ad — likes, reactions, profile clicks, comments, carousel expansion, plus the link click itself. "Link clicks" counts only the click that drives a user to your landing page. On engagement-heavy creatives, all clicks can exceed link clicks by 30 to 50 percent. Standardize on link clicks across platforms.

Related reading