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
| Column | Type |
|---|---|
| subscriber_id | int |
| name | varchar |
| mobile_number | varchar |
| circle | varchar |
Primary key: subscriber_id.
One row per prepaid connection; circle is the telecom circle the number belongs to.
Table: Recharge
| Column | Type |
|---|---|
| recharge_id | int |
| subscriber_id | int |
| amount | int |
| recharge_date | date |
Primary key: recharge_id.
Every subscriber_id here is in Subscriber. amount is in rupees.
Examples
Example 1
Subscriber
| subscriber_id | name | mobile_number | circle |
|---|---|---|---|
| 1 | Aarav | 9845012345 | Karnataka |
| 2 | Diya | 9900123456 | Kerala |
| 3 | Farhan | 9731122334 | Karnataka |
| 4 | Meera | 8123456789 | Tamil Nadu |
| 5 | Rohan | 7019988776 | Gujarat |
Recharge
| recharge_id | subscriber_id | amount | recharge_date |
|---|---|---|---|
| 1 | 1 | 299 | 2024-03-01 |
| 2 | 2 | 299 | 2024-02-28 |
| 3 | 4 | 579 | 2024-01-10 |
| 4 | 4 | 299 | 2024-04-02 |
| 5 | 5 | 98 | 2024-02-10 |
| 6 | 2 | 98 | 2024-01-15 |
Output
| subscriber_id | name |
|---|---|
| 2 | Diya |
| 3 | Farhan |
| 5 | Rohan |
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 NULLAnother 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 →