Getting the latest row within each group (greatest-n-per-group), such as “the latest login for each user,” is one of the most common requirements in practice. If you write logged_at = (SELECT MAX(...)) with a correlated subquery, you get not only N+1 (Q1) but also a correctness problem where a timestamp tie returns two rows. With the window function ROW_NUMBER(), PARTITION BY divides the groups and the within-group ordering assigns a number to every row—the whole table can be processed in a single scan (one pass).

-- ✗ Search for MAX row by row (N+1) + both rows in a timestamp tie are returned
WHERE logged_at = (SELECT MAX(logged_at) FROM logins x
                   WHERE x.user_id = l.user_id)

-- ✓ Number each partition → keep only rn = 1 outside (always exactly one row)
ROW_NUMBER() OVER (PARTITION BY user_id
                    ORDER BY logged_at DESC, login_id DESC) AS rn

From the logins table, retrieve the latest login for each user (timestamp and device). If multiple logins have the same timestamp, choose the one with the larger login_id, guaranteeing exactly one row per user. Write it so the table is scanned only once. The output columns must be user_id, logged_at, device (user_id ascending).

※ Before writing it, predict how many rows user_id=3 would produce with a correlated MAX subquery.

Expected output (user_id ascending):

user_id=3 has login_id 4 and 6 at the same time. The tie-breaker (login_id DESC) selects tablet (id=6) as “the latest row.” The MAX comparison version returns both rows.

SELECT   user_id, logged_at, device
FROM (
  SELECT user_id, logged_at, device,
         ROW_NUMBER() OVER (PARTITION BY user_id                      -- one partition per user
                            ORDER BY logged_at DESC, login_id DESC) AS rn  -- tie-breaker is required
  FROM   logins
) t
WHERE    rn = 1   -- a window function cannot be written directly in WHERE; filter outside
ORDER BY user_id;

-- Alternative (PostgreSQL only, concise):
-- SELECT DISTINCT ON (user_id) user_id, logged_at, device
-- FROM logins ORDER BY user_id, logged_at DESC, login_id DESC;

/*
  Logical evaluation order:
  1. Inner FROM logins    → one scan
  2. Window               → partition by user_id and assign ROW_NUMBER
  3. Outer WHERE rn = 1   → one row per partition
  4. SELECT               → finalize the three columns
  5. ORDER BY user_id      → sort ascending
  */

The standard pagination pattern LIMIT 20 OFFSET 100000 internally reads 100,020 rows and throws away 100,000 of them. Because the cost of OFFSET is proportional to the number of discarded rows, later pages become slower; inserting a row midway can also cause page drift (duplicates and omissions). Keyset pagination (the seek method) remembers “the key of the last row on the previous page” and reads the index from there—so every page has a constant cost.

-- ✗ Page 3: read 6 rows and discard 4 (imagine page 100,000...)
ORDER BY published_at DESC, article_id DESC LIMIT 2 OFFSET 4

-- ✓ Keyset: read only the continuation after the last key on the previous page
WHERE (published_at, article_id) < (TIMESTAMP '2026-05-05 13:00', 5)
ORDER BY published_at DESC, article_id DESC LIMIT 2

Display an article list from the articles table (1 million actual rows, with a composite index on (published_at, article_id)) in newest-first order (published_at DESC, article_id DESC), two rows per page. The last row on page 2 was (published_at, article_id) = ('2026-05-05 13:00', 5). Without using OFFSET, retrieve the two rows on page 3. The output columns must be article_id, title, published_at.

※ The newest-first order is 8→7→6→5→4→3→2→1. Page 1=8,7 / page 2=6,5. Preventing tied id=6 from leaking into page 3 is the keyset design challenge.

Expected output (page 3, newest first):

id=6 has (13:00, 6), which is ahead in the sort order of cursor (13:00, 5) because id DESC puts 6 first; it was already shown on page 2. The tuple comparison excludes it correctly.

SELECT   article_id, title, published_at
FROM     articles
WHERE    (published_at, article_id) < (TIMESTAMP '2026-05-05 13:00', 5)  -- key of the last row on the previous page
ORDER BY published_at DESC, article_id DESC  -- same columns and directions as the cursor
LIMIT    2;

-- Assumed index: CREATE INDEX ON articles (published_at, article_id); (scan backward for DESC)

/*
  Logical evaluation order:
  1. FROM articles       → choose the source
  2. WHERE row-value comparison → convert to an index seek
  3. ORDER BY             → use index order with no sort
  4. LIMIT                → stop after 2 rows
  */

When you want to know “does this user have an order?”, writing COUNT(*) > 0 makes the database count every row. What you actually want is a Boolean “yes/no,” not a count. EXISTS stops as soon as the inner query returns even one row—its internal representation is a Semi Join, the same tool as Q4 (avoid fan-out without DISTINCT). The difference between asking for a count and asking for existence can change the amount of scanning by a factor of one hundred or one thousand.

-- ✗ Cannot decide until the count is complete (worst case: read every row)
SELECT CASE WHEN (SELECT COUNT(*) FROM orders WHERE user_id = 42) > 0
            THEN 'yes' ELSE 'no' END;

-- ✓ Stop the moment one row is found (Semi Join)
SELECT EXISTS (SELECT 1 FROM orders WHERE user_id = 42);

From users (100,000 actual rows) and orders (5 million actual rows, with an index on user_id), build a list of users who have at least one order and users with no orders, with an existence flag for each. Do not count orders per user for the judgment (the count is not needed and the scan would be wasteful). The output columns must be user_id, name, has_order ('yes' / 'no', user_id ascending).

