Revenue by Product Category | Dataford Interview Questions

Chewy's merchandising team needs a category-level view of revenue from the most recently completed quarter. Assume the reporting date is 2025-04-15, so the last quarter is Q4 2024, from 2024-10-01 through 2024-12-31.

Write a PostgreSQL query that calculates revenue by product category.

Requirements

Include only orders with status = 'completed' placed during Q4 2024.
Calculate revenue as quantity * unit_price from order items.
Group results by product category, labeling a missing category as Uncategorized.
Sort categories by total revenue descending, then category name ascending to break ties.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Current order status

order_items

Column	Type	Description
order_item_idPK	INT	Unique order-line identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit at purchase time

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Product name
category	VARCHAR(80)	Product merchandising category

Your question is Revenue by Product Category. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem