Prolific Authors in Largest Service Teams

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

Zenefits

Problem

List the stable versions shipped by whoever has deployed the most different versions in deploy_logs. Release candidates still count toward being the most prolific but stay out of the release team's digest, and the log's casing is unreliable, so 'BOB' and 'bob' are the same person.

Senior leads in the biggest teams.

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 problem really is

Under the release-digest costume this is a max-of-aggregates filter on a dirty key: count each person's different versions, find the top count, keep everyone sitting at it, then print their stable releases. The release-candidate filter is the easy part. What separates candidates is deciding who a person is before counting anything. The log signs one deployer 'bob' on one service and 'BOB' on another, so a raw count credits each spelling with a single version, everybody ties at one, and the digest names the whole team instead of the two people who actually ship the most.

Building it up

Step 1: Decide who a person is

Group on LOWER(author), never on author. 'bob' and 'BOB' each shipped one version; together they shipped two, and that is the whole difference between a leader and a tie with everyone.

Step 2: Count versions, not deploys

COUNT(DISTINCT version) per person measures how many releases they put out. Each version is deployed again and again across environments, so the distinct count is what keeps a redeploy from looking like new work.

Step 3: Find the top count

Wrap the per-person counts in the derived table shipped_counts and take MAX(versions_shipped). It runs over a handful of people, not the deploy rows, and returns the single bar everyone is measured against.

Step 4: Keep everyone at the top

Inside the IN subquery, HAVING COUNT(DISTINCT version) = (SELECT MAX(versions_shipped) ...) keeps exactly the people at the top. The outer LOWER(author) IN (...) then matches every spelling of those people back to their rows.

Step 5: Print the stable releases

Only now apply version NOT LIKE '%-rc%', and select LOWER(author) with version under DISTINCT. The filter lives in the outer query because release candidates still counted toward who leads; they just do not get printed.

The solution

SELECT DISTINCT LOWER(author) AS author, version
FROM deploy_logs
WHERE version NOT LIKE '%-rc%'
  AND LOWER(author) IN (
    SELECT LOWER(author)
    FROM deploy_logs
    GROUP BY LOWER(author)
    HAVING COUNT(DISTINCT version) = (
      SELECT MAX(versions_shipped)
      FROM (
        SELECT COUNT(DISTINCT version) AS versions_shipped
        FROM deploy_logs
        GROUP BY LOWER(author)
      ) AS shipped_counts
    )
  )

Filter, then count

Putting version NOT LIKE '%-rc%' in every subquery removes the release candidate from the ranking too. A leader whose second version was a candidate drops to one, and their stable release disappears from the digest without an error.

Count, then filter

The ranking reads every version; only the outer WHERE drops candidates. Both leaders stay at the top, and the digest lists only what actually shipped stable.

Common follow-up questions

• If the digest could credit only one person, how would you choose among tied leaders deterministically? (Tests replacing the tie-keeping match with a deterministic pick.)
• Could a window function over the per-person counts find the leaders, and would it scan `deploy_logs` fewer times? (Probes window-function fluency as an alternative to the nested scalar subquery.)
• `svc_name` also has casing and padding variants. If the question became services per person, what would you normalize? (Tests whether the candidate spots the other dirty columns.)