Zomato: Feeding India Cities That Raised ₹50,000

Easy
1027.

Zomato: Feeding India Cities That Raised ₹50,000

Customers can add a donation to Feeding India at checkout. For the annual impact report the foundation's team wants the cities whose donors gave the most in 2024.

Consider only donations with status success made in 2024. Return each city whose total is at least 50000, with the columns city, total_raised and donors (the number of distinct donors behind those donations). Order by total_raised descending, then city ascending.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Donation

ColumnTypeValues
donation_idint
donor_idint
cityvarchar
amountint
donated_ondate
statusenumsuccess, failed, refunded

Primary key: donation_id.

One row per donation attempt, in rupees. A donor gives from one city.

Example

The rows must come back in the order shown.

Donation

donation_iddonor_idcityamountdonated_onstatus
111Mumbai250002024-01-15success
212Mumbai250002024-03-02success
311Mumbai100002024-05-10success
413Delhi400002024-02-11success
514Delhi200002024-02-12failed
615Delhi300002023-12-31success
716Pune500002024-12-31success
817Kochi300002024-06-01success
918Kochi300002024-06-02refunded
1019Kochi300002024-07-01success

Output

citytotal_raiseddonors
Kochi600002
Mumbai600002
Pune500001
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Zomato: Feeding India Cities That Raised ₹50,000

Customers can add a donation to Feeding India at checkout. For the annual impact report the foundation's team wants the cities whose donors gave the most in…

  • Difficulty: Easy
  • Topics: Aggregation, Dates
  • Dialect: MySQL
  • Problem: #1027

Problem statement

Customers can add a donation to Feeding India at checkout. For the annual impact report the foundation's team wants the cities whose donors gave the most in 2024.

Consider only donations with status success made in 2024. Return each city whose total is at least 50000, with the columns city, total_raised and donors (the number of distinct donors behind those donations). Order by total_raised descending, then city ascending.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Donation

ColumnType
donation_idint
donor_idint
cityvarchar
amountint
donated_ondate
statusenum(success, failed, refunded)

Primary key: donation_id.

One row per donation attempt, in rupees. A donor gives from one city.

Examples

Example 1

Donation

donation_iddonor_idcityamountdonated_onstatus
111Mumbai250002024-01-15success
212Mumbai250002024-03-02success
311Mumbai100002024-05-10success
413Delhi400002024-02-11success
514Delhi200002024-02-12failed
615Delhi300002023-12-31success
716Pune500002024-12-31success
817Kochi300002024-06-01success
918Kochi300002024-06-02refunded
1019Kochi300002024-07-01success

Output

citytotal_raiseddonors
Kochi600002
Mumbai600002
Pune500001

How to solve Zomato: Feeding India Cities That Raised ₹50,000

The shape is filter → group → filter the groups. WHERE keeps only successful donations dated in 2024, so a failed attempt, a refund or a gift made on 31 December 2023 never reaches a total. GROUP BY city then forms one group per city, and SUM(amount) is the money it raised.

The threshold is about a group's total, which does not exist until the rows are grouped, so it goes in HAVING SUM(amount) >= 50000. The comparison is inclusive: a city that raised exactly 50,000 is in.

donors must count people, not donations — a donor who gave twice is one donor — hence COUNT(DISTINCT donor_id). Totals often tie, so the order uses city as the second key.

The alternative groups in a derived table and filters its output with an ordinary WHERE, testing the year with YEAR(); both are a single scan, though the date range can use an index and YEAR() cannot.

Reference solution (MySQL)

SELECT city, SUM(amount) AS total_raised, COUNT(DISTINCT donor_id) AS donors
FROM Donation
WHERE status = 'success'
  AND donated_on BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY city
HAVING SUM(amount) >= 50000
ORDER BY total_raised DESC, city

Another way

SELECT city, total_raised, donors FROM (
  SELECT city, SUM(amount) AS total_raised, COUNT(DISTINCT donor_id) AS donors
  FROM Donation WHERE status = 'success' AND YEAR(donated_on) = 2024
  GROUP BY city
) t
WHERE total_raised >= 50000
ORDER BY total_raised DESC, city ASC

← Zomato: Build the URL Slug of Every Restaurant Page · Zomato: Sold-Out Movie Shows on District →