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.