SQL Window Functions Ranking

EasySQL · PostgreSQL

Problem

Career Soft Solutions Pvt. uses a product revenue report to identify leading products each month. Write a PostgreSQL query that ranks products by revenue within every category and month.

Requirements

Include only orders whose status is completed.
Calculate product revenue as quantity * unit_price, aggregated by calendar month, category, and product.
Use a window function to assign a deterministic rank within each month and category, ordering revenue descending and product_id ascending for ties.
Return only the top three products in each category for each month, including categories with fewer than three qualifying products.
Return results ordered by month, category, rank, and product ID.

Schema

categories

Column	Type	Description
category_idPK	INT	Primary key for a product category
category_name	VARCHAR(80)	Display name of the category

products

Column	Type	Description
product_idPK	INT	Primary key for a product
product_name	VARCHAR(120)	Product display name
category_id	INT	Reference to categories.category_id

orders

Column	Type	Description
order_idPK	INT	Primary key for an order
order_date	TIMESTAMP	Timestamp when the order was placed
status	VARCHAR(20)	Order processing status

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for an order line item
order_id	INT	Reference to orders.order_id
product_id	INT	Reference to products.product_id
quantity	INT	Number of units in the line item
unit_price	NUMERIC(12,2)	Price per unit at purchase time

Tablescategoriesproductsordersorder_items