SQL: Top Categories by GMV Concentration | Dataford Interview Questions

Your question is SQL: Top Categories by GMV Concentration. Start with the requirements and the two tables on the right.

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

Problem

Business Context

Movate's analytics team wants to identify product categories whose annual GMV is highly concentrated in a short seasonal period.

Task

Write a PostgreSQL query for calendar year 2024 that returns the top five product categories where at least 60% of annual GMV occurs in any two consecutive calendar months.

Requirements

Include all twelve months for every category, treating months without completed orders as zero GMV.
Exclude orders outside 2024 and orders whose order_status is not completed.
Calculate annual GMV and every two-month consecutive GMV total using window functions.
Keep categories where the largest two-month total is at least 60% of annual GMV.
Return the top five qualifying categories ranked by annual GMV descending, including the first month of the peak pair and its concentration percentage.

Schema

product_categories

Column	Type	Description
category_idPK	INT	Unique product category identifier
category_name	VARCHAR(100)	Product category name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
category_id	INT	Product category associated with the order
order_date	DATE	Date on which the order was placed
gmv	NUMERIC(12,2)	Gross merchandise value for the order
order_status	VARCHAR(20)	Order processing status