Dormant Prepaid Subscribers Since March

Easy
499.

Dormant Prepaid Subscribers Since March

A prepaid subscriber who has not recharged for a while is about to be disconnected. The operator calls a subscriber dormant when they have no recharge on or after 2024-03-01 — including subscribers who never recharged at all.

Return every dormant subscriber with the columns subscriber_id and name. Recharges made before March do not make anyone active. Return the rows in any order.

Tables

Table: Subscriber

ColumnType
subscriber_idint
namevarchar
mobile_numbervarchar
circlevarchar

Primary key: subscriber_id.

One row per prepaid connection; circle is the telecom circle the number belongs to.

Table: Recharge

ColumnType
recharge_idint
subscriber_idint
amountint
recharge_datedate

Primary key: recharge_id.

Every subscriber_id here is in Subscriber. amount is in rupees.

Example

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

Subscriber

subscriber_idnamemobile_numbercircle
1Aarav9845012345Karnataka
2Diya9900123456Kerala
3Farhan9731122334Karnataka
4Meera8123456789Tamil Nadu
5Rohan7019988776Gujarat

Recharge

recharge_idsubscriber_idamountrecharge_date
112992024-03-01
222992024-02-28
345792024-01-10
442992024-04-02
55982024-02-10
62982024-01-15

Output

subscriber_idname
2Diya
3Farhan
5Rohan
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.

Dormant Prepaid Subscribers Since March — SQL Joins Problem

A prepaid subscriber who has not recharged for a while is about to be disconnected.

  • Difficulty: Easy
  • Topics: Joins, Subqueries
  • Dialect: MySQL
  • Problem: #499

Problem statement

A prepaid subscriber who has not recharged for a while is about to be disconnected. The operator calls a subscriber dormant when they have no recharge on or after 2024-03-01 — including subscribers who never recharged at all.

Return every dormant subscriber with the columns subscriber_id and name. Recharges made before March do not make anyone active. Return the rows in any order.

Tables

Table: Subscriber

ColumnType
subscriber_idint
namevarchar
mobile_numbervarchar
circlevarchar

Primary key: subscriber_id.

One row per prepaid connection; circle is the telecom circle the number belongs to.

Table: Recharge

ColumnType
recharge_idint
subscriber_idint
amountint
recharge_datedate

Primary key: recharge_id.

Every subscriber_id here is in Subscriber. amount is in rupees.

Examples

Example 1

Subscriber

subscriber_idnamemobile_numbercircle
1Aarav9845012345Karnataka
2Diya9900123456Kerala
3Farhan9731122334Karnataka
4Meera8123456789Tamil Nadu
5Rohan7019988776Gujarat

Recharge

recharge_idsubscriber_idamountrecharge_date
112992024-03-01
222992024-02-28
345792024-01-10
442992024-04-02
55982024-02-10
62982024-01-15

Output

subscriber_idname
2Diya
3Farhan
5Rohan

How to solve Dormant Prepaid Subscribers Since March

This is an anti join with a condition on the other side: keep a subscriber when no recharge of theirs is dated 1 March or later.

With a LEFT JOIN, the date test has to sit in the ON clause. There it restricts which recharges may match, and a subscriber with only February recharges (or none) is kept once with NULLs on the recharge side; WHERE r.recharge_id IS NULL then keeps exactly those. Putting the date test in WHERE instead would discard every NULL-extended row — the dormant subscribers — and turn the query into a plain inner join.

NOT EXISTS with the same correlated condition says it directly and stops at the first qualifying recharge. NOT IN works here too because subscriber_id is never NULL in Recharge; with a nullable column it would silently return nothing. A recharge on exactly 2024-03-01 counts as activity, so >= is the right comparison. With an index on (subscriber_id, recharge_date) each subscriber costs one index probe.

Reference solution (MySQL)

SELECT s.subscriber_id, s.name
FROM Subscriber s
LEFT JOIN Recharge r
  ON r.subscriber_id = s.subscriber_id AND r.recharge_date >= '2024-03-01'
WHERE r.recharge_id IS NULL

Another way

SELECT subscriber_id, name FROM Subscriber s WHERE NOT EXISTS (SELECT 1 FROM Recharge r WHERE r.subscriber_id = s.subscriber_id AND r.recharge_date >= '2024-03-01')

Another way

SELECT subscriber_id, name FROM Subscriber WHERE subscriber_id NOT IN (SELECT subscriber_id FROM Recharge WHERE recharge_date >= '2024-03-01')

← Prepaid Plans Expiring in the First Week of June · August Data Usage by Telecom Circle →