# Content Page Spreads > Content, laid out in two columns. Canonical URL: 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. ## Tables ### content_items - content_id INTEGER - title TEXT - content_type TEXT - duration_seconds INTEGER - creator_id INTEGER - publish_date TEXT Sample rows (10 of 200): | content_id | title | content_type | duration_seconds | creator_id | publish_date | | --- | --- | --- | --- | --- | --- | | 243 | The Ultimate Guide to AI Trends | article | NULL | 197 | 2026-02-02 | | 286 | Top 10 Mobile Apps | podcast | 154 | 294 | 2025-03-03 | | 329 | Understanding Machine Learning [DRAFT] | short | 201 | 391 | 2026-04-04 | | 372 | Mastering Tech Startups | livestream | 248 | 488 | 2025-05-05 | | 415 | Introduction to Cybersecurity | video | 295 | 585 | 2026-06-06 | | 458 | Deep Dive: Cloud Computing | article | NULL | 682 | 2025-07-07 | | 501 | Behind the Scenes of Product Management | podcast | 389 | 779 | 2026-08-08 | | 544 | Breaking Down DevOps | short | 436 | 876 | 2025-09-09 | | 587 | Exploring Web Development | livestream | 483 | NULL | 2026-01-10 | | 630 | How to Data Science | video | 530 | 1070 | 2025-11-11 | ## Expected output First 5 of 151 rows: | left_id | left_title | right_title | | --- | --- | --- | | 372 | Mastering Tech Startups | NULL | | 458 | Deep Dive: Cloud Computing | NULL | | 544 | Breaking Down DevOps | NULL | | 630 | How to Data Science | NULL | | 10004502 | Top 10 Mobile Apps | Understanding Machine Learning | ## 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. > **Trick to solving** > > Split `content_items` by parity first: even ids are the left pages, odd ids are the right pages. FULL OUTER JOIN the two on `r.content_id` = `l.content_id` + 1. Matched pairs become spreads, even pages with no id+1 partner keep a blank right, and odd pages with no id-1 partner keep a blank left. Every page surfaces exactly once. --- ### 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 **Full outer self-join of the even and odd pages** ```sql 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 ``` > **Cost analysis** > > Two parity scans of the 500K-row table feed one equality join on `content_id + 1`. A B-tree index on `content_id` turns the pairing into an index probe rather than a nested scan, so the plan stays close to two ordered passes plus the merge. > **Interviewers watch for** > > Whether the candidate surfaces BOTH unmatched directions. A single LEFT JOIN keeps the even orphans and looks complete on the sample, but it quietly loses every odd page with no even partner. Stopping there is the tell of someone who did not think through which side an orphan lands on. > **Common pitfall** > > Writing only the left-anchored join and calling it done. The even orphans show up with a blank right, so the result looks right at a glance, but the odd orphans never appear at all. The FULL OUTER JOIN is what brings them back. **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.)_ ## 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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.