AdTech SQL for Solution Engineers & TAMs

ChatGPT-style interactive guide • 27 Core Modules • DBeaver MySQL Sandbox

⚡ Execution Order 🗄️ Database DDL 📊 CTR / CPC / CPM 🎯 Fill Rate Waterfall 🧪 10 Exercises

1. The mental model first

Imagine you're working with an AdTech platform.

An advertiser runs campaigns:

Advertiser → Campaign → Ad Group → Impressions / Clicks / Spend

A publisher generates inventory:

Publisher → Ad Request → Impression → Revenue

For example:

Sample AdTech Campaign Records
Campaign Advertiser Impressions Clicks Spend
Nike CTV Nike 1,000,000 8,500 ₹80,000
CTV Display Vega 500,000 3,000 ₹30,000
PhonePe Video PhonePe 2,000,000 12,000 ₹100,000

From this raw data, SQL allows you to answer:

"Which campaign is performing badly?"
"Which publisher makes the most money?"
"Why did yesterday's delivery drop?"
"Which campaigns have impressions but zero clicks?"

That's exactly the type of SQL useful for AdTech Solution Engineering / TAM / Technical Account Management.

2. First: understand SQL execution order

This is extremely important for interviews.

When you write:

SQL 
SELECT
    campaign_id,
    SUM(spend)
FROM campaign_performance
WHERE date >= '2026-09-01'
GROUP BY campaign_id
HAVING SUM(spend) > 10000
ORDER BY SUM(spend) DESC;

You write SQL approximately in this order:

SQL Written Order 
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But conceptually SQL processes it more like:

1. FROM — Pick target table
↓
2. JOIN — Merge related dimensions
↓
3. WHERE — Filter rows BEFORE grouping
↓
4. GROUP BY — Aggregate into buckets
↓
5. HAVING — Filter groups AFTER grouping
↓
6. SELECT — Compute columns & aliases
↓
7. ORDER BY — Sort final output rows

The important interview distinction:

WHERE vs HAVING

WHERE filters rows before aggregation.

SQL 
WHERE impressions > 1000

HAVING filters groups after aggregation.

SQL 
HAVING SUM(spend) > 10000
Interview question
Q: Why can't I normally use an aggregate like SUM(spend) in WHERE?
Because WHERE happens before GROUP BY aggregation.

Use: 
HAVING SUM(spend) > 10000
instead.

3. Let's build our AdTech database

Open DBeaver → MySQL connection → SQL Editor.

Run this first:

SQL 
CREATE DATABASE adtech_training;
USE adtech_training;

Now create our tables.

Advertisers

SQL 
CREATE TABLE advertisers (
    advertiser_id INT PRIMARY KEY,
    advertiser_name VARCHAR(100)
);

Campaigns

SQL 
CREATE TABLE campaigns (
    campaign_id INT PRIMARY KEY,
    advertiser_id INT,
    campaign_name VARCHAR(100),
    campaign_type VARCHAR(50),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (advertiser_id)
        REFERENCES advertisers(advertiser_id)
);

Publishers

SQL 
CREATE TABLE publishers (
    publisher_id INT PRIMARY KEY,
    publisher_name VARCHAR(100)
);

Campaign performance

This is our most important table.

SQL 
CREATE TABLE campaign_performance (
    performance_id INT PRIMARY KEY AUTO_INCREMENT,
    campaign_id INT,
    publisher_id INT,
    report_date DATE,
    impressions INT,
    clicks INT,
    spend DECIMAL(12,2),
    ad_requests INT,
    wins INT,
    FOREIGN KEY (campaign_id)
        REFERENCES campaigns(campaign_id),
    FOREIGN KEY (publisher_id)
        REFERENCES publishers(publisher_id)
);

4. Insert realistic data

Advertisers

SQL 
INSERT INTO advertisers
(advertiser_id, advertiser_name)
VALUES
(1, 'Nike'),
(2, 'Vega'),
(3, 'PhonePe'),
(4, 'Adobe'),
(5, 'Amazon');

Campaigns

