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
| Column | Type |
|---|---|
| donation_id | int |
| donor_id | int |
| city | varchar |
| amount | int |
| donated_on | date |
| status | enum(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_id | donor_id | city | amount | donated_on | status |
|---|---|---|---|---|---|
| 1 | 11 | Mumbai | 25000 | 2024-01-15 | success |
| 2 | 12 | Mumbai | 25000 | 2024-03-02 | success |
| 3 | 11 | Mumbai | 10000 | 2024-05-10 | success |
| 4 | 13 | Delhi | 40000 | 2024-02-11 | success |
| 5 | 14 | Delhi | 20000 | 2024-02-12 | failed |
| 6 | 15 | Delhi | 30000 | 2023-12-31 | success |
| 7 | 16 | Pune | 50000 | 2024-12-31 | success |
| 8 | 17 | Kochi | 30000 | 2024-06-01 | success |
| 9 | 18 | Kochi | 30000 | 2024-06-02 | refunded |
| 10 | 19 | Kochi | 30000 | 2024-07-01 | success |
Output
| city | total_raised | donors |
|---|---|---|
| Kochi | 60000 | 2 |
| Mumbai | 60000 | 2 |
| Pune | 50000 | 1 |
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, cityAnother 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 →