Subquery

🎬 Scenario: Identifying High-Converting Campaigns

Your manager at Google Ads, Jordan, wants to find the highest-converting campaigns and analyze user spending.

Can you retrieve campaigns with a conversion rate higher than the average and also list the top spenders?

Your task is to use subqueries to analyze high-performing campaigns and identify top advertisers.

google_ads_campaigns:

1. Understanding Subqueries

A subquery is a query inside another query. It is used to:

• Retrieve intermediate results that another query can use.
• Filter data dynamically based on another dataset.
• Break down complex calculations into smaller, manageable parts.

2. Using Subqueries in SELECT

We can use subqueries in SELECT to compute derived values.

For example, if we want to calculate conversion rates for each campaign, we can use a subquery to divide conversions by clicks.

Example Query: Calculating Conversion Rate

1SELECT campaign_name,  
2       (SELECT COUNT(*) FROM google_ads_clicks cl WHERE cl.campaign_id = c.campaign_id AND cl.converted = TRUE) * 100.0 /  
3       (SELECT COUNT(*) FROM google_ads_clicks cl WHERE cl.campaign_id = c.campaign_id) AS conversion_rate  
4FROM google_ads_campaigns c;

What’s Happening?

• The first subquery counts successful conversions for each campaign.
• The second subquery counts all clicks for each campaign.
• The main query divides the two counts to calculate the conversion rate.

3. Using Subqueries in WHERE

We can use a subquery in WHERE to filter results dynamically.

For example, to find campaigns with a conversion rate higher than the average, we first calculate the average conversion rate in a subquery.

Example Query: Finding High-Converting Campaigns

1SELECT campaign_name, conversions * 100.0 / clicks AS conversion_rate  
2FROM google_ads_campaigns  
3WHERE (conversions * 100.0 / clicks) >  
4      (SELECT AVG(conversions * 100.0 / clicks) FROM google_ads_campaigns);

What’s Happening?

• The subquery calculates the average conversion rate.
• The main query filters campaigns with a conversion rate above that average.

4. Using Subqueries in FROM

We can use a subquery as a temporary table by placing it inside FROM.

For example, we can first group campaigns by ad type and then select the highest-spending ad type.

Example Query: Highest-Spending Ad Type

1SELECT ad_type, total_budget  
2FROM (  
3    SELECT ad_type, SUM(budget) AS total_budget  
4    FROM google_ads_campaigns  
5    GROUP BY ad_type  
6) AS ad_summary  
7ORDER BY total_budget DESC  
8LIMIT 1;

What’s Happening?

• The inner query (ad_summary) calculates total spend per ad type.
• The outer query retrieves only the highest-spending ad type.

✍️ SQL Exercises

Exercise 1: Finding High-Converting Campaigns

Jordan wants a list of campaigns with conversion rates above average, round to the 2 decimal places.

Filter condition:

• Retrieve campaign name and conversion rate, rounded to 2 decimal places.
• Only include campaigns with a conversion rate above the average.
• Sort by conversion_rate in descending order.

Write an SQL query to return the requested data.

Exercise 2: Identifying the Highest-Spending Ad Type

Jordan also wants to know which ad type received the highest total budget.

Filter condition:

• Retrieve ad type and total budget.
• Display only the highest-spending ad type.

Write an SQL query to return the requested data.