SQL Revenue Filtering Query | Dataford Interview Questions

A retail analytics team needs a calendar-year revenue report by product category. Write a PostgreSQL query that calculates category revenue and the number of distinct orders contributing to that revenue for the anchored calendar year 2026. Include only categories whose total revenue exceeds 5,000.

Output

One row per qualifying product category, with columns category_name, total_revenue, and order_count
Revenue is the sum of quantity * price_per_unit; rows with a NULL quantity or price are ignored by the aggregate
Orders with NULL dates are excluded, as are orders outside calendar year 2026
Include only categories with total revenue greater than 5,000
Sort by total_revenue descending, then category_name ascending

Schema

categories

Column	Type	Description
category_idPK	INT	Primary key for a product category
category_name	VARCHAR(100)	Product category name

products

Column	Type	Description
product_idPK	INT	Primary key for a product
product_name	VARCHAR(150)	Product name
category_id	INT	Category containing the product

orders

Column	Type	Description
order_idPK	INT	Primary key for an order
order_date	DATE	Date on which the order was placed

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for an order line
order_id	INT	Order containing the line item
product_id	INT	Product sold on the line
quantity	INT	Number of units sold
price_per_unit	NUMERIC(12,2)	Unit selling price

Your question is SQL Revenue Filtering Query. Start with the requirements and the four 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