SQL 
INSERT INTO campaigns
(campaign_id, advertiser_id, campaign_name, campaign_type, start_date, end_date)
VALUES
(101, 1, 'Nike CTV Summer', 'CTV', '2026-08-01', '2026-09-30'),
(102, 2, 'Galaxy CTV', 'Display', '2026-08-15', '2026-09-30'),
(103, 3, 'PhonePe Video', 'Video', '2026-08-20', '2026-09-30'),
(104, 4, 'Adobe Creative Cloud', 'Display', '2026-08-10', '2026-09-30'),
(105, 5, 'Amazon Prime', 'CTV', '2026-08-05', '2026-09-30');

Publishers

SQL 
INSERT INTO publishers
(publisher_id, publisher_name)
VALUES
(201, 'StreamVault'),
(202, 'YouTube'),
(203, 'NDTV'),
(204, 'Times Internet'),
(205, 'Sony LIV');

Performance

SQL 
INSERT INTO campaign_performance
(campaign_id, publisher_id, report_date, impressions, clicks, spend, ad_requests, wins)
VALUES
(101, 201, '2026-09-01', 500000, 4500, 40000, 700000, 500000),
(101, 205, '2026-09-02', 550000, 4000, 42000, 750000, 550000),

(102, 203, '2026-09-01', 300000, 2500, 18000, 500000, 300000),
(102, 204, '2026-09-02', 250000, 500, 16000, 450000, 250000),

(103, 202, '2026-09-01', 800000, 7000, 45000, 1000000, 800000),
(103, 205, '2026-09-02', 900000, 5000, 55000, 1200000, 900000),

(104, 204, '2026-09-01', 200000, 1000, 12000, 300000, 200000),
(104, 203, '2026-09-02', 180000, 0, 11000, 250000, 180000),

(105, 201, '2026-09-01', 1000000, 15000, 70000, 1500000, 1000000),
(105, 205, '2026-09-02', 1200000, 16000, 85000, 1700000, 1200000);

Now test:

SQL 
SELECT *
FROM campaign_performance;

5. SQL Core #1 — SELECT

Start simple.

SQL 
SELECT
    campaign_id,
    impressions,
    clicks,
    spend
FROM campaign_performance;

You're saying:

"Give me these columns from this table."

6. SQL Core #2 — WHERE

Suppose I only want CTV-related campaigns.

We need the campaign table:

SQL 
SELECT *
FROM campaigns
WHERE campaign_type = 'CTV';

Or performance after September 1:

SQL 
SELECT *
FROM campaign_performance
WHERE report_date >= '2026-09-01';

Multiple conditions:

SQL 
SELECT *
FROM campaign_performance
WHERE impressions > 500000
AND clicks > 5000;

7. SQL Core #3 — GROUP BY

Now we're getting into reporting SQL.

Suppose we want total impressions by campaign.

SQL 
SELECT
    campaign_id,
    SUM(impressions) AS total_impressions
FROM campaign_performance
GROUP BY campaign_id;

Why SUM()?

Because campaign 101 has multiple rows.

We want:

Campaign 101
↓
500,000 + 550,000
↓
1,050,000 impressions

8. Q1 — Total impressions, clicks and spend by campaign

Your first interview question.

SQL 
SELECT
    campaign_id,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks,
    SUM(spend) AS total_spend
FROM campaign_performance
GROUP BY campaign_id;

This is your first campaign performance report.

9. Q2 — CTR, CPC and CPM

This is extremely important for AdTech interviews.

CTR (Click-Through Rate): CTR = Clicks / Impressions × 100

CPC (Cost Per Click): CPC = Spend / Clicks

CPM (Cost Per Mille): CPM = Spend / Impressions × 1000

In SQL:

SQL 
SELECT
    campaign_id,

    SUM(impressions) AS impressions,

    SUM(clicks) AS clicks,

    SUM(spend) AS spend,

    ROUND(
        SUM(clicks) / NULLIF(SUM(impressions), 0) * 100,
        2
    ) AS ctr_percent,

    ROUND(
        SUM(spend) / NULLIF(SUM(clicks), 0),
        2
    ) AS cpc,

    ROUND(
        SUM(spend) / NULLIF(SUM(impressions), 0) * 1000,
        2
    ) AS cpm

