INNER JOIN

🎬 Scenario: Understanding Google Ads Performance

Your new manager at Google Ads, Jordan, is analyzing advertising performance and wants to connect user accounts with their ad campaigns.

I need a report that shows each company’s total ad budget alongside their account details. Can you join the relevant tables for me?

Your task is to join the google_ads_users and google_ads_campaigns tables to create a comprehensive report on ad spending.

Table: google_ads_campaigns

Table: google_ads_users

1. Understanding INNER JOIN

An INNER JOIN combines rows from two tables when there is a matching value in both tables.

Syntax

1SELECT table1.column1, table2.column2  
2FROM table1  
3INNER JOIN table2  
4ON table1.common_key = table2.common_key;

• INNER JOIN only returns rows where both tables have matching values on the common key.
• If there is no match, the row is excluded from the result.

2. Joining Google Ads Users with Campaign Data

To analyze company spending, we need to join:

• google_ads_users (user account details)
• google_ads_campaigns (advertising campaign details)

The common key is user_id, which links each campaign to a user.

Example Query: Merging User and Campaign Data

1SELECT u.user_id, u.name, u.company, u.country, c.campaign_name, c.budget  
2FROM google_ads_users u  
3INNER JOIN google_ads_campaigns c  
4ON u.user_id = c.user_id;

What’s Happening?

• INNER JOIN connects users and their campaigns by user_id.
• If a user has multiple campaigns, their details repeat in the result.
• Users without campaigns are excluded from the result.

3. Joining Campaigns with Click Data

To analyze campaign click performance, we need to join:

• google_ads_campaigns (campaign details)
• google_ads_clicks (click data)

The common key is campaign_id, which links clicks to campaigns.

Example Query: Joining Campaigns with Clicks

1SELECT c.campaign_id, c.campaign_name, cl.click_id, cl.device_type, cl.converted  
2FROM google_ads_campaigns c  
3INNER JOIN google_ads_clicks cl  
4ON c.campaign_id = cl.campaign_id;

What’s Happening?

• INNER JOIN links each click to the campaign that generated it.
• If a campaign has no recorded clicks, it is excluded from the results.

✍️ SQL Exercises

Exercise 1: Merging User and Campaign Data

Jordan needs a report showing each company’s total ad budget and country.

Filter condition:

• Retrieve company name, country, and total budget for each user.
• Sort by budget in descending order.

Write an SQL query to return the requested data.

Exercise 2: Analyzing Campaign Click Data

Jordan wants to analyze clicks per campaign.

Filter condition:

• Retrieve campaign name, click ID, and device type.
• Sort by campaign_id in ascending order.

Write an SQL query to return the requested data.