Lighthouse

Level 2 · Check and measure the channel mix in SQL

From Daniel Foster, Mobile Product Manager

“We agreed the question: each channel's share of completed orders and revenue by year, and whether the app's growth is new business or orders moving over from the website. Start by showing me the data is fit for it. December 2025 isn't fully settled yet.”

Your goal

Step 1 of 3. Check the data. For each channel, count all orders by status: return channel, completed, cancelled, refunded, pending, and pending_late (pending orders placed on or after 2025-12-22), sorted by channel. Use the orders table alone.

Read from the orders tablenot done yet
Show exactly channel completed cancelled refunded pending pending_latenot done yet
3 rows, sorted as askednot done yet

Step 1 · your query

SQL

Loading the editor…

Reference solution

ResultPreview of orders · press Run to see yours

Preview of orders
order_id	customer_id	order_date	status	channel
1001	125	2023-01-01 20:51:19	completed	web
1002	156	2023-01-02 07:58:46	completed	web
1003	22	2023-01-02 10:25:27	completed	marketplace
1004	48	2023-01-02 11:21:51	completed	marketplace

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

Step 2 · your query

SQL

Loading the editor…

Reference solution

ResultPreview of orders · press Run to see yours

Preview of orders
order_id	customer_id	order_date	status	channel
1001	125	2023-01-01 20:51:19	completed	web
1002	156	2023-01-02 07:58:46	completed	web
1003	22	2023-01-02 10:25:27	completed	marketplace
1004	48	2023-01-02 11:21:51	completed	marketplace

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

Step 3 · your query

SQL

Loading the editor…

Reference solution

ResultPreview of orders · press Run to see yours

Preview of orders
order_id	customer_id	order_date	status	channel
1001	125	2023-01-01 20:51:19	completed	web
1002	156	2023-01-02 07:58:46	completed	web
1003	22	2023-01-02 10:25:27	completed	marketplace
1004	48	2023-01-02 11:21:51	completed	marketplace

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

Tables

Harbor & Pine store data, ready to query.

orders12,000 rows · 7 columns

order_idinteger
customer_idinteger
order_datetimestamp
statusvarchar
channelvarchar
shipping_feedecimal
coupon_codevarchar

First 5 rows of orders
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_idinteger
order_idinteger
product_idinteger
quantityinteger
unit_pricedecimal
discount_pctdecimal

First 5 rows of order_items
order_item_id	order_id	product_id	quantity	unit_price
1	1001	121	9	31.99
2	1001	154	3	11.99
3	1002	154	4	11.99
4	1002	194	1	149.00
5	1002	193	2	19.99

customers2,018 rows · 9 columns

customer_idinteger
first_namevarchar
last_namevarchar
emailvarchar
cityvarchar
statevarchar
signup_datedate
segmentvarchar
referral_sourcevarchar

First 5 rows of customers
customer_id	first_name	last_name	email	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	pinterest
5	Kyle	Jimenez	kjimenez27@yahoo.com	Indianapolis	IN	2022-06-07	consumer	NULL

products120 rows · 8 columns

product_idinteger
namevarchar
categoryvarchar
subcategoryvarchar
unit_pricedecimal
unit_costdecimal
launched_ondate
is_activeboolean

First 5 rows of products
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

returns1,673 rows · 5 columns

return_idinteger
order_item_idinteger
returned_ondate
reasonvarchar
refund_amountdecimal

First 5 rows of returns
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_idinteger
first_namevarchar
last_namevarchar
departmentvarchar
titlevarchar
manager_idinteger
hire_datedate
salaryinteger

First 5 rows of employees
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

web_sessions40,000 rows · 7 columns

session_idinteger
customer_idinteger
started_attimestamp
devicevarchar
sourcevarchar
pages_viewedinteger
convertedboolean

First 5 rows of web_sessions
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_datedate
channelvarchar
amountdecimal

First 5 rows of marketing_spend
spend_date	channel	amount
2023-01-01	paid	492.99
2023-01-01	email	37.38
2023-01-01	social	200.53
2023-01-02	paid	709.13
2023-01-02	email	64.13

The lesson

What you are checking, and why

Before measuring shares, make sure the two columns the analysis rests on are clean: channel and status. One query does both, by pivoting statuses into columns with conditional counts:

COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed

COUNT skips NULLs, and a CASE without ELSE returns NULL when the condition fails, so only matching rows are counted. Three things to read in the result:

The number of rows. One per channel value. A fourth row such as App would mean casing to fix.
Pending orders. Daniel warned that December is not settled. pending_late shows how many pending orders are simply recent ones.
Cancellations by channel. If one channel cancelled far more, using completed orders would tilt the mix. Here they are all close to 6%.

Shares within each year

A share of total needs each row’s group total next to it. A window function does that without collapsing the rows:

orders * 100.0 / SUM(orders) OVER (PARTITION BY year)

PARTITION BY year restarts the total for every year, so each year’s three shares add up to 100%. Aggregate to year and channel in a CTE first, then apply the window to the result.

Mix or growth?

A rising share has two possible stories. Either the app grew while the others held steady, or the app held steady while the others fell. Usually it is some of both, and only the counts tell them apart. Step 3 puts 2024 and 2025 side by side with conditional aggregation:

COUNT(CASE WHEN year = 2024 THEN 1 END) AS o24,
COUNT(CASE WHEN year = 2025 THEN 1 END) AS o25

Reading the result

channel	orders_2024	orders_2025	orders_change_pct	aov_2025
app	1239	1904	53.7	523.27
marketplace	543	630	16.0	515.01
web	1891	1761	-6.9	557.03

The store grew in 2025 and the marketplace kept pace with it. The app grew far faster, and the website shrank. The data cannot say which customers moved; a customer-level follow-up would show how many 2024 web buyers ordered through the app in 2025.

Pitfalls

Counting COUNT(*) after joining to order_items counts lines, not orders. Use COUNT(DISTINCT o.order_id) or roll up to orders first.
A share without the counts behind it can hide a shrinking market.
shipping_fee is what the customer paid for delivery, not the fee the marketplace charges us.

Level 2 · Check and measure the channel mix in SQL

Your goal

This case unlocks when you finish any one of:

Step 1 · your query

Reference solution

Mote checks your result

Step 2 · your query

Reference solution

Mote checks your result

Step 3 · your query

Reference solution

Mote checks your result

What Daniel gets, and why

Tables

The lesson

What you are checking, and why

Shares within each year

Mix or growth?

Reading the result

Pitfalls

Lantern lit!

Level 2 · Check and measure the channel mix in SQL

The request

Your goal

This case unlocks when you finish any one of:

Step 1 · your query

Reference solution

Mote checks your result

Step 2 · your query

Reference solution

Mote checks your result

Step 3 · your query

Reference solution

Mote checks your result

What Daniel gets, and why

Tables

The lesson

What you are checking, and why

Shares within each year

Mix or growth?

Reading the result

Pitfalls

Cookie choices