FROM campaign_performance

GROUP BY campaign_id;

Why NULLIF()?

Suppose:

Impressions = 100,000
Clicks = 0

Then: spend / clicks becomes division by zero.

Instead:

NULLIF(SUM(clicks), 0) turns zero into NULL.

So: ₹10,000 / NULL returns NULL rather than crashing the calculation.

This is a very useful production SQL habit.

10. Q3 — Top 5 campaigns by spend

SQL 
SELECT
    campaign_id,
    SUM(spend) AS total_spend
FROM campaign_performance
GROUP BY campaign_id
ORDER BY total_spend DESC
LIMIT 5;

This is one of the most common reporting queries.

Notice:

ORDER BY total_spend DESC means highest first.
Then LIMIT 5 means only five rows.

11. Q4 — Campaigns with CTR below 1%

This is where HAVING becomes important.

Wrong approach:

WHERE ctr < 1 — because ctr is calculated from an aggregate.

Correct:

SQL 
SELECT
    campaign_id,

    ROUND(
        SUM(clicks) / NULLIF(SUM(impressions), 0) * 100,
        2
    ) AS ctr_percent

FROM campaign_performance

GROUP BY campaign_id

HAVING ctr_percent < 1;

This is a classic interview question.

12. JOIN — The heart of real-world SQL

Our performance table contains:

campaign_id
publisher_id

But not:

campaign_name
publisher_name
advertiser_name

Those live in other tables. So we JOIN them.

SQL 
SELECT
    c.campaign_name,
    p.publisher_name,
    cp.impressions,
    cp.clicks,
    cp.spend
FROM campaign_performance cp

JOIN campaigns c
    ON cp.campaign_id = c.campaign_id

JOIN publishers p
    ON cp.publisher_id = p.publisher_id;

Think:

campaign_performance
| campaign_id ↓
campaigns
campaign_performance
| publisher_id ↓
publishers

13. JOIN types you need to know

INNER JOIN

Only matching records.

SQL 
SELECT *
FROM campaigns c
INNER JOIN advertisers a
    ON c.advertiser_id = a.advertiser_id;

Meaning: Give me campaigns that have a matching advertiser.

LEFT JOIN

Keep everything from the left table.

SQL 
SELECT *
FROM campaigns c
LEFT JOIN advertisers a
    ON c.advertiser_id = a.advertiser_id;

Even if an advertiser doesn't match, the campaign remains.

This is very important for troubleshooting.

Example: "Show me all campaigns, including campaigns that have no performance data."

SQL 
SELECT
    c.campaign_name,
    cp.impressions,
    cp.clicks
FROM campaigns c
LEFT JOIN campaign_performance cp
    ON c.campaign_id = cp.campaign_id;

14. NULL handling

Suppose a campaign has no performance.

A LEFT JOIN may produce:

campaign_name: Adobe Campaign
impressions: NULL

You can convert NULL to zero:

COALESCE(cp.impressions, 0)

Example:

SQL 
SELECT
    c.campaign_name,
    COALESCE(SUM(cp.impressions), 0) AS impressions,
    COALESCE(SUM(cp.clicks), 0) AS clicks
FROM campaigns c
LEFT JOIN campaign_performance cp
    ON c.campaign_id = cp.campaign_id
GROUP BY c.campaign_id, c.campaign_name;

COALESCE() is another function you should become very comfortable with.

15. CASE WHEN

Suppose we want to classify campaigns.

CTR >= 2% → Excellent
CTR >= 1% → Good
CTR < 1% → Poor

SQL:

SQL 
SELECT
    campaign_id,

    ROUND(
        SUM(clicks) / NULLIF(SUM(impressions), 0) * 100,
        2
    ) AS ctr,

    CASE
        WHEN SUM(clicks) / NULLIF(SUM(impressions), 0) * 100 >= 2
            THEN 'Excellent'

        WHEN SUM(clicks) / NULLIF(SUM(impressions), 0) * 100 >= 1
            THEN 'Good'

        ELSE 'Poor'
    END AS performance_category

FROM campaign_performance

GROUP BY campaign_id;

CASE WHEN is basically SQL's:

IF / ELSE IF / ELSE

