Paytm: Wallet Users by KYC Level in Each City

Easy
851.

Paytm: Wallet Users by KYC Level in Each City

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

ColumnTypeValues
user_idint
cityvarchar
kyc_levelenumnone, minimum, full
joined_ondate

Primary key: user_id.

One row per wallet holder with their current KYC level.

Example

The rows must come back in the order shown.

WalletUser

user_idcitykyc_leveljoined_on
1Lucknowfull2023-04-11
2Lucknowminimum2024-01-20
3Lucknowminimum2024-06-02
4Indorenone2024-09-15
5Indorefull2022-12-30
6Kochinone2024-10-01
7Lucknownone2024-11-11
8Indorefull2023-07-07

Output

cityusersfull_kycmin_kyc
Indore320
Kochi100
Lucknow412
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.

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

ColumnType
user_idint
cityvarchar
kyc_levelenum(none, minimum, full)
joined_ondate

Primary key: user_id.

One row per wallet holder with their current KYC level.

Examples

Example 1

WalletUser

user_idcitykyc_leveljoined_on
1Lucknowfull2023-04-11
2Lucknowminimum2024-01-20
3Lucknowminimum2024-06-02
4Indorenone2024-09-15
5Indorefull2022-12-30
6Kochinone2024-10-01
7Lucknownone2024-11-11
8Indorefull2023-07-07

Output

cityusersfull_kycmin_kyc
Indore320
Kochi100
Lucknow412

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 city

Another 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 city

Another 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 →