Top Earning Dish per Diner

Problem

A restaurant tracks every order line and the menu price of each dish. Table `orders` has `(order_id, dish_id, diner_id, servings)`, and table `menu` has `(dish_id, unit_price)`.

For each diner, report the dish on which they spent the most money in total (sum over all their order lines of servings multiplied by unit_price). If a diner's maximum spend is tied across several dishes, report every one of those dishes.

Return the columns `diner_id` and `dish_id`.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 4 rows with the columns diner_id, dish_id.

Starter code (SQL)

SELECT *
FROM orders;

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