16. Q5 — Publishers with highest revenue

Our current table has advertiser spend.

For a publisher revenue report, let's add revenue.

SQL 
ALTER TABLE campaign_performance
ADD COLUMN publisher_revenue DECIMAL(12,2);

Populate it:

SQL 
UPDATE campaign_performance
SET publisher_revenue =
    CASE
        WHEN publisher_id = 201 THEN spend * 0.70
        WHEN publisher_id = 202 THEN spend * 0.65
        WHEN publisher_id = 203 THEN spend * 0.60
        WHEN publisher_id = 204 THEN spend * 0.55
        WHEN publisher_id = 205 THEN spend * 0.68
    END;

Now:

SQL 
SELECT
    p.publisher_name,
    SUM(cp.publisher_revenue) AS total_revenue
FROM campaign_performance cp
JOIN publishers p
    ON cp.publisher_id = p.publisher_id
GROUP BY p.publisher_id, p.publisher_name
ORDER BY total_revenue DESC;

This gives you a publisher revenue ranking.

17. Q6 — Fill Rate by publisher

This is an important AdTech KPI.

Simplified formula:

Fill Rate = Filled Impressions / Ad Requests × 100

Our dataset uses impressions as filled impressions.

SQL 
SELECT
    p.publisher_name,

    SUM(cp.ad_requests) AS ad_requests,

    SUM(cp.impressions) AS impressions,

    ROUND(
        SUM(cp.impressions) /
        NULLIF(SUM(cp.ad_requests), 0) * 100,
        2
    ) AS fill_rate_percent

FROM campaign_performance cp

JOIN publishers p
    ON cp.publisher_id = p.publisher_id

GROUP BY p.publisher_id, p.publisher_name;

Real-world troubleshooting

Suppose:

Publisher A
Requests: 1,000,000
Impressions: 400,000
Fill Rate: 40%

A Solution Engineer shouldn't simply say: "Fill rate is low."

You investigate:

Request volume
↓
Eligible requests
↓
Auction participation
↓
Bid responses
↓
Winning bids
↓
Ad serving
↓
Impressions

That is where SQL becomes Solution Engineering, rather than just reporting.

18. Win Rate

Another important programmatic metric:

Win Rate = Wins / Bid Requests × 100

Using our data:

SQL 
SELECT
    publisher_id,

    SUM(wins) AS wins,

    SUM(ad_requests) AS requests,

    ROUND(
        SUM(wins) /
        NULLIF(SUM(ad_requests), 0) * 100,
        2
    ) AS win_rate_percent

FROM campaign_performance

GROUP BY publisher_id;

19. Q7 — Daily impressions trend

SQL 
SELECT
    report_date,
    SUM(impressions) AS total_impressions
FROM campaign_performance
WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY report_date
ORDER BY report_date;

This is how you build a daily delivery trend.

In a real job, you'd put this into:

SQL
↓
Database
↓
Power BI / Looker / Tableau
↓
Daily delivery dashboard

20. Q8 — Advertisers spending more than ₹10,000

Now we're joining three tables.

SQL 
SELECT
    a.advertiser_name,
    SUM(cp.spend) AS total_spend

FROM advertisers a

JOIN campaigns c
    ON a.advertiser_id = c.advertiser_id

JOIN campaign_performance cp
    ON c.campaign_id = cp.campaign_id

GROUP BY a.advertiser_id, a.advertiser_name

HAVING SUM(cp.spend) > 10000

ORDER BY total_spend DESC;

Notice the pattern:

Advertiser
↓
Campaign
↓
Performance

This type of multi-table reasoning is what I want you to become strong at.

21. Q9 — Campaigns with impressions but no clicks

Very common troubleshooting question.

SQL 
SELECT
    campaign_id,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks
FROM campaign_performance
GROUP BY campaign_id
HAVING SUM(impressions) > 0
AND SUM(clicks) = 0;

This identifies:

Impressions > 0
Clicks = 0

Potential causes could include:

Important Takeaway
SQL identifies the anomaly. It doesn't automatically tell you the root cause. As a Solution Engineer, you use SQL as the investigative telescope, then verify tags, creative XML, and client logs.

