Paytm: Soundbox Devices That Never Announced a Payment
The Soundbox field team wants to visit every shop where the speaker was installed but has not read out a single payment — usually a SIM, power or pairing…
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #849
Problem statement
The Soundbox field team wants to visit every shop where the speaker was installed but has not read out a single payment — usually a SIM, power or pairing problem.
Return every device that has no row at all in PaymentAlert, with the columns device_id and merchant_name. Return the rows in any order.
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: SoundboxDevice
| Column | Type |
|---|---|
| device_id | int |
| merchant_name | varchar |
| city | varchar |
| installed_on | date |
Primary key: device_id.
One row per installed speaker; a merchant may have more than one.
Table: PaymentAlert
| Column | Type |
|---|---|
| alert_id | int |
| device_id | int |
| paid_at | datetime |
| amount | int |
Primary key: alert_id.
One row per payment the speaker announced. device_id always names an installed device.
Examples
Example 1
SoundboxDevice
| device_id | merchant_name | city | installed_on |
|---|---|---|---|
| 101 | Sharma General Store | Jaipur | 2024-11-02 |
| 102 | Balaji Medicals | Pune | 2024-11-15 |
| 103 | Annapurna Sweets | Indore | 2024-12-01 |
| 104 | Gupta Kirana | Lucknow | 2024-12-03 |
| 105 | Om Sai Vegetables | Nagpur | 2024-12-09 |
PaymentAlert
| alert_id | device_id | paid_at | amount |
|---|---|---|---|
| 1 | 101 | 2024-12-10 09:15:00 | 120 |
| 2 | 101 | 2024-12-10 11:40:00 | 45 |
| 3 | 103 | 2024-12-11 18:05:00 | 260 |
| 4 | 105 | 2024-12-12 07:30:00 | 30 |
Output
| device_id | merchant_name |
|---|---|
| 102 | Balaji Medicals |
| 104 | Gupta Kirana |
How to solve Paytm: Soundbox Devices That Never Announced a Payment
This is an anti join: keep the rows of one table that have no partner in another. The LEFT JOIN version keeps every device and attaches its alerts; a device with no alerts still appears once, with NULL in every PaymentAlert column. Filtering on a.alert_id IS NULL — the primary key, which is never NULL in a real alert — keeps exactly those silent devices. A device with several alerts produces several joined rows, but none of them pass the filter, so no duplicates reach the answer.
NOT EXISTS states the rule more literally: for each device, look for any alert with its id and keep it only if there is none. NOT IN works here as well because PaymentAlert.device_id is never NULL; if it could be, a single NULL in the subquery would make NOT IN return no rows at all, which is why NOT EXISTS is the safer habit.
With an index on PaymentAlert.device_id, each device costs one index probe.
Reference solution (MySQL)
SELECT d.device_id, d.merchant_name
FROM SoundboxDevice d
LEFT JOIN PaymentAlert a ON a.device_id = d.device_id
WHERE a.alert_id IS NULLAnother way
SELECT device_id, merchant_name FROM SoundboxDevice d WHERE NOT EXISTS (SELECT 1 FROM PaymentAlert a WHERE a.device_id = d.device_id)Another way
SELECT device_id, merchant_name FROM SoundboxDevice WHERE device_id NOT IN (SELECT device_id FROM PaymentAlert)← Paytm: Failed Recharges Still Awaiting a Refund · Paytm: Merchant QR Codes Still on the Old @paytm Handle →