SQL Weekly Sales Trend Flags | Dataford Interview Questions

Write a SQL query to analyze weekly sales by store, compare each week to the prior week, and flag stores with declining performance.

Use completed sales only. Compare each store's reported week with its previous reported week.

Output

One row per store and reported week
Columns: store_id, store_name, week_start, weekly_sales, prior_week_sales, is_declining
Include the first reported week with a false decline flag
Order by store_id, then week_start

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store display name
region	VARCHAR(50)	Geographic operating region

sales_transactions

Column	Type	Description
transaction_idPK	INT	Unique sales transaction identifier
store_id	INT	Store associated with the transaction
sale_date	DATE	Date the sale was recorded
sale_amount	NUMERIC(10,2)	Sale amount in dollars
status	VARCHAR(20)	Transaction processing status

Your question is SQL Weekly Sales Trend Flags. Start with the requirements and the two 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.