22. Q10 — CPC above average CPC

This is slightly harder.

First calculate campaign-level CPC. Then compare each campaign with the average campaign CPC.

A clean approach is a CTE (Common Table Expression):

SQL 
WITH campaign_cpc AS (

    SELECT
        campaign_id,

        SUM(spend) /
        NULLIF(SUM(clicks), 0) AS cpc

    FROM campaign_performance

    GROUP BY campaign_id
)

SELECT
    campaign_id,
    ROUND(cpc, 2) AS cpc

FROM campaign_cpc

WHERE cpc > (
    SELECT AVG(cpc)
    FROM campaign_cpc
)

ORDER BY cpc DESC;

This is a major step up from basic SQL.

You're now thinking in multiple stages:

Raw performance
↓
Aggregate by campaign
↓
Calculate CPC
↓
Calculate average CPC
↓
Compare campaigns

That's the kind of thinking I want you to develop.

23. Date functions you should know

For AdTech reporting, learn these:

Key MySQL Date Functions for AdTech
Purpose Syntax AdTech Use Case
Extract year YEAR(report_date) Annual pacing & YoY spend comparisons
Extract month MONTH(report_date) Monthly revenue reconciliation & billing
Extract day DAY(report_date) Day-of-month delivery trending
Day name DAYNAME(report_date) Weekend vs weekday traffic/conversion patterns
Date difference DATEDIFF(end_date, start_date) Campaign flight duration & daily pacing calculation
Last 7 days WHERE report_date >= CURRENT_DATE - INTERVAL 7 DAY Rolling weekly executive summary reports
Last 30 days WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAY Monthly inventory delivery & fill analysis

24. Your first professional report

Now combine everything.

Try this:

SQL 
SELECT
    c.campaign_name,
    a.advertiser_name,

    SUM(cp.impressions) AS impressions,

    SUM(cp.clicks) AS clicks,

    ROUND(SUM(cp.spend), 2) AS spend,

    ROUND(
        SUM(cp.clicks) /
        NULLIF(SUM(cp.impressions), 0) * 100,
        2
    ) AS ctr_percent,

    ROUND(
        SUM(cp.spend) /
        NULLIF(SUM(cp.clicks), 0),
        2
    ) AS cpc,

    ROUND(
        SUM(cp.spend) /
        NULLIF(SUM(cp.impressions), 0) * 1000,
        2
    ) AS cpm

FROM campaign_performance cp

JOIN campaigns c
    ON cp.campaign_id = c.campaign_id

JOIN advertisers a
    ON c.advertiser_id = a.advertiser_id

GROUP BY
    c.campaign_id,
    c.campaign_name,
    a.advertiser_name

ORDER BY spend DESC;

This is your Campaign Performance Report.

25. The five projects you should build

Don't just read SQL. Build these in DBeaver.

P1

Project 1 — Campaign Performance Report

Target Output Columns:

Campaign | Advertiser | Impressions | Clicks | Spend | CTR | CPC | CPM
P2

Project 2 — Publisher Revenue Report

Target Output Columns:

Publisher | Impressions | Ad Requests | Revenue | Fill Rate | Win Rate
P3

Project 3 — Daily Trend Report

Target Output Columns:

Date | Impressions | Clicks | Spend | CTR

Then eventually visualize it in Power BI / Tableau / Looker.

P4

Project 4 — Top 5 Campaigns

Target Output Columns:

Campaign | Advertiser | Spend | Impressions | CTR
Sorted by spend descending.
P5

Project 5 — Fill Rate Report

Target Output Columns:

Publisher | Requests | Impressions | Fill Rate
Then investigate publishers below, for example, 60%.

26. Your learning progression

I don't want you to memorize 100 SQL commands.

I want you to progress like this:

