The request
From Jordan Reyes, Head of Marketing
“Budget planning is next week. I need three tables: how each source converts on each device this year, what each paid channel has cost per conversion over the last three years, and where each source loses its visitors. Make sure spend isn't double counted this time.”
Your goal
Step 1 of 3. For 2025, return one row per source and device with: source, device, sessions, conversions and conversion_rate_pct (conversions * 100 / sessions, rounded to 2). Sort by source, then device, ascending.
- Read from the
web_sessionstablenot done yet - Show exactly
sourcedevicesessionsconversionsconversion_rate_pctnot done yet - 15 rows, sorted as askednot done yet
Step 2 of 3. For each year 2023–2025 and each channel in marketing_spend (email, paid, social), return: year, channel, spend (total amount, rounded to 2), conversions (converted sessions whose source equals the channel, same year of started_at) and cost_per_conversion (spend / conversions, rounded to 2). Sort by year, then channel.
- Read from the
web_sessionstablenot done yet - Read from the
marketing_spendtablenot done yet - Show exactly
yearchannelspendconversionscost_per_conversionnot done yet
Step 3 of 3. For 2025, return one row per source with: source, sessions, engaged (sessions with pages_viewed >= 3), conversions, engaged_pct (engaged * 100 / sessions) and engaged_conv_pct (conversions * 100 / engaged), both rounded to 2. Sort by engaged_conv_pct descending.
- Read from the
web_sessionstablenot done yet - Show exactly
sourcesessionsengagedconversionsengaged_pctengaged_conv_pctnot done yet - 5 rows, sorted as askednot done yet
Step 1 · your query
Loading the editor…
Reference solution
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic |
Mote checks your result
Press Run. Each row that matches the goal becomes a firefly in your lantern.
Step 2 · your query
Loading the editor…
Reference solution
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic |
Mote checks your result
Press Run. Each row that matches the goal becomes a firefly in your lantern.
Step 3 · your query
Loading the editor…
Reference solution
| session_id | customer_id | started_at | device | source |
|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic |
Mote checks your result
Press Run. Each row that matches the goal becomes a firefly in your lantern.
What Jordan gets, and why
Tables
Harbor & Pine store data, ready to query.
web_sessions40,000 rows · 7 columns
session_idintegercustomer_idintegerstarted_attimestampdevicevarcharsourcevarcharpages_viewedintegerconvertedboolean
| session_id | customer_id | started_at | device | source | pages_viewed | converted |
|---|---|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid | 1 | false |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid | 1 | false |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct | 7 | false |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic | 3 | false |
| 5 | NULL | 2023-01-01 13:06:21 | mobile | social | 2 | false |
marketing_spend3,276 rows · 3 columns
spend_datedatechannelvarcharamountdecimal
| spend_date | channel | amount |
|---|---|---|
| 2023-01-01 | paid | 492.99 |
| 2023-01-01 | 37.38 | |
| 2023-01-01 | social | 200.53 |
| 2023-01-02 | paid | 709.13 |
| 2023-01-02 | 64.13 |
customers2,018 rows · 9 columns
customer_idintegerfirst_namevarcharlast_namevarcharemailvarcharcityvarcharstatevarcharsignup_datedatesegmentvarcharreferral_sourcevarchar
| customer_id | first_name | last_name | city | state | signup_date | segment | referral_source | |
|---|---|---|---|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco | CA | 2022-06-02 | consumer | search |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta | GA | 2022-06-02 | consumer | NULL |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles | CA | 2022-06-04 | business | NULL |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle | WA | 2022-06-06 | business | |
| 5 | Kyle | Jimenez | kjimenez27@yahoo.com | Indianapolis | IN | 2022-06-07 | consumer | NULL |
products120 rows · 8 columns
product_idintegernamevarcharcategoryvarcharsubcategoryvarcharunit_pricedecimalunit_costdecimallaunched_ondateis_activeboolean
| product_id | name | category | subcategory | unit_price | unit_cost | launched_on | is_active |
|---|---|---|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 | NULL | 2023-09-11 | true |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 | 503.50 | 2024-05-16 | true |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 | 429.87 | 2023-05-27 | true |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 | 214.70 | 2022-12-20 | true |
| 105 | Avery Leather Bed Frame | Furniture | Beds | 449.00 | 232.70 | 2021-08-31 | true |
orders12,000 rows · 7 columns
order_idintegercustomer_idintegerorder_datetimestampstatusvarcharchannelvarcharshipping_feedecimalcoupon_codevarchar
| order_id | customer_id | order_date | status | channel | shipping_fee | coupon_code |
|---|---|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web | 0.00 | NULL |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web | 0.00 | NULL |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace | 4.99 | WELCOME10 |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace | 4.99 | WELCOME10 |
| 1005 | 82 | 2023-01-02 11:43:36 | completed | web | 0.00 | WELCOME10 |
order_items30,338 rows · 6 columns
order_item_idintegerorder_idintegerproduct_idintegerquantityintegerunit_pricedecimaldiscount_pctdecimal
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1001 | 121 | 9 | 31.99 | 0.00 |
| 2 | 1001 | 154 | 3 | 11.99 | 0.00 |
| 3 | 1002 | 154 | 4 | 11.99 | 0.00 |
| 4 | 1002 | 194 | 1 | 149.00 | 0.00 |
| 5 | 1002 | 193 | 2 | 19.99 | 0.00 |
returns1,673 rows · 5 columns
return_idintegerorder_item_idintegerreturned_ondatereasonvarcharrefund_amountdecimal
| return_id | order_item_id | returned_on | reason | refund_amount |
|---|---|---|---|---|
| 1 | 14 | 2023-01-10 | damaged | 83.69 |
| 2 | 42 | 2023-01-10 | wrong_item | 19.99 |
| 3 | 105 | 2023-01-14 | wrong_item | 10.99 |
| 4 | 24 | 2023-01-16 | changed_mind | 26.99 |
| 5 | 126 | 2023-01-16 | late | 20.99 |
employees60 rows · 8 columns
employee_idintegerfirst_namevarcharlast_namevarchardepartmentvarchartitlevarcharmanager_idintegerhire_datedatesalaryinteger
| employee_id | first_name | last_name | department | title | manager_id | hire_date | salary |
|---|---|---|---|---|---|---|---|
| 1 | Catherine | Hale | Executive | Chief Executive Officer | NULL | 2016-03-01 | 285000 |
| 2 | Robert | Kim | Executive | Chief Operating Officer | 1 | 2017-01-09 | 235000 |
| 3 | Megan | Collins | Sales | Head of Sales | 1 | 2018-04-16 | 172000 |
| 4 | Chloe | Gutierrez | Sales | Account Executive | 3 | 2020-01-21 | 72000 |
| 5 | Frank | Bell | Sales | Account Executive | 3 | 2023-05-21 | 73000 |
The lesson
What the report needs
Marketing’s budget pack has three tables, and each one uses a different technique from this chapter:
- Conversion by source and device (2025): one GROUP BY on two columns.
- Cost per conversion by year and paid channel: two tables, each aggregated on its own, then joined.
- Funnel by source (2025): two stages per source with step rates.
Pre-aggregate before you join
marketing_spend has one row per day and channel. web_sessions has one row per visit. Join them on
the channel and year and every spend row repeats once for every matching session: 2025 paid spend turns from $340,778 into $1.2 billion
out of nowhere.
The fix is to bring each table to the grain of the answer first, one row per year and channel, in its own CTE:
WITH spend AS (SELECT year(spend_date) AS year, channel, SUM(amount) AS spend FROM marketing_spend GROUP BY 1, 2),
conv AS (SELECT year(started_at) AS year, source, COUNT(*) FILTER (WHERE converted) AS conversions FROM web_sessions GROUP BY 1, 2)Now the join is one-to-one and nothing is counted twice. Organic and direct have no spend, so an
inner join from spend drops them, which is what a cost table should do.
Step rates by source
For the funnel table, count engaged sessions and conversions per source in one pass, and divide each
stage by the stage before it. engaged_pct shows how well a source’s visitors get past the landing
page; engaged_conv_pct shows whether those who browse go on to buy. Sorting by the second puts the
sources whose engaged visitors buy at the top.
Caveats to put in the pack
- Conversions are last-touch converted sessions, not orders. App and marketplace orders are not in the session data.
- Only known customers can convert here, so sources with many anonymous visitors look weaker.
- Twelve day/channel rows are missing from
marketing_spendbecause of tracking outages, so a few yearly totals are slightly low.
Common mistakes
- Summing spend after a join to sessions.
- Dividing conversions by all sessions in the step rate, which repeats the overall rate.
- Filtering 2025 with
year() = 2025on one table and a date range on the other and getting different windows. Use the same rule on both sides.
Lantern lit!
Practised: funnelconversion ratecost per conversionpre aggregatectedrop off
- Solved
- No hints
- First run