Tenure Mentorship Match

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Stitch Fix

Problem

Nobody's start date is on record, so whoever opened the earliest review on a repo counts as its longest-standing author. An engineering manager wants every review in code_reviews paired with that author as its mentor, listed repo by repo alphabetically, then by author name, then by review id.

Pair by tenure. Longest with newest.

Tables

code_reviews

Columns: review_id INTEGER, repo_name TEXT, author TEXT, reviewer TEXT, status TEXT, comments INTEGER, opened_at TEXT, merged TEXT

Expected output

Worked solution and explanation

What this really is

This is a per-group argmin broadcast dressed up as a mentorship program. Anyone can find each repo's earliest opened_at with MIN. What separates candidates is knowing that `MIN` hands you a timestamp, not the person who owns it, and that the answer needs that person stamped onto every review in the repo. Put author next to MIN(opened_at) and you get an error or an arbitrary author; stop at one row per repo and every review you were meant to route is gone.

Building it

Step 1: Order each repo's reviews oldest first

Number the rows of code_reviews inside each repo_name by opened_at, with review_id as the second key so a same-second tie still has one winner. Row 1 of each repo is the review that proves who has been around longest, and it carries its own author with it, which is exactly what MIN cannot do.

Step 2: Keep only rank 1

Filtering tenure_rank = 1 leaves one row per repo: the repo and its most tenured author. This is the argmin, and it is tiny: one row per repo, however many reviews there are.

Step 3: Broadcast the winner back onto every review

Join code_reviews to that small set on repo_name. Every review picks up its repo's mentor and keeps its own row, so a repo with forty reviews still returns forty rows. Putting tenure_rank = 1 in the join condition is what stops the fan-out.

Step 4: Sort the roster

repo_name, then author, then review_id, all ascending and all returned, so the manager reads each repo as one block and each author's reviews in id order.

WITH repo_first_author AS (
    SELECT
        repo_name,
        author AS most_tenured_author,
        ROW_NUMBER() OVER (PARTITION BY repo_name ORDER BY opened_at, review_id) AS tenure_rank
    FROM code_reviews
)
SELECT
    r.review_id,
    r.repo_name,
    r.author,
    f.most_tenured_author
FROM code_reviews r
JOIN repo_first_author f
    ON f.repo_name = r.repo_name
    AND f.tenure_rank = 1
ORDER BY r.repo_name, r.author, r.review_id

Minimum plus a tagalong author

SELECT repo_name, MIN(opened_at), author FROM code_reviews GROUP BY repo_name either fails because author is not grouped, or, where an engine allows it, returns some author from the group with no link to the minimum. It also returns one row per repo, not one per review.

Argmin, then join back

Order inside the repo, keep rank 1, and join it back. The author travels with the row that won, and the join restores one row per review.

Common follow-up questions

• How would you stop the mentor's own reviews from pairing with themselves and give them the runner-up instead? (Tests keeping rank 2 and choosing between the two per review.)
• What changes if tenure is measured across all repos rather than within each one? (Drops the partition key; the argmin becomes a single company-wide winner.)
• Could you also show how many reviews the mentor has opened on that repo? (Layers a second per-repo, per-author aggregate onto the CTE without breaking the grain.)
• What if `code_reviews` grows to a billion rows and is refreshed daily? (Probes maintaining the per-repo earliest review incrementally instead of re-sorting history.)