Spotify: Student Plans Due for Re-Verification — SQL Problem
Premium Student is discounted only while the subscriber is still enrolled, so Spotify asks students to re-verify every 12 months.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #928
Problem statement
Premium Student is discounted only while the subscriber is still enrolled, so Spotify asks students to re-verify every 12 months. Billing runs the check on 2025-03-01.
Return every active student subscriber whose last verification was 12 months or more before 2025-03-01 — that is, verified_on on or before 2024-03-01 — with the columns user_id, user_name and days_since_verified (days from verified_on to 2025-03-01). Order by days_since_verified from most to fewest, then by user_id.
A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.
Tables
Table: StudentPlan
| Column | Type |
|---|---|
| user_id | int |
| user_name | varchar |
| university | varchar |
| verified_on | date |
| status | enum(active, cancelled) |
Primary key: user_id.
One row per Premium Student subscription; verified_on is the date enrolment was last confirmed.
Examples
Example 1
StudentPlan
| user_id | user_name | university | verified_on | status |
|---|---|---|---|---|
| 1 | Aarav | IIT Bombay | 2024-03-01 | active |
| 2 | Diya | Delhi University | 2024-03-02 | active |
| 3 | Kabir | BITS Pilani | 2023-08-15 | active |
| 4 | Meera | Anna University | 2023-01-10 | cancelled |
| 5 | Rohan | Christ University | 2024-02-29 | active |
| 6 | Zara | IIT Madras | 2023-08-15 | active |
Output
| user_id | user_name | days_since_verified |
|---|---|---|
| 3 | Kabir | 564 |
| 6 | Zara | 564 |
| 5 | Rohan | 366 |
| 1 | Aarav | 365 |
How to solve Spotify: Student Plans Due for Re-Verification
The rule is a cut-off date: anyone verified on or before 2024-03-01 has gone twelve months without re-verifying by the run date. Writing the cut-off as DATE_SUB('2025-03-01', INTERVAL 12 MONTH) lets the database work it out, and the comparison verified_on <= cut-off is inclusive because a student verified exactly a year ago is due today. A student verified on 2024-03-02 still has a day left.
The leap day is a nice check: 29 February 2024 plus one year is 28 February 2025 in MySQL's month arithmetic, which is before the run date, so that student is due under every formulation — the date range, DATE_SUB and DATE_ADD(verified_on, INTERVAL 1 YEAR) all agree.
DATEDIFF('2025-03-01', verified_on) gives the age of the verification in days, and the statement fixes the order, with user_id breaking ties between students verified the same day. One scan of the table, or an index range scan on verified_on.
Reference solution (MySQL)
SELECT user_id, user_name, DATEDIFF('2025-03-01', verified_on) AS days_since_verified
FROM StudentPlan
WHERE status = 'active'
AND verified_on <= '2024-03-01'
ORDER BY days_since_verified DESC, user_idAnother way
SELECT user_id, user_name, DATEDIFF('2025-03-01', verified_on) AS days_since_verified FROM StudentPlan WHERE status = 'active' AND verified_on <= DATE_SUB('2025-03-01', INTERVAL 12 MONTH) ORDER BY days_since_verified DESC, user_idAnother way
SELECT user_id, user_name, DATEDIFF('2025-03-01', verified_on) AS days_since_verified FROM StudentPlan WHERE status <> 'cancelled' AND DATE_ADD(verified_on, INTERVAL 1 YEAR) <= '2025-03-01' ORDER BY 3 DESC, 1← Spotify: Artists Who Released Nothing in 2024 · Spotify: Artist Merch Revenue From Shipped Orders →