Lesson 13.3

SQL JOIN Practice: Attributing Sessions to Campaigns

Turn campaign ids into campaign names, and watch which campaigns quietly fall out of an inner join.

Each paid, social, or email session carries a campaign_id, but a report needs the campaign's name. That lives in the campaigns table, reached by joining on the key.

Grouping the joined rows by campaign_name answers a common marketing reporting question: which campaigns are associated with recorded sessions? This is attribution based on the stored campaign id, not proof that the campaign caused the visit.

An INNER JOIN only keeps matches

The campaigns table lists a Retargeting campaign, but no session references it. Because an INNER JOIN keeps only rows that match on both sides, that campaign never appears in the result. Sessions with a NULL campaign_id (organic and direct traffic) drop out too. That is exactly what you want when the question is about campaigns.

Pattern

select dim.label, dim.attribute, count(*) as events
from fact_table as fact
join dim_table as dim on fact.dim_id = dim.dim_id
group by dim.label, dim.attribute
order by events desc, dim.label

Schema · Website Analytics Tables · campaigns, sessions 2 tables

Table · campaigns

Marketing campaigns with their channel and UTM tags.

5 columns · 5 rows

campaign_id int campaign_name text channel text utm_source text utm_medium text

Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows

session_id int user_id int session_date date channel text campaign_id int device text duration_seconds int

Your task

For each campaign that drove traffic, return the campaign_name, its channel, and the number of sessions (sessions). Sort by sessions descending, then campaign name.

Website Analytics · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: campaign_name, channel, sessions.
Rows: 4 campaigns that actually drove sessions.
Social Promo leads with 8 sessions; the Retargeting campaign drops out with zero.

Previous · 13.2 Longest sessions

Next · 13.4 Anonymous sessions →

Reference

SQL JOIN Types

INNER, LEFT, RIGHT, FULL, and CROSS joins.

Cheatsheet

SQL Joins Cheatsheet

Join types and common join patterns.

Interview

SQL JOIN Interview Questions

Practice join keys, left joins, duplicate rows, and debugging.

Mistake