SQL for Store Performance by Day | Dataford Interview Questions

Write a SQL query to analyze store performance by day at King Soopers, including sales, customer counts, and out-of-stock incidents.

Use the provided stores, sales_transactions, and out_of_stock_incidents tables. Include dates represented by either a sale or an out-of-stock incident, including dates with no sales.

Output

One row per store and activity date.
Return store_id, store_name, activity_date, total_sales, customer_count, and out_of_stock_incidents.
Use zero for missing sales or incident counts, count distinct non-null customers, and order by store_id, then activity_date.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique King Soopers store identifier
store_name	VARCHAR(100)	Store display name
city	VARCHAR(100)	Store city

sales_transactions

Column	Type	Description
transaction_idPK	INT	Unique sales transaction identifier
store_id	INT	Referenced King Soopers store
sale_date	DATE	Date of the sale
customer_id	VARCHAR(30)	Loyalty customer identifier, when available
sale_amount	NUMERIC(10,2)	Transaction sales amount

out_of_stock_incidents

Column	Type	Description
incident_idPK	INT	Unique out-of-stock incident identifier
store_id	INT	Store where the incident occurred
incident_date	DATE	Date of the out-of-stock incident
sku	VARCHAR(30)	Product SKU associated with the incident

Your question is SQL for Store Performance by Day. 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