Paytm: Soundbox Devices That Never Announced a Payment

Easy
849.

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

ColumnType
device_idint
merchant_namevarchar
cityvarchar
installed_ondate

Primary key: device_id.

One row per installed speaker; a merchant may have more than one.

Table: PaymentAlert

ColumnType
alert_idint
device_idint
paid_atdatetime
amountint

Primary key: alert_id.

One row per payment the speaker announced. device_id always names an installed device.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

SoundboxDevice

device_idmerchant_namecityinstalled_on
101Sharma General StoreJaipur2024-11-02
102Balaji MedicalsPune2024-11-15
103Annapurna SweetsIndore2024-12-01
104Gupta KiranaLucknow2024-12-03
105Om Sai VegetablesNagpur2024-12-09

PaymentAlert

alert_iddevice_idpaid_atamount
11012024-12-10 09:15:00120
21012024-12-10 11:40:0045
31032024-12-11 18:05:00260
41052024-12-12 07:30:0030

Output

device_idmerchant_name
102Balaji Medicals
104Gupta Kirana
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: 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

ColumnType
device_idint
merchant_namevarchar
cityvarchar
installed_ondate

Primary key: device_id.

One row per installed speaker; a merchant may have more than one.

Table: PaymentAlert

ColumnType
alert_idint
device_idint
paid_atdatetime
amountint

Primary key: alert_id.

One row per payment the speaker announced. device_id always names an installed device.

Examples

Example 1

SoundboxDevice

device_idmerchant_namecityinstalled_on
101Sharma General StoreJaipur2024-11-02
102Balaji MedicalsPune2024-11-15
103Annapurna SweetsIndore2024-12-01
104Gupta KiranaLucknow2024-12-03
105Om Sai VegetablesNagpur2024-12-09

PaymentAlert

alert_iddevice_idpaid_atamount
11012024-12-10 09:15:00120
21012024-12-10 11:40:0045
31032024-12-11 18:05:00260
41052024-12-12 07:30:0030

Output

device_idmerchant_name
102Balaji Medicals
104Gupta 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 NULL

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