※ Even if Sato has 1 million orders, this judgment should be settled the moment the first row is read.

Expected output (user_id ascending):

The COUNT version counts all 1 million of Sato's orders before deciding '> 0'. The EXISTS version uses idx(user_id) and is decided the moment it finds one row.

SELECT   u.user_id, u.name,
         CASE WHEN EXISTS (
           SELECT 1 FROM orders o WHERE o.user_id = u.user_id  -- stop immediately when one row is found
         ) THEN 'yes' ELSE 'no' END AS has_order
FROM     users u
ORDER BY u.user_id;

-- ✗: CASE WHEN (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id) > 0 …
--      → Count every order per user (run the idx(user_id) Range Scan to the end)

/*
  Logical evaluation order:
  1. FROM users        → scan
  2. EXISTS in SELECT  → stop after one row via the index (Semi Join)
  3. CASE              → convert TRUE/FALSE to 'yes'/'no'
  4. ORDER BY user_id  → sort ascending
  */

As a capstone for this part, build a practical admin-screen query. “Among unprocessed tasks, show only the latest one for each assignee, then page through them newest-first with a cursor”—five foundational techniques divide the work cleanly.

① A partial index (Q5) indexes only the minority of 'pending' rows, ② ROW_NUMBER (Q7) selects the latest row per assignee, ③ keyset pagination (Q8) pages without OFFSET, ④ avoiding implicit type conversion (Q6) keeps the index usable, and ⑤ EXISTS (Q9) expresses the filter as existence. The Part 3 fundamentals—communicate intent / do not wrap columns in functions / operate on the set once / stop at Top-N—all crystallize in one query.

-- The overall shape (pseudocode)
WITH ranked AS (             -- ① latest one per assignee with ROW_NUMBER
  SELECT ..., ROW_NUMBER() OVER (PARTITION BY assignee_id ORDER BY created_at DESC, task_id DESC) AS rn
  FROM tasks WHERE status = 'pending'   -- ② shape that lets the partial index work (align the string type)
)
SELECT ... FROM ranked
WHERE rn = 1
  AND (created_at, task_id) < (?, ?)              -- ③ continue with keyset pagination
  AND EXISTS (SELECT 1 FROM task_tags ...)        -- ⑤ existence check with EXISTS
ORDER BY created_at DESC, task_id DESC LIMIT ?;

Build the admin screen for a task-management SaaS showing the newest unprocessed task for each assignee, newest first. The requirements are:

• Show only the latest pending task for each assignee (tie-breaker: task_id DESC)
• Keep only tasks with the “important” tag (there is a tag = 'important' row in task_tags)
• Use cursor pagination in newest-first order (created_at DESC, task_id DESC), two rows per page
• Previous-page cursor: (created_at, task_id) = ('2026-06-04 11:00', 4) (retrieve the next page from this cursor position)
• Do not use OFFSET; also provide a partial index to avoid scanning every table row (status is varchar)

※ Latest pending per assignee: A1→task1, A2→task3, A3→task5, A4→task7. Filtering by important leaves 1, 3, 5, 7 (all four match). Newest first is 1→3→5→7. Page 1=1,3 / page 2=5,7, but the cursor (2026-06-04 11:00, 4) is placed at task=4 rather than on a page boundary.

Expected output (the next page from the cursor position, newest first):

Of the four targets (tasks 1, 3, 5, 7), tasks older than cursor (2026-06-04 11:00, 4) are 5 and 7. Display those two as the next page from the cursor position.

CREATE INDEX idx_tasks_pending_keyset                             -- Partial index: index only 'pending' rows in latest-per-assignee order (Q5 + Q7)
  ON tasks (assignee_id, created_at DESC, task_id DESC)
  WHERE status = 'pending';
CREATE INDEX idx_task_tags_task_tag ON task_tags (task_id, tag);  -- Supporting index: existence lookup for the important tag (Q9 EXISTS)

WITH ranked AS (
  SELECT task_id, assignee_id, title, created_at,
         ROW_NUMBER() OVER (PARTITION BY assignee_id                       -- ① one partition per assignee
                            ORDER BY created_at DESC, task_id DESC) AS rn  -- tie-breaker is required
  FROM   tasks
  WHERE  status = 'pending'                                              -- ② the partial index is implied (string-to-string comparison, Q6)
)
SELECT   task_id, assignee_id, title, created_at
FROM     ranked
WHERE    rn = 1                                                            -- keep only the latest row per assignee
  AND    (created_at, task_id) < (TIMESTAMP '2026-06-04 11:00', 4)       -- ③ keyset pagination (Q8)
  AND    EXISTS (                                                          -- ⑤ existence check (Q9)
           SELECT 1 FROM task_tags tt
           WHERE  tt.task_id = ranked.task_id AND tt.tag = 'important'
         )
ORDER BY created_at DESC, task_id DESC                                  -- same columns and directions as the cursor
LIMIT    2;

/*
  Logical evaluation order:
  1. CTE FROM/WHERE        → use the partial index for status='pending'
  2. CTE window            → partition and assign ROW_NUMBER (use per-assignee index order)
  3. Outer WHERE rn = 1    → keep the first row per assignee
  4. Keyset row-value comparison → seek toward older rows
  5. EXISTS                → check for important with one lookup (Semi Join)
  6. ORDER BY + LIMIT      → order the remaining candidates and stop at 2 rows
  */