Bookends

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

L4

Asked in

ESPN

Problem

Our side-by-side of the very first signup and the newest one keeps coming back empty, because both ends of users turn out to be accounts that never logged a session. Counting only people who appear in user_sessions, return every session belonging to the earliest and the most recent signup on one timeline, oldest session first.

The extremes of the user base.

Tables

user_sessions

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

users

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

Expected output

Worked solution and explanation

What this problem really is

This is a min and a max taken over the wrong population, dressed up as an engagement comparison. Anyone can write MIN(signup_date) and MAX(signup_date); the whole question is which rows they run over. Take them over all of users and both ends land on accounts that never logged a session: on the seed the earliest signup is a 2023 account and the latest is a 2026-09-12 account, and neither appears in user_sessions. The join back to sessions then returns nothing, which is exactly the empty comparison the statement complains about. Gate the population first, take the extremes second, then fan the winners back out to their session rows.

How to build it

Step 1: Narrow to users who actually have a session

Test each user with EXISTS against user_sessions and keep their signup_date. This must happen before any boundary is computed: the extremes are defined over this set, not over the raw users table. A semi-join rather than a plain join, because a user with five sessions must still be one row here.

Step 2: Take the earliest signup with a scalar MIN

Compare each session-having user's signup_date to (SELECT MIN(signup_date) FROM session_users). Matching on equality instead of grabbing one row with LIMIT 1 keeps everyone tied on that day. On the seed the earliest session-having signup is user 8151, on 2024-01-01.

Step 3: Take the latest with an anti-join

Left-join session_users to itself on later.signup_date > a.signup_date and keep the rows where later.user_id IS NULL: nobody in the population signed up after them. That is user 779, whose signup_date is '2026-09-09 14:53:00'. The time of day does not hurt: as text it still sorts after every earlier date.

Step 4: Combine, fan out, lay it on one timeline

UNION the two id sets (it collapses a user who is somehow both ends), join that tiny set back to user_sessions, project the six session columns and sort by session_start. The answer is eight rows, five for user 779 and three for user 8151, interleaved by when each session began.

WITH session_users AS (
    SELECT u.user_id, u.signup_date
    FROM users u
    WHERE EXISTS (
        SELECT 1
        FROM user_sessions s
        WHERE s.user_id = u.user_id
    )
),
extremes AS (
    SELECT su.user_id
    FROM session_users su
    WHERE su.signup_date = (SELECT MIN(signup_date) FROM session_users)
    UNION
    SELECT a.user_id
    FROM session_users a
    LEFT JOIN session_users later
        ON later.signup_date > a.signup_date
    WHERE later.user_id IS NULL
)
SELECT us.session_id,
       us.user_id,
       us.device_id,
       us.session_start,
       us.session_duration_sec,
       us.pages_viewed
FROM user_sessions us
JOIN extremes e ON us.user_id = e.user_id
ORDER BY us.session_start;

Boundaries first, filter later

WHERE signup_date = (SELECT MIN(signup_date) FROM users) picks a 2023 account with no sessions, and the join to user_sessions drops it. The same happens at the other end with the 2026-09-12 account, so the whole comparison comes back empty.

Filter first, boundaries second

The EXISTS gate builds session_users before anything else, so the MIN and the anti-join can only choose people who have sessions to return. User 8151 and user 779 are the answer, and every tied user would come through too.

Common follow-up questions

• Two different users both signed up on the earliest date. How does your query behave, and is that what the business wants? (Tests whether matching on the boundary value, not `LIMIT 1`, is understood as what preserves ties.)
• The team now wants one row per extreme user with their total session count. How does the query change? (Tests changing grain without breaking the population filter.)
• `user_sessions` is partitioned by `session_start` across 365 partitions. Does that help this query, and what would you add to keep it fast? (Tests scaling instincts on a table partitioned by a column the query does not filter on.)