Content Page Spreads

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

IBM

Problem

Picture the content library as a book: an even-numbered content_id is a left page, facing the content_id one higher (an odd number) on its right. Lay out every item exactly once as it sits in the book, each row carrying the left page's ID, the left page's title, and the right page's title. A page with no facing partner still appears, and every column that belongs to a missing page is left blank, so an odd page with no left neighbor shows a blank left ID and a blank left title, while an even page with no right neighbor shows a blank right title.

Content, laid out in two columns.

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

Expected output

Worked solution and explanation

Strip the metaphor

Take the book language away and this is a full outer self-join: pair each even page with its id+1 odd neighbor, and one hard question decides the grade. What happens to the pages with no neighbor? Anyone can write the pairing condition. What separates candidates is seeing that an orphan can sit on EITHER side: an even page with no right partner, or an odd page with no left one. Reach for a single LEFT JOIN and you keep the even orphans but silently lose every odd one; an INNER JOIN loses both. A FULL OUTER JOIN surfaces both directions at once, matched spreads plus orphans from either arm, in a single pass.

Walking through it

Step 1: Split the library by parity

Carve content_items into two sides with WHERE: content_id % 2 = 0 gives the even left pages, content_id % 2 = 1 gives the odd right pages. Keeping them as two subqueries makes the join read as left-side against right-side rather than one table wrestled against itself by hand.

Step 2: Join both sides so neither is lost

FULL OUTER JOIN the even side to the odd side on r.content_id = l.content_id + 1. A matched even page carries its right title; an even page with no id+1 partner still emits with a blank right; and an odd page with no id-1 partner emits with a blank left. A one-sided join would drop that last group without a word.

The solution

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

LEFT JOIN only

Keeps every even left page and attaches a right title when the id+1 partner exists. But an odd page with no even partner only ever lived on the right side of a match that failed, so a left-anchored join never emits it. Those orphans vanish with no error.

FULL OUTER JOIN

Emits matched spreads, even pages with a blank right, and odd pages with a blank left, all in one pass. Every page in the library surfaces exactly once regardless of which side its partner was missing from.

Common follow-up questions

• What if `content_id` values have gaps? (Gaps mean most even ids have no id+1 partner, so `right_title` comes back blank, and most odd ids qualify as left orphans. The full outer join already handles both directions; nothing changes.)
• How would you number the spreads sequentially? (Wrap the result and add `ROW_NUMBER`() OVER (ORDER BY COALESCE(`l.content_id`, `r.content_id`)) to give each spread a running position in id order.)
• What if the pairing was by `publish_date` order instead of ID? (Assign `ROW_NUMBER`() OVER (ORDER BY `publish_date`) to build a dense ordering, then full outer join consecutive rank values instead of raw ids.)