🧠 1. Plain-English Mental Model (Deduplicating & Ranking Without Collapsing Rows):
In real-world AI evaluation pipelines, a single prompt (P-101) often gets graded multiple times as raters re-audit tasks over several days. A classic senior-level SQL interview question is:
"Write a query that returns ONLY the single most recent evaluation row for each prompt_id."
Why doesn't GROUP BY prompt_id solve this cleanly? Because GROUP BY smashes rows together—it can tell you MAX(created_at), but it doesn't cleanly hand you the exact rater_name and score belonging to that latest row.
Instead, we solve this in two clean steps using a Window Function inside a CTE (Common Table Expression):

Step 1 — Number the rows inside each prompt (ROW_NUMBER() OVER (...)): Without collapsing any rows, add a virtual column rn that ranks rows 1, 2, 3... inside each prompt_id from newest to oldest.
Step 2 — Wrap Step 1 in a temporary named table (WITH ranked AS (...)) and filter WHERE rn = 1!

🔍 2. Clause-by-Clause Syntax Decoder:

WITH ranked AS (                                  -- 1. `WITH name AS (...)` defines a temporary table (CTE)
    SELECT
        prompt_id,
        rater_name,
        score,
        ROW_NUMBER() OVER (                       -- 2. Window function: assigns 1, 2, 3... to each row
            PARTITION BY prompt_id                --    Restarts numbering at 1 for each distinct `prompt_id`
            ORDER BY created_at DESC              --    Row with the latest `created_at` gets `rn = 1`!
        ) AS rn
    FROM prompt_evals
)
SELECT prompt_id, rater_name, score               -- 3. Query our `ranked` CTE just like a normal table
FROM ranked
WHERE rn = 1                                      -- 4. Keep ONLY the #1 most recent row per prompt_id!
ORDER BY prompt_id ASC;

Why do we need the WITH ranked AS (...) CTE? SQL does not allow window functions like ROW_NUMBER() inside a WHERE clause in the same query block (because WHERE runs before SELECT!). Wrapping the window function in a CTE lets the outer query filter WHERE rn = 1 cleanly.
ROW_NUMBER() vs RANK() vs DENSE_RANK(): ROW_NUMBER() always assigns unique numbers (1, 2, 3) even on ties. RANK() gives ties the same rank and skips the next number (1, 1, 3). DENSE_RANK() gives ties the same rank without gaps (1, 1, 2).

📘 3. Worked Mini-Example:

-- SAMPLE TABLE: prompt_evals
-- eval_id: 1 | prompt_id: 'P-101' | rater_name: 'Alice' | score: 3 | created_at: '2026-10-01 09:00:00' -> rn = 2
-- eval_id: 2 | prompt_id: 'P-101' | rater_name: 'Bob'   | score: 5 | created_at: '2026-10-02 14:30:00' -> rn = 1 (Newest!)
-- eval_id: 3 | prompt_id: 'P-102' | rater_name: 'Carol' | score: 4 | created_at: '2026-10-01 11:00:00' -> rn = 1 (Newest!)
-- eval_id: 4 | prompt_id: 'P-103' | rater_name: 'Dave'  | score: 2 | created_at: '2026-10-01 08:00:00' -> rn = 2
-- eval_id: 5 | prompt_id: 'P-103' | rater_name: 'Eve'   | score: 1 | created_at: '2026-10-03 16:00:00' -> rn = 1 (Newest!)

-- OUTPUT ROWS (After filtering `WHERE rn = 1` in outer query):
-- 1. {prompt_id: 'P-101', rater_name: 'Bob',   score: 5}
-- 2. {prompt_id: 'P-102', rater_name: 'Carol', score: 4}
-- 3. {prompt_id: 'P-103', rater_name: 'Eve',   score: 1}

Conner's Private AI & ML Dojo