# Content Page Spreads

> Content, laid out in two columns.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

Think of content as a book where each spread pairs an even-numbered content_id (left page) with the next sequential odd-numbered content_id (right page). Pages without a matching partner should still appear with a blank on the missing side. Return the left ID, left title, and right title.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests self-joins and modular arithmetic for pairing adjacent records. The even/odd page pairing pattern probes whether you can express mathematical row-pairing logic in SQL.

> **Trick to Solving**
>
> The phrase "even-numbered ID with the next sequential odd-numbered ID" means pairing ID n (even) with ID n+1 (odd). The trick is a self-join where left.content_id = right.content_id - 1 with left.content_id being even.
> 
> 1. Filter even IDs for the left page
> 2. Join to odd IDs (even_id + 1) for the right page
> 3. Use FULL OUTER JOIN to include unpaired pages

---

### Break down the requirements

#### Step 1: Identify even and odd content IDs

Even: `content_id % 2 = 0`. Odd: `content_id % 2 = 1`.

#### Step 2: Pair even with next odd

Join on `even.content_id + 1 = odd.content_id`. Use FULL OUTER JOIN to keep unpaired pages.

#### Step 3: Return paired titles

Show left (even) ID, left title, and right (odd) title. NULLs appear for missing partners.

---

### The solution

**Self-join with modular arithmetic pairing**

```sql
SELECT
    l.content_id AS left_id,
    l.title AS left_title,
    r.title AS right_title
FROM content_items l
FULL OUTER JOIN content_items r
    ON l.content_id + 1 = r.content_id
    AND l.content_id % 2 = 0
    AND r.content_id % 2 = 1
WHERE l.content_id % 2 = 0
   OR r.content_id % 2 = 1
```

> **Cost Analysis**
>
> Self-join of 500K rows. The join condition on `content_id + 1` can use a B-tree index on `content_id`. Output is ~250K spread pairs.

> **Interviewers Watch For**
>
> Whether the candidate uses FULL OUTER JOIN for unpaired pages vs INNER JOIN (which drops them) or LEFT JOIN (which only keeps even pages without right partners).

> **Common Pitfall**
>
> Using LEFT JOIN instead of FULL OUTER JOIN would miss odd-numbered pages that have no even partner (the first page if it is odd, or gaps in the ID sequence).

---

## Common follow-up questions

- What if content_id values have gaps? _(Some even IDs might not have an adjacent odd ID, producing NULL right pages. The FULL OUTER JOIN handles this.)_
- How would you number the spreads sequentially? _(Add ROW_NUMBER() OVER (ORDER BY left_id) for spread numbering.)_
- What if the pairing was by publish_date order instead of ID? _(Tests ROW_NUMBER to assign sequential positions, then pair by row number.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/content_page_spreads)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.