SQL for Weekly Store Performance Ranking | Dataford Interview Questions

Write a SQL query to analyze weekly store performance by location at Asics, including sales, conversion rate, and return rate, and rank stores by improvement over the prior period.

Use the stores and weekly_store_metrics tables. Calculate improvement as percentage change in sales versus the store's previous week.

Output

One row per store and week with prior-week data available.
Columns: store_id, location, week_start, sales, conversion_rate, return_rate, prior_sales, sales_improvement_pct, and improvement_rank.
Rank separately within each week, highest sales improvement first, then location ascending for ties.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique Asics store identifier
location	VARCHAR(100)	Store location name
region	VARCHAR(50)	Operating region
store_name	VARCHAR(100)	Display name of the store

weekly_store_metrics

Column	Type	Description
metric_idPK	INT	Unique weekly metric record identifier
store_id	INT	Referenced Asics store
week_start	DATE	Start date of the reporting week
sales	NUMERIC(12,2)	Sales amount for the week or metric segment
visits	INT	Store visits recorded
transactions	INT	Completed transactions
returned_orders	INT	Orders returned during the reporting period

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

Problem