Week 39 - SQL Chips and Chunks Newletter

1. SQL Chip

When auditing high-speed ad campaigns, the HAVING clause is your best friend for finding overspenders. While WHERE filters individual rows of data (like a single click), HAVING filters the results after they have been grouped. This is essential for identifying which specific Campaign IDs have exceeded their daily budget across thousands of micro-transactions.

2. SQL Chunk

This query identifies campaigns that have already spent more than $500 today, helping you flag potential overspending before the next billing cycle.

SELECT 
    campaign_id, 
    SUM(spend_amount) AS total_daily_spend
FROM 
    campaign_delivery_logs
WHERE 
    delivery_date = CURRENT_DATE
GROUP BY 
    campaign_id
HAVING 
    SUM(spend_amount) > 500
ORDER BY 
    total_daily_spend DESC;

3. SQL Challenge

Now, let’s look at "Pacing." It’s not just about total spend; it’s about spend relative to a cap. We can use a JOIN to compare our live delivery logs against our contract metadata to find campaigns that are delivering even though they are marked as 'Paused' in the system.

SELECT 
    m.campaign_name,
    m.status AS intended_status,
    SUM(d.spend_amount) AS leakage_spend
FROM 
    campaign_metadata m
JOIN 
    campaign_delivery_logs d ON m.campaign_id = d.campaign_id
WHERE 
    m.status = 'Paused' 
    AND d.delivery_date = CURRENT_DATE
GROUP BY 
    m.campaign_name, 
    m.status
HAVING 
    SUM(d.spend_amount) > 0
ORDER BY 
    leakage_spend DESC;

4. SQL Mistake

The "Unaggregated Column" Error. A common mistake when using GROUP BY is trying to SELECT a column that isn't in the GROUP BY clause and isn't wrapped in an aggregate function (like SUM or AVG).

The Mistake: SELECT campaign_id, timestamp, SUM(spend) FROM logs GROUP BY campaign_id
The Fix: You cannot select timestamp here because the database doesn't know which timestamp to show for the group. You must either add it to the GROUP BY or use MAX(timestamp) to see the most recent delivery.

5. SQL in Practice

Scenario: E-commerce Geofence Audit A retail client is running a "Labor Day Sale" targeting specific zip codes. You need to verify if the ads are actually serving in the correct locations or if there is "geo-drift" (ads showing in the wrong place).

SELECT 
    geo_zip_code,
    count(impression_id) AS impression_count,
    SUM(spend_amount) AS total_cost
FROM 
    ad_impressions
WHERE 
    campaign_tag = 'LaborDay_2025_Retail'
    AND geo_country = 'USA'
GROUP BY 
    geo_zip_code
ORDER BY 
    impression_count DESC
LIMIT 20;

6. SQL Resource

This week we have a dedicated resource page specifically for this edition: More SQL Chips for Snowflake & MySQL. This guide acts as a technical deep dive to supplement this week’s newsletter, covering ANSI compliance, strategic aliasing, and advanced workarounds for the unaggregated column error. It’s the essential "extra credit" for anyone mastering AdTech data across Snowflake and MySQL environments.

Select Star SQL This is an interactive book that lets you learn SQL by running queries against a real dataset (Texas death row executions). It is excellent for AdTech professionals because it focuses on data storytelling and finding patterns within a messy, real-world dataset, rather than just teaching syntax in a vacuum.

P.S. Looking for a gift for a fellow data analyst? Our new "Data Rules" tee is perfect for the programmer who knows clean SQL is the only way to operate