HardSQL challenge #193CTEMulti-JOINCOALESCELEFT JOINAggregation

Build a Product Performance Report with Two CTEs

For the top 20 active products by revenue, show units sold, revenue, average review, review count and margin.

The challenge runs on the SQLabHub practice database (SQLite) and uses the products, order_items and reviews tables. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

For each active product: units sold, revenue, average review rating, review count and profit margin %. Aggregate sales and reviews separately before joining them to products.

A correct answer returns 20 rows with 8 columns: name, category, price, units_sold, revenue, avg_review, review_count, margin_pct.

Tables used in this challenge

`products` 120 rows

id INTEGER (PK) · name TEXT · category TEXT · price REAL · cost REAL · stock INTEGER · sku TEXT · status TEXT · rating REAL · review_count INTEGER

First 3 rows of the products table
id	name	category	price	cost	stock	sku	status	rating	review_count
1	Play Kitchen Deluxe	Toys	1786.38	589.51	257	SKU-000001	active	4	57
2	Micro-Cut Paper Shredder 8-Sheet	Office Supplies	907.93	281.46	131	SKU-000002	active	2.5	447
3	4K Dual Dash Camera	Automotive	568.89	204.8	176	SKU-000003	active	4.1	29

`order_items` 496 rows

id INTEGER (PK) · order_id INTEGER · product_id INTEGER · quantity INTEGER · unit_price REAL · subtotal REAL

First 3 rows of the order_items table
id	order_id	product_id	quantity	unit_price	subtotal
1	1	76	5	192.21	961.05
2	1	82	3	107.1	321.3
3	1	65	1	5.39	5.39

`reviews` 180 rows

id INTEGER (PK) · product_id INTEGER · user_id INTEGER · rating INTEGER · comment TEXT · reviewed_at TEXT · helpful_votes INTEGER

First 3 rows of the reviews table
id	product_id	user_id	rating	comment	reviewed_at	helpful_votes
1	23	45	5	Works perfectly. Very happy with this purchase.	2022-09-02	40
2	101	68	5	Excellent build quality. Will buy again!	2023-01-05	96
3	40	39	1	Would give 6 stars if I could!	2023-05-15	17

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
name	category	price	units_sold	revenue	avg_review	review_count	margin_pct
Merino Wool Sweater	Clothing	1473.96	30	8995.83	1.5	2	51
LEGO Creator Set 742pcs	Toys	397	37	8530.61	NULL	0	67
Leather Steering Wheel Cover	Automotive	1727.7	25	7519.96	NULL	0	47
Ergonomic Desk Mat XL	Office Supplies	1233.19	33	7066.04	2.5	2	69
Ergonomic Desk Mat XL	Office Supplies	1839.35	24	6944.33	NULL	0	61

First 5 of 20 rows. This is the output of the model solution on the practice database.

What you practise

Filtering rows with a WHERE condition
Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
Grouping rows with GROUP BY to get one result row per group
Keeping every row of the left table with LEFT JOIN, matched or not
Naming an intermediate result with a WITH clause and reading from it
Replacing NULL with a default using COALESCE
Sorting the result with ORDER BY
Returning only the first rows with LIMIT

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

Summarise order_items and reviews in two CTEs, then LEFT JOIN both to products. Joining the raw tables together would multiply the sales by the number of reviews.

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

Define a common table expression named sales:
1. Read from the order_items table.
2. Group the rows by product_id, so the result has one row for each value.
3. For each group, compute SUM(quantity) and SUM(subtotal).
Define a common table expression named ratings:
1. Read from the reviews table.
2. Group the rows by product_id, so the result has one row for each value.
3. For each group, compute AVG(rating) and COUNT(*).
Read from the products table.
LEFT JOIN sales on sales.product_id = products.id. Rows from the left side that have no match are kept, with NULL in the joined columns.
LEFT JOIN ratings on ratings.product_id = products.id. Rows from the left side that have no match are kept, with NULL in the joined columns.
Filter in WHERE: keep the rows where products.status equals 'active'.
ROUND() trims the calculated figures to a fixed number of decimals.
COALESCE(x, 0) replaces a NULL result with 0.
Sort the result by revenue descending.
Keep only the first 20 rows with LIMIT.

Model solution

Checking your session…

What to watch for

One CTE summarises sales and another summarises reviews, each to one row per product, and only then are they joined to products. Joining order_items and reviews directly would repeat every order line once per review and overstate units and revenue. The margin uses 100.0 so the division is not truncated.

CTE (WITH). A CTE exists only for the single statement it is attached to. It is not a temporary table and cannot be reused by the next query.
LEFT JOIN. A WHERE condition on the right-hand table removes the unmatched rows and turns the LEFT JOIN back into an inner join. Conditions on that table go in the ON clause.
GROUP BY. Every selected column must either be in GROUP BY or sit inside an aggregate. SQLite accepts other columns and returns an arbitrary value for them; most databases raise an error.