Deploy Author Performance Score

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

Spotify

Problem

Each row of deploy_logs is one deploy, and the same author is sometimes logged under different capitalization. The calendar year holding the newest deploy is still in progress, so only the two most recent full years before it count. Give each author their average duration in whole seconds (any fraction dropped), a score of (100 minus that average) times their number of deploys, and a standing where 1 is the strongest score.

Not all deployers are equally reliable.

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

Expected output

Worked solution and explanation

What this really is

Strip off the scorecard and this is a window-scoping problem: the scorecard covers the two complete calendar years behind the year the log's newest deploy sits in, and both 'newest' and 'complete' are facts about the log, not the clock. Anyone can write AVG and COUNT(*) per author. What separates candidates is treating the newest year as finished. Take the two newest years present and you score 2025 and 2026 instead of 2024 and 2025: every count, average and standing moves. Two quieter traps sit underneath. 'alice' and 'Alice' are one person, and the average is whole seconds with the fraction dropped, so rounding it moves every score.

Building it up

Step 1: Find the open year

strftime('%Y', MAX(deploy_at)) is the year the newest deploy sits in. That year is still filling up, so it is the one bucket the scorecard must skip. The scalar subquery reads it straight off the log, which keeps the answer right whenever the log was cut.

Step 2: Keep the two newest full years

Bucket every deploy by strftime('%Y', deploy_at), drop the open year in the WHERE, sort the remaining years newest first and keep two. That two-row full_years set is the whole window.

Step 3: Scope the deploys with a join back to the log

Join deploy_logs to full_years on the same year expression. The join is the filter: a deploy from the open year or from an older one has no partner and drops out.

Step 4: Fold the names, then measure

Group on LOWER(d.author) so 'BOB' and 'bob' land in one row before anything is counted. COUNT(*) keeps null-duration deploys, AVG(d.dur_secs) ignores them, and CAST(... AS INTEGER) drops the fraction.

Step 5: Score, then assign the standing

Compute score once in its own CTE from the whole-second avg_duration. Then apply DENSE_RANK() OVER (ORDER BY score DESC) so the strongest score takes standing 1 and any tied scores would share a standing.

WITH full_years AS (
    SELECT strftime('%Y', deploy_at) AS deploy_year
    FROM deploy_logs
    WHERE strftime('%Y', deploy_at) <> (
        SELECT strftime('%Y', MAX(deploy_at))
        FROM deploy_logs
    )
    GROUP BY strftime('%Y', deploy_at)
    ORDER BY deploy_year DESC
    LIMIT 2
),
author_stats AS (
    SELECT
        LOWER(d.author) AS author,
        CAST(AVG(d.dur_secs) AS INTEGER) AS avg_duration,
        COUNT(*) AS deploy_count
    FROM deploy_logs AS d
    JOIN full_years AS f
        ON strftime('%Y', d.deploy_at) = f.deploy_year
    GROUP BY LOWER(d.author)
),
scored AS (
    SELECT
        author,
        avg_duration,
        (100 - avg_duration) * deploy_count AS score
    FROM author_stats
)
SELECT
    author,
    avg_duration,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS standing
FROM scored

Two newest years in the log

Grouping the years and keeping the top two looks right, but the top one is the open year. The scorecard silently shifts a year forward and grades people on a half-finished year.

Two newest full years

full_years removes the year of MAX(deploy_at) before the LIMIT 2, so the window is always the two complete years behind the newest deploy, whenever the log was cut.

Common follow-up questions

• An author with one fast deploy can beat a prolific one. How would you require a minimum number of deploys? (Tests filtering groups after aggregation on `deploy_count`.)
• What changes if the log had a gap year, with nothing logged in the year before the open one? (Tests whether 'the two newest full years present' and 'the two calendar years before the open one' are recognized as different windows.)
• How would you make the average robust to a few absurdly slow deploys? (Tests awareness that a median of `dur_secs` resists outliers where `AVG` does not.)