The Dormant Accounts

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

Finance is cleaning up active accounts that have gone dark, deciding whether to nudge them or shut them down: the ones whose most recent login was over 90 days ago, and the ones who have never logged in at all. For each, show the username, account status, the date of their last login, and their lifetime spend.

They are still paying. They stopped showing up.

Tables

users

Columns: user_id INTEGER, username TEXT, account_status TEXT, email TEXT, signup_date TEXT, age_bucket TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, session_start TEXT, device_id INTEGER, session_duration_sec INTEGER, pages_viewed INTEGER

transactions

Columns: transaction_id INTEGER, user_id INTEGER, total_amount REAL, product_id INTEGER, quantity INTEGER, transaction_date TEXT

Expected output

Worked solution and explanation

What this problem really is

This is a per-account rollup over two optional child tables, dressed up as an account-cleanup report. Every active account has to reach the end whether or not it has sessions or purchases, and dormancy is a fact about the account's latest login, not about any one session. Anyone can write the joins. What separates candidates is that the two cohorts finance most wants are exactly the ones a careless query deletes. Require a matching session and every never-logged-in account vanishes. Require a purchase and, on this data, the report comes back empty, because not one dormant account ever bought anything. Test the 90-day cutoff on session rows instead of the latest one and an account that logged in last week sneaks in on the strength of an old session.

Break down the requirements

Step 1: Outer-join both child tables onto users

Start at users, LEFT JOIN user_sessions on user_id for login timestamps, then LEFT JOIN transactions on user_id for spend. Either side can be missing for an account finance cares about, so neither join may be allowed to drop a user.

Step 2: Filter to active accounts

Apply WHERE u.account_status = 'active' so the cohort is locked in before aggregation. It is a per-row test, so it belongs in WHERE, not HAVING.

Step 3: Aggregate per user

Group by u.user_id, u.username, u.account_status. Project MAX(s.session_start) AS last_login and COALESCE(SUM(t.total_amount), 0) AS lifetime_spend. A sum over no rows is NULL; the COALESCE is what reports a never-spent account as 0.

Step 4: Apply the dormancy filter in HAVING

Use HAVING MAX(s.session_start) < datetime('now', '-90 days') OR MAX(s.session_start) IS NULL. The first branch catches accounts that logged in long ago; the second catches accounts that never logged in, whose MAX(session_start) is NULL because the outer join found no session. Drop that branch and they vanish, because NULL < anything is not true.

The solution

SELECT
    u.username,
    u.account_status,
    MAX(s.session_start) AS last_login,
    COALESCE(SUM(t.total_amount), 0) AS lifetime_spend
FROM users u
LEFT JOIN user_sessions s ON u.user_id = s.user_id
LEFT JOIN transactions t ON u.user_id = t.user_id
WHERE u.account_status = 'active'
GROUP BY u.user_id, u.username, u.account_status
HAVING MAX(s.session_start) < datetime('now', '-90 days')
    OR MAX(s.session_start) IS NULL

Common follow-up questions

• How would you rewrite this so lifetime spend stays correct for accounts that have both sessions and purchases? (Tests pre-aggregating each child table per user before joining.)
• Finance wants to exclude accounts created in the last 90 days so fresh signups don't look dormant. How would you add that? (Tests adding a `signup_date` guard alongside the status filter.)
• If this needs to run hourly on 10M users, what would you precompute or cache to keep it cheap? (Tests maintaining a per-user last-login and spend table incrementally.)