LEVEL 1: Core Fundamentals
SELECT • WHERE • ORDER BY • LIMIT
↓
LEVEL 2: Aggregations
GROUP BY • SUM • COUNT • AVG • MIN • MAX
↓
LEVEL 3: Conditional Group Filtering
HAVING • CASE WHEN • NULL • COALESCE • NULLIF
↓
LEVEL 4: Relational Joins
INNER JOIN • LEFT JOIN • multiple JOINs
↓
LEVEL 5: Subqueries & CTEs
WITH (CTEs) • Subqueries • Date functions
↓
LEVEL 6: Window Functions
ROW_NUMBER() • RANK() • DENSE_RANK() • LAG() • LEAD()
↓
LEVEL 7: AdTech Analytics Mastery
CTR • CPC • CPM • Fill Rate • Win Rate • Conversion Rate
↓
LEVEL 8: Solution Engineering Troubleshooting
Delivery drops • Tracking failures • Auction problems • Revenue discrepancies
↓
LEVEL 9: Production SQL & Data Engineering
Performance optimization • Indexes • EXPLAIN ANALYZE • Data modeling

Level 6–9 is where I want to take you eventually.

27. Your first exercise — don't look at the answer

Run the database we created in Section 3 & 4. Then solve these yourself in DBeaver before toggling the answers below:

E1

Exercise 1: Find total spend across all campaigns

▼

Solution Query:

SQL Solution 
SELECT
    SUM(spend) AS total_platform_spend
FROM campaign_performance;

Output: ₹394,000.00 across all 10 recorded rows.

E2

Exercise 2: Find total impressions by publisher

▼

Solution Query:

SQL Solution 
SELECT
    p.publisher_name,
    SUM(cp.impressions) AS total_impressions
FROM campaign_performance cp
JOIN publishers p
    ON cp.publisher_id = p.publisher_id
GROUP BY p.publisher_id, p.publisher_name
ORDER BY total_impressions DESC;

Key Result: Sony LIV leads with 2,650,000 impressions, followed by StreamVault with 1,500,000.

E3

Exercise 3: Find the campaign with the highest CTR

▼

Solution Query:

SQL Solution 
SELECT
    c.campaign_name,
    ROUND(
        SUM(cp.clicks) / NULLIF(SUM(cp.impressions), 0) * 100,
        2
    ) AS ctr_percent
FROM campaign_performance cp
JOIN campaigns c
    ON cp.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name
ORDER BY ctr_percent DESC
LIMIT 1;

Winner: Amazon Prime (Campaign 105) with 1.41% CTR (31,000 clicks / 2,200,000 imps).

E4

Exercise 4: Find campaigns where Impressions > 500,000 AND CTR < 1%

▼

Solution Query:

SQL Solution 
SELECT
    c.campaign_name,
    SUM(cp.impressions) AS total_impressions,
    ROUND(
        SUM(cp.clicks) / NULLIF(SUM(cp.impressions), 0) * 100,
        2
    ) AS ctr_percent
FROM campaign_performance cp
JOIN campaigns c
    ON cp.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name
HAVING SUM(cp.impressions) > 500000
   AND ctr_percent < 1.0;

Flagged Campaigns: Nike CTV Summer (0.81%), Galaxy CTV (0.55%), PhonePe Video (0.71%).

E5

Exercise 5: Find total spend by advertiser

▼

Solution Query:

SQL Solution 
SELECT
    a.advertiser_name,
    SUM(cp.spend) AS total_spend
FROM advertisers a
JOIN campaigns c
    ON a.advertiser_id = c.advertiser_id
JOIN campaign_performance cp
    ON c.campaign_id = cp.campaign_id
GROUP BY a.advertiser_id, a.advertiser_name
ORDER BY total_spend DESC;
E6

Exercise 6: Find publishers with fill rate below 70%

▼

Solution Query:

SQL Solution 
SELECT
    p.publisher_name,
    SUM(cp.ad_requests) AS requests,
    SUM(cp.impressions) AS impressions,
    ROUND(
        SUM(cp.impressions) / NULLIF(SUM(cp.ad_requests), 0) * 100,
        2
    ) AS fill_rate_percent
FROM campaign_performance cp
JOIN publishers p
    ON cp.publisher_id = p.publisher_id
GROUP BY p.publisher_id, p.publisher_name
HAVING fill_rate_percent < 70.0
ORDER BY fill_rate_percent ASC;

Underperforming Publishers: Times Internet (60.00%), NDTV (64.00%), StreamVault (68.18%).

E7

