First Deploy Attribution

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

Tesla

Problem

Give each service in deploy_logs its total_deploys and its first_time_deploys, the deployments that were some author's first-ever deployment to that service: the onboarding-velocity number a release engineer tracks. Service names were typed with inconsistent letter case and surrounding spaces, and author names with inconsistent letter case, so each still means one service or one person.

The first deploy per service.

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

This is a two-grain count presented as an onboarding metric. The total lives at the service grain, but 'first' lives at the (author, svc_name) grain. Anyone can write COUNT(*) per service. The trap is judging 'first' per service alone, which pins first_time_deploys to 1 everywhere. That erases every engineer after the first who onboarded onto a service, and that signal is the reason the report exists.

Break down the requirements

Step 1: Rank within (author, svc_name)

Clean the names first: LOWER(TRIM(svc_name)) folds ' payment-api', 'Payment-API' and 'payment-api ' into one service, and LOWER(author) makes 'Alice' and 'alice' one engineer. Then ROW_NUMBER() OVER (PARTITION BY LOWER(author), LOWER(TRIM(svc_name)) ORDER BY deploy_at) tags each row. rn = 1 marks that author's first deploy to that service, so a service three engineers onboarded onto carries three of them.

Step 2: Aggregate per service

Group by svc_name. COUNT(*) is the total, and SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) is the first-deploy subset. Both are counted in the same pass over every row.

The solution

SELECT
  dl.svc_name,
  COUNT(*) AS total_deploys,
  SUM(CASE WHEN dl.rn = 1 THEN 1 ELSE 0 END) AS first_time_deploys
FROM (
  SELECT
    LOWER(TRIM(svc_name)) AS svc_name,
    ROW_NUMBER() OVER (
      PARTITION BY LOWER(author), LOWER(TRIM(svc_name))
      ORDER BY deploy_at
    ) AS rn
  FROM deploy_logs
) dl
GROUP BY dl.svc_name
;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• What if two deploys by the same author share a `deploy_at`? (`ROW_NUMBER` picks one of the tied rows arbitrarily. Add `log_id` to the window's `ORDER BY`, or use `RANK` if both rows should count as first.)
• How would you also report the share of first-time deploys per service? (Wrap with `1.0 * first_time_deploys / total_deploys`, or use AVG over the CASE expression.)
• How would you restrict to successful deploys only? (Add `WHERE status = 'success'` inside the subquery so that failed attempts do not count as someone's first.)