Spotify: Student Plans Due for Re-Verification

Easy
928.

Spotify: Student Plans Due for Re-Verification

EasyDatesBasics

MySQL

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

ColumnTypeValues
user_idint
user_namevarchar
universityvarchar
verified_ondate
statusenumactive, cancelled

Primary key: user_id.

One row per Premium Student subscription; verified_on is the date enrolment was last confirmed.

Example

The rows must come back in the order shown.

StudentPlan

user_iduser_nameuniversityverified_onstatus
1AaravIIT Bombay2024-03-01active
2DiyaDelhi University2024-03-02active
3KabirBITS Pilani2023-08-15active
4MeeraAnna University2023-01-10cancelled
5RohanChrist University2024-02-29active
6ZaraIIT Madras2023-08-15active

Output

user_iduser_namedays_since_verified
3Kabir564
6Zara564
5Rohan366
1Aarav365
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.

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

ColumnType
user_idint
user_namevarchar
universityvarchar
verified_ondate
statusenum(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_iduser_nameuniversityverified_onstatus
1AaravIIT Bombay2024-03-01active
2DiyaDelhi University2024-03-02active
3KabirBITS Pilani2023-08-15active
4MeeraAnna University2023-01-10cancelled
5RohanChrist University2024-02-29active
6ZaraIIT Madras2023-08-15active

Output

user_iduser_namedays_since_verified
3Kabir564
6Zara564
5Rohan366
1Aarav365

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_id

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

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