SQL for Sales Down, Inventory High | Dataford Interview Questions

Write a SQL query to combine daily sales, inventory, and staffing tables to identify Victoria's Secret stores where sales are down but inventory is high.

Use the latest date represented in the daily sales data. Sales are down when total sales are lower than the store's previous day, and inventory is high when it is at least the average inventory across stores on that date.

Output

One row per qualifying store with store_id, current sales, previous-day sales, decline percentage, inventory units, average inventory units, and staff count
Include only stores meeting both conditions, ordered by greatest decline percentage, then store_id

Schema

daily_sales

Column	Type	Description
sales_idPK	INT	Unique sales record identifier
store_id	INT	Victoria's Secret store identifier
sale_date	DATE	Date of the sales record
net_sales	DECIMAL(12,2)	Net sales amount for the record

daily_inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory snapshot identifier
store_id	INT	Victoria's Secret store identifier
inventory_date	DATE	Date of the inventory snapshot
inventory_units	INT	Units available in store inventory

daily_staffing

Column	Type	Description
staffing_idPK	INT	Unique staffing record identifier
store_id	INT	Victoria's Secret store identifier
staff_date	DATE	Date of the staffing record
staff_count	INT	Number of scheduled staff

Your question is SQL for Sales Down, Inventory High. 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