Practice · medium · dataset: ecom

Customer category coverage

For every customer and every product category in the catalog, report the customer’s revenue in that category, using 0 when the customer has no orders in it. Revenue is quantity times the line’s unit_price.

Return customer_id, category, and revenue rounded to two decimals. Sort by customer_id ascending, then by category ascending.

Tables available

Table Columns
customers customer_id, email, country, signup_date
orders order_id, customer_id, order_date, status, channel
order_items order_id, product_id, quantity, unit_price
products product_id, name, category, unit_price

The result is a matrix: every customer paired with every category, including pairs with no sales.

⌘/Ctrl + Enter to grade

Write a query, then grade it.

Hints

Try it first. Reveal a hint only when you are stuck.