Paytm: Wallet Users by KYC Level in Each City — SQL Problem
A wallet with only minimum KYC has lower limits than one with full KYC, so the compliance team tracks how far each city has moved users up the ladder.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #851
Problem statement
A wallet with only minimum KYC has lower limits than one with full KYC, so the compliance team tracks how far each city has moved users up the ladder.
For every city with at least one wallet user, return city, users (all its users), full_kyc (users at level full) and min_kyc (users at level minimum). Users at level none count only in users. A city with no user at some level shows 0 for it. Return the rows ordered by city.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: WalletUser
| Column | Type |
|---|---|
| user_id | int |
| city | varchar |
| kyc_level | enum(none, minimum, full) |
| joined_on | date |
Primary key: user_id.
One row per wallet holder with their current KYC level.
Examples
Example 1
WalletUser
| user_id | city | kyc_level | joined_on |
|---|---|---|---|
| 1 | Lucknow | full | 2023-04-11 |
| 2 | Lucknow | minimum | 2024-01-20 |
| 3 | Lucknow | minimum | 2024-06-02 |
| 4 | Indore | none | 2024-09-15 |
| 5 | Indore | full | 2022-12-30 |
| 6 | Kochi | none | 2024-10-01 |
| 7 | Lucknow | none | 2024-11-11 |
| 8 | Indore | full | 2023-07-07 |
Output
| city | users | full_kyc | min_kyc |
|---|---|---|---|
| Indore | 3 | 2 | 0 |
| Kochi | 1 | 0 | 0 |
| Lucknow | 4 | 1 | 2 |
How to solve Paytm: Wallet Users by KYC Level in Each City
This is conditional aggregation: one pass over the table, grouped by city, with several counts that each look at a different subset of the group. COUNT(*) counts everyone. For the KYC levels, SUM(CASE WHEN kyc_level = 'full' THEN 1 ELSE 0 END) adds one per full-KYC user and nothing for the others — the ELSE 0 is what makes a city with no full-KYC users show 0 rather than NULL.
The alternatives are the same idea in other spellings: COUNT(IF(cond, 1, NULL)) relies on COUNT ignoring NULLs, and in MySQL a comparison is itself 0 or 1, so SUM(kyc_level = 'full') adds the matches directly.
Cities only appear if some user lives there, because groups are formed from rows; that is exactly what the statement asks. The query is one scan plus a grouping, and the final ORDER BY city fixes the order.
Reference solution (MySQL)
SELECT city,
COUNT(*) AS users,
SUM(CASE WHEN kyc_level = 'full' THEN 1 ELSE 0 END) AS full_kyc,
SUM(CASE WHEN kyc_level = 'minimum' THEN 1 ELSE 0 END) AS min_kyc
FROM WalletUser
GROUP BY city
ORDER BY cityAnother way
SELECT city, COUNT(user_id) AS users, COUNT(IF(kyc_level = 'full', 1, NULL)) AS full_kyc, COUNT(IF(kyc_level = 'minimum', 1, NULL)) AS min_kyc FROM WalletUser GROUP BY city ORDER BY cityAnother way
SELECT city, COUNT(*) AS users, SUM(kyc_level = 'full') AS full_kyc, SUM(kyc_level = 'minimum') AS min_kyc FROM WalletUser GROUP BY city ORDER BY city← Paytm: Merchant QR Codes Still on the Old @paytm Handle · Paytm: February LPG Cylinder Bookings by Gas Provider →