Exercise 7: Find campaigns that have more than 1 million impressions

▼

Solution Query:

SQL Solution 
SELECT
    c.campaign_name,
    SUM(cp.impressions) AS total_impressions
FROM campaign_performance cp
JOIN campaigns c
    ON cp.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name
HAVING SUM(cp.impressions) > 1000000
ORDER BY total_impressions DESC;
E8

Exercise 8: Find the average CPC across campaigns

▼

Solution Query (using CTE):

SQL Solution 
WITH campaign_cpc AS (
    SELECT
        campaign_id,
        SUM(spend) / NULLIF(SUM(clicks), 0) AS cpc
    FROM campaign_performance
    GROUP BY campaign_id
)
SELECT
    ROUND(AVG(cpc), 2) AS platform_avg_cpc
FROM campaign_cpc;

Average: ₹11.46

E9

Exercise 9 — Interview level: Top 3 campaigns by CPM

▼

Solution Query:

SQL Solution 
SELECT
    c.campaign_name,
    SUM(cp.impressions) AS impressions,
    SUM(cp.spend) AS spend,
    ROUND(
        SUM(cp.spend) / NULLIF(SUM(cp.impressions), 0) * 1000,
        2
    ) AS cpm
FROM campaign_performance cp
JOIN campaigns c
    ON cp.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name
ORDER BY cpm DESC
LIMIT 3;

Top 3 CPMs: 1. Nike CTV Summer (₹78.10 CPM), 2. Amazon Prime (₹70.45 CPM), 3. Galaxy CTV (₹61.82 CPM).

Notice that premium CTV inventory commands much higher CPMs than standard display!

E10

Exercise 10 — Solution Engineering level: Requests > 1M but Fill Rate < 70%

▼

Solution Query:

SQL Solution 
SELECT
    p.publisher_name,
    SUM(cp.ad_requests) AS ad_requests,
    SUM(cp.impressions) AS impressions,
    ROUND(
        SUM(cp.impressions) / NULLIF(SUM(cp.ad_requests), 0) * 100,
        2
    ) AS fill_rate_percent
FROM campaign_performance cp
JOIN publishers p
    ON cp.publisher_id = p.publisher_id
GROUP BY p.publisher_id, p.publisher_name
HAVING SUM(cp.ad_requests) > 1000000
   AND fill_rate_percent < 70.0;

Three Technical Reasons Why This Happens (Solution Engineer Root Cause Analysis):

  1. Excessive Floor Prices (Price Floor / UPR Misconfiguration):
    The publisher has configured hard CPM floors in Google Ad Manager (GAM 360) or their Prebid Server that are significantly higher than the fair market clearing price. DSPs submit valid bids, but the ad exchange discards them as floor-rejected (Loss Code 100 / floor price violation).
  2. Latency / Timeout (tmax) Dropped Bids:
    The publisher's video player or wrapper timeout is set too strictly (e.g. 500ms for CTV or client-side Prebid). Downstream DSPs require 600–900ms to evaluate user segments and respond. As a result, hundreds of thousands of bid responses arrive after the auction has already timed out.
  3. Identity & Consent Drop-off (No-Bid / Zero Addressability):
    If the publisher is sending ad requests with missing or invalid IAB TCF v2.2 consent strings, missing GDPR signals, or stripped device identifiers (no IDFA, GAID, or PPID), DSPs refuse to bid due to compliance mandates or inability to frequency-cap.
Your interview vocabulary

Start becoming comfortable explaining SQL in this language:

"I aggregate campaign-level performance using GROUP BY, calculate KPIs using aggregated metrics, use HAVING for post-aggregation filtering, and use LEFT JOIN when I need to preserve entities that may not have matching performance data."

That one sentence already sounds much more like someone who understands production reporting, rather than someone who has simply learned SQL syntax.

Next step

Do Exercises 1–10 in DBeaver and paste your SQL here — even if it's wrong. I'll review it like a Solution Engineering interview, point out exactly what is wrong, explain why, and then move you into CTEs → subqueries → window functions → real AdTech troubleshooting SQL.

Think

AdTech Toolkit

Enter any two values
to calculate the third

More tools coming soon