Paid Accounts Dormant for Over 30 Days — SQL Problem
Customer success calls paying customers who have stopped using the product before they cancel. Today is 2024-09-30.
- Difficulty: Medium
- Topics: Subqueries, Dates
- Dialect: MySQL
- Problem: #409
Problem statement
Customer success calls paying customers who have stopped using the product before they cancel. Today is 2024-09-30. An account is dormant when it is active, on a paid plan (anything but free), and no one has logged in for more than 30 days — its latest login falls on a date more than 30 days before today, or it has never logged in at all.
Return account_id, company_name and last_login_on (the date of the latest login, NULL if none), ordered by last_login_on with never-logged-in accounts first, then by account_id.
Tables
Table: accounts
| Column | Type |
|---|---|
| account_id | int |
| company_name | varchar |
| plan | enum(free, starter, growth, enterprise) |
| status | enum(active, cancelled) |
Primary key: account_id.
One row per customer account.
Table: logins
| Column | Type |
|---|---|
| login_id | int |
| account_id | int |
| logged_in_at | datetime |
Primary key: login_id.
One row per sign-in by any member of the account.
Examples
Example 1
accounts
| account_id | company_name | plan | status |
|---|---|---|---|
| 1 | Zentrix Labs | growth | active |
| 2 | Quillpad | starter | active |
| 3 | Ledgerly | free | active |
| 4 | Orbitpay | enterprise | active |
| 5 | Vaani AI | growth | cancelled |
| 6 | Tiffinly | starter | active |
| 7 | Mintmark | growth | active |
logins
| login_id | account_id | logged_in_at |
|---|---|---|
| 1 | 1 | 2024-09-25 10:00:00 |
| 2 | 1 | 2024-07-01 09:00:00 |
| 3 | 2 | 2024-08-30 23:50:00 |
| 4 | 3 | 2024-06-01 12:00:00 |
| 5 | 5 | 2024-05-01 08:30:00 |
| 6 | 6 | 2024-08-31 00:10:00 |
| 7 | 7 | 2024-07-14 12:00:00 |
Output
| account_id | company_name | last_login_on |
|---|---|---|
| 4 | Orbitpay | NULL |
| 7 | Mintmark | 2024-07-14 |
| 2 | Quillpad | 2024-08-30 |
How to solve Paid Accounts Dormant for Over 30 Days
Dormancy is a property of each account's latest login, so the core is MAX(logged_in_at) per account. LEFT JOIN keeps accounts with no logins at all — their MAX is NULL — and the HAVING clause keeps an account when that MAX is NULL or more than 30 days before today. The plan and status filters are on the account row, so they belong in WHERE, before grouping.
The boundary: DATEDIFF('2024-09-30', d) > 30 holds for 30 August and not for 31 August, and DATEDIFF ignores the time of day, so a login at 00:10 on 31 August makes Tiffinly not dormant, while Quillpad's 23:50 on 30 August is 31 days ago. Equivalently, an account is dormant when no login exists on or after 2024-08-31 — the NOT EXISTS form, which never needs the maximum to decide and only computes it for display.
Ordering by last_login_on puts NULLs first in MySQL's ascending sort, which is the order asked for; account_id breaks ties between accounts last seen on the same date. With an index on logins(account_id, logged_in_at) every form reads one index entry per account.
Reference solution (MySQL)
SELECT a.account_id, a.company_name, DATE(MAX(l.logged_in_at)) AS last_login_on
FROM accounts a
LEFT JOIN logins l ON l.account_id = a.account_id
WHERE a.status = 'active' AND a.plan <> 'free'
GROUP BY a.account_id, a.company_name
HAVING MAX(l.logged_in_at) IS NULL OR DATEDIFF('2024-09-30', MAX(l.logged_in_at)) > 30
ORDER BY last_login_on, a.account_idAnother way
SELECT a.account_id, a.company_name,
(SELECT DATE(MAX(l.logged_in_at)) FROM logins l WHERE l.account_id = a.account_id) AS last_login_on
FROM accounts a
WHERE a.status = 'active' AND a.plan <> 'free'
AND NOT EXISTS (SELECT 1 FROM logins l WHERE l.account_id = a.account_id AND l.logged_in_at >= '2024-08-31')
ORDER BY last_login_on, a.account_idAnother way
SELECT account_id, company_name, last_login_on FROM (
SELECT a.account_id, a.company_name, (SELECT LEFT(MAX(l.logged_in_at), 10) FROM logins l WHERE l.account_id = a.account_id) AS last_login_on
FROM accounts a WHERE a.status = 'active' AND a.plan <> 'free'
) x
WHERE last_login_on IS NULL OR last_login_on < '2024-08-31'
ORDER BY last_login_on, account_id← Monthly Paid Revenue and Its Change From Last Month · Top Two Features in Every Pricing Plan →