# Bookends

> The extremes of the user base.

Canonical URL: <https://datadriven.io/problems/bookends>

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

The growth team wants to compare engagement between the very first person to sign up and the most recent one, looking only at people who have actually logged a session. Return every session record belonging to those two users.

## Worked solution and explanation

### What this problem really is

This looks like a reporting ask, but underneath it is 'find the rows at both extreme ends of a sorted column, then fan back out to a detail table.' The skill being probed: can you pin the earliest and latest signup_date over the RIGHT population and carry exactly those identities back into user_sessions without dragging in everyone else. The trap is the population. The extremes must be measured only among users who own at least one session, so a candidate who takes the boundaries over the whole users table can silently pick a person with zero sessions and hand back an empty or wrong detail set. In the seed, user 100 signed up but never logged a session, so it must never be eligible to be an extreme.

---

### How to build it

#### Step 1: Narrow to users who actually have a session

A user counts only if they have logged at least one session, so test each one with EXISTS against user_sessions. Doing this BEFORE you touch signup_date is the whole game: the extremes are defined relative to this filtered set, not the raw users table. User 100 signed up but never logged a session, so the EXISTS filter drops it before any boundary is computed.

#### Step 2: Grab the earliest signup with a scalar MIN subquery

Find the smallest signup_date across the filtered set with a scalar subquery, then keep every session-having user whose signup_date equals it. Matching on equality (not fetching one ORDER BY plus LIMIT 1 row) means that if two people signed up on that earliest day, both come through. In the seed the earliest is user 294 on 2024-04-04.

#### Step 3: Grab the latest with an anti-join, then UNION and project

For the other end, self-join session_users to itself and keep only the users for whom no peer has a later signup_date: that left anti-join leaves exactly the most recent signups, ties included. UNION the earliest set with the latest set (UNION, not UNION ALL, collapses the rare user who is somehow both ends), join that small id set back to user_sessions, and project all six columns ordered by user_id then session_id. The latest here is user 779 on 2026-09-09.

---

### The solution

**Scope the population, take the earliest by MIN and the latest by an anti-join, UNION, then join sessions**

```sql
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.user_id, us.session_id;
```

> **Trick to solving**
>
> There are two clean idioms for grabbing a boundary while keeping ties, and this answer uses one for each end so you can see both. Comparing signup_date to a scalar MIN holds every user tied on the earliest day. A left anti-self-join (no peer with a later date) holds every user tied on the latest day. Either idiom beats ORDER BY plus LIMIT 1, which silently keeps just one row.

**Naive: ORDER BY ... LIMIT 1**

SELECT user_id FROM session_users ORDER BY signup_date LIMIT 1 grabs ONE earliest user and silently discards anyone tied on the same signup_date. It needs a second, separately ordered scan for the latest user, and it says nothing about the session filter.

**Tie-safe: equality to MIN, or an anti-join**

Comparing signup_date to (SELECT MIN(signup_date) FROM session_users) keeps every user tied on the earliest date, and the left anti-self-join keeps every user tied on the latest. The EXISTS filter guarantees both ends are drawn only from users who actually have a session.

> **Interviewers watch for**
>
> The tell of a strong candidate is whether they scope the boundaries to session-havers and whether they ask about ties. Reaching for LIMIT 1 is the signal that they have not considered two users sharing a signup date. Watch also for whether they keep the detail projection (all six session columns) separate from the selection logic instead of aggregating the sessions away.

> **Common pitfall**
>
> The classic miss is computing the extremes over the full users table. With 15,000,000 users and only a fraction owning sessions, the earliest or latest signup can land on a user with zero sessions, and the final join then returns nothing for that end. Define the population with EXISTS first, then take the boundaries.

> **Performance insight**
>
> At 60,000,000 sessions and 15,000,000 users, an index on user_sessions(user_id) turns the EXISTS into a cheap semi-join probe and lets the final id-to-sessions join seek instead of scan. The MIN subquery and the anti-self-join both run over the already-filtered session_users set, which is tiny next to the raw tables, so the dominant cost is reading the handful of qualifying session rows.

---

## Common follow-up questions

- Two different users both signed up on the earliest date. How does your query behave, and is that the behavior the business wants? _(Tests whether they understood that matching on the boundary value, not LIMIT 1, is what preserves ties.)_
- Instead of every session row, the team now wants just one row per extreme user with their total session count. How does the query change? _(Tests the EXISTS-versus-aggregate distinction and whether they can change grain without breaking the population filter.)_
- user_sessions is partitioned by session_start across 365 partitions. Does that partitioning help this query, and what index would you add to keep it fast? _(Tests scaling instincts on the partitioned sessions table.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/bookends)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.