Promotional Inventory Performance SQL

MediumSQL · PostgreSQL

Problem

Write a SQL query to analyze promotional inventory performance by brand and week, including stockouts, excess inventory, and sell-through rate.

Use the supplied brand, promotion, and weekly inventory data. Include promotional weeks even when no inventory record exists, and treat unavailable sell-through denominators as NULL.

Output

One row per promotional brand and week
Columns: brand_name, week_start, promotion_name, stockout_days, excess_inventory_units, and sell_through_rate
Sell-through rate is a percentage, rounded to two decimals
Sort by week_start, then brand_name

Schema

brands

Column	Type	Description
brand_idPK	INT	Unique brand identifier
brand_name	VARCHAR(100)	Brand name

promotions

Column	Type	Description
promotion_idPK	INT	Unique promotion identifier
brand_id	INT	Brand participating in the promotion
week_start	DATE	Monday starting the promotional week
promotion_name	VARCHAR(100)	Promotion name

inventory_weekly

Column	Type	Description
inventory_idPK	INT	Unique inventory record identifier
promotion_id	INT	Related promotion
store_code	VARCHAR(20)	Store or distribution location code
beginning_inventory	INT	Units available at the beginning of the week
units_received	INT	Units received during the week
units_sold	INT	Units sold during the week
stockout_days	INT	Number of days the promotional item was out of stock
excess_inventory_units	INT	Units identified as excess inventory

Tablesbrandspromotionsinventory_weekly