Common Table Expressions (CTE)

🎬 Scenario: Simplifying Google Ads Performance Reports#

Your manager at Google Ads, Jordan, wants a report on high-converting campaigns, but their existing query is difficult to read and maintain.

Can you break the query into smaller logical steps so we can easily modify it later?

Your task is to use Common Table Expressions (CTEs) to simplify the analysis of Google Ads performance.

google_ads_campaigns:

1. Understanding Common Table Expressions (CTEs)#

A CTE allows you to define a temporary result set that can be referenced inside a query.

CTEs make queries:

• More readable by breaking them into logical steps.
• Reusable since the CTE can be referenced multiple times.

Syntax

1WITH cte_name AS (  
2    SELECT column1, column2  
3    FROM table_name  
4    WHERE condition  
5)  
6SELECT * FROM cte_name;

• The WITH clause defines the CTE, which is like a temporary table.
• The main query selects from the CTE instead of a complex inline subquery.

2. Using CTEs to Calculate Conversion Rates#

Instead of writing a long, nested query, we can use a CTE to first calculate campaign conversion rates and then filter the results.

Example Query: Calculating Conversion Rate Using CTE

1WITH conversion_rates AS (  
2    SELECT campaign_name,  
3           conversions * 100.0 / clicks AS conversion_rate  
4    FROM google_ads_campaigns  
5)  
6SELECT * FROM conversion_rates  
7WHERE conversion_rate > 5  
8ORDER BY conversion_rate DESC;

What’s Happening?

• The CTE conversion_rates first calculates the conversion rate for each campaign.
• The main query selects from the CTE and filters campaigns above 5% conversion.

3. Using Multiple CTEs in One Query#

CTEs can be chained together for more complex analysis.

For example, we can:

1. Calculate total ad spend per user (user_spend).
2. Find users who spent more than the average (high_spenders).

Example Query: Finding Top Ad Spenders Using Multiple CTEs

1WITH user_spend AS (  
2    SELECT user_id, company, SUM(ad_spend_total) AS total_spent  
3    FROM google_ads_users  
4    GROUP BY user_id, company  
5),  
6high_spenders AS (  
7    SELECT user_id, company, total_spent  
8    FROM user_spend  
9    WHERE total_spent > (SELECT AVG(total_spent) FROM user_spend)  
10)  
11SELECT * FROM high_spenders  
12ORDER BY total_spent DESC;

What’s Happening?

• The first CTE (user_spend) calculates total ad spend per user.
• The second CTE (high_spenders) filters out users below average spend.
• The main query selects from high_spenders, displaying only top spenders.

✍️ SQL Exercises#

Exercise 1: Finding High-Converting Campaigns#

Jordan needs a list of campaigns with conversion rates above 5% using a CTE.

Filter condition:

• Retrieve campaign name and conversion rate.
• Only include campaigns with conversion rates above 5%.
• Sort by conversion_rate in descending order.

Write an SQL query to return the requested data.

Exercise 2: Identifying High Ad Spenders Using CTEs#

Jordan also wants to find the advertisers who spent more than the average using CTEs.

Filter condition:

• Retrieve user ID, company, and total spend.
• Only include advertisers who spent more than the average.

Write an SQL query to return the requested data.