Leaderboard Shake-Up

Problem

A chain ranks its cafes on a leaderboard. `coffee_shops` has columns `(shop_id, shop_name, revenue)` (`shop_id` is the primary key), and a month of corrections is staged in `revenue_adjustments` with columns `(shop_id, revenue_delta)` (`shop_id` is the primary key, one row per shop).

A shop's rank is by `revenue` descending, breaking ties by `shop_name` ascending. After adding each shop's `revenue_delta` to its `revenue`, the ranking is recomputed the same way. Report `shop_id`, `shop_name`, and `rank_shift` = old rank minus new rank (positive means the shop climbed, negative means it fell). Row order does not matter.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 4 rows with the columns shop_id, shop_name, rank_shift.

Starter code (SQL)

SELECT *
FROM coffee_shops;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium