Paid Accounts Dormant for Over 30 Days

Medium
409.

Paid Accounts Dormant for Over 30 Days

MediumSubqueriesDates

MySQL

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

ColumnTypeValues
account_idint
company_namevarchar
planenumfree, starter, growth, enterprise
statusenumactive, cancelled

Primary key: account_id.

One row per customer account.

Table: logins

ColumnType
login_idint
account_idint
logged_in_atdatetime

Primary key: login_id.

One row per sign-in by any member of the account.

Example

The rows must come back in the order shown.

accounts

account_idcompany_nameplanstatus
1Zentrix Labsgrowthactive
2Quillpadstarteractive
3Ledgerlyfreeactive
4Orbitpayenterpriseactive
5Vaani AIgrowthcancelled
6Tiffinlystarteractive
7Mintmarkgrowthactive

logins

login_idaccount_idlogged_in_at
112024-09-25 10:00:00
212024-07-01 09:00:00
322024-08-30 23:50:00
432024-06-01 12:00:00
552024-05-01 08:30:00
662024-08-31 00:10:00
772024-07-14 12:00:00

Output

account_idcompany_namelast_login_on
4OrbitpayNULL
7Mintmark2024-07-14
2Quillpad2024-08-30
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.

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

ColumnType
account_idint
company_namevarchar
planenum(free, starter, growth, enterprise)
statusenum(active, cancelled)

Primary key: account_id.

One row per customer account.

Table: logins

ColumnType
login_idint
account_idint
logged_in_atdatetime

Primary key: login_id.

One row per sign-in by any member of the account.

Examples

Example 1

accounts

account_idcompany_nameplanstatus
1Zentrix Labsgrowthactive
2Quillpadstarteractive
3Ledgerlyfreeactive
4Orbitpayenterpriseactive
5Vaani AIgrowthcancelled
6Tiffinlystarteractive
7Mintmarkgrowthactive

logins

login_idaccount_idlogged_in_at
112024-09-25 10:00:00
212024-07-01 09:00:00
322024-08-30 23:50:00
432024-06-01 12:00:00
552024-05-01 08:30:00
662024-08-31 00:10:00
772024-07-14 12:00:00

Output

account_idcompany_namelast_login_on
4OrbitpayNULL
7Mintmark2024-07-14
2Quillpad2024-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_id

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

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