Top Reviewer and Best-Rated Dish of February — SQL Medium Problem

Problem statement

A food-delivery app shows two names on its monthly highlights card: the diner who has written the most reviews (counting every review ever written), and the dish with the highest average stars among reviews written in February 2025 (from 2025-02-01 to 2025-02-28).

Return both names in a single column named result. Break a tie for most reviews by taking the diner name that comes first alphabetically, and a tie for best average the same way, by dish name. A diner with no reviews never counts; if there are no reviews at all, the diner row is missing, and if no review falls in February 2025, the dish row is missing. The rows may come in any order.

Tables

Table: Diner

Primary key: diner_id.

One row per diner; names are unique.

Table: Dish

Primary key: dish_id.

One row per dish; names are unique.

Table: DishReview

Primary key: dish_id, diner_id.

One row per review: a diner reviews a dish at most once, giving 1 to 5 stars.

Examples

Example 1

Diner

Dish

DishReview

Output

How to solve Top Reviewer and Best-Rated Dish of February

The card asks two unrelated questions, so the query is two small queries glued together with UNION ALL — both return one text column, which the outer result names result.

Top reviewer. Join reviews to diners, group by diner, and sort by COUNT(*) descending and then by name; the first row is the answer. Joining from DishReview means diners with no reviews never appear, and an empty review table gives no row at all.

Best dish of February. Filter the reviews to 2025-02-01 … 2025-02-28 before grouping, then sort the dishes by AVG(stars) descending and by name. The filter matters: a dish rated highly only in January or March must not win.

Each half needs ORDER BY … LIMIT 1, and in standard SQL that clause applies to the whole compound query, so each half goes inside a derived table. UNION ALL keeps both rows even if the two names were equal; UNION would merge them.

Without LIMIT, compute the counts and February averages in CTEs, keep the rows equal to the maximum, and take MIN(name) — the alphabetical tie-break. HAVING COUNT(*) > 0 stops an empty half from returning a NULL row. ROW_NUMBER() with the same two-key order is a third way. Each version groups the review table twice.

Reference solution (MySQL)

SELECT result FROM (
  SELECT d.diner_name AS result
  FROM DishReview r
  JOIN Diner d ON d.diner_id = r.diner_id
  GROUP BY d.diner_id, d.diner_name
  ORDER BY COUNT(*) DESC, d.diner_name
  LIMIT 1
) AS top_diner
UNION ALL
SELECT result FROM (
  SELECT m.dish_name AS result
  FROM DishReview r
  JOIN Dish m ON m.dish_id = r.dish_id
  WHERE r.reviewed_on BETWEEN '2025-02-01' AND '2025-02-28'
  GROUP BY m.dish_id, m.dish_name
  ORDER BY AVG(r.stars) DESC, m.dish_name
  LIMIT 1
) AS top_dish

Another way

WITH per_diner AS (SELECT diner_id, COUNT(*) AS n FROM DishReview GROUP BY diner_id),
feb AS (SELECT dish_id, AVG(stars) AS avg_stars FROM DishReview WHERE reviewed_on LIKE '2025-02-%' GROUP BY dish_id)
SELECT MIN(d.diner_name) AS result
FROM per_diner p JOIN Diner d ON d.diner_id = p.diner_id
WHERE p.n = (SELECT MAX(n) FROM per_diner)
HAVING COUNT(*) > 0
UNION ALL
SELECT MIN(m.dish_name)
FROM feb f JOIN Dish m ON m.dish_id = f.dish_id
WHERE f.avg_stars = (SELECT MAX(avg_stars) FROM feb)
HAVING COUNT(*) > 0

Another way

SELECT result FROM (
  SELECT d.diner_name AS result, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, d.diner_name) AS rn
  FROM DishReview r JOIN Diner d ON d.diner_id = r.diner_id
  GROUP BY d.diner_id, d.diner_name
) a WHERE rn = 1
UNION ALL
SELECT result FROM (
  SELECT m.dish_name AS result, ROW_NUMBER() OVER (ORDER BY AVG(r.stars) DESC, m.dish_name) AS rn
  FROM DishReview r JOIN Dish m ON m.dish_id = r.dish_id
  WHERE YEAR(r.reviewed_on) = 2025 AND MONTH(r.reviewed_on) = 2
  GROUP BY m.dish_id, m.dish_name
) b WHERE rn = 1