Ship It or Skip It

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Vesta Innovations

Problem

How many deploys did we ship on each calendar day, going by the timestamps in our deploy_logs? List the days from earliest to latest.

The calendar doesn't lie. How aggressive is this team, really?

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

This is calendar bucketing wearing a deployment-velocity costume. deploy_at is stored to the second, so the day is not a column you can read: it is a value you derive before you count. Anyone can write COUNT(*). The thing that separates candidates is noticing the time of day riding along in every timestamp. Group on the raw deploy_at and you get one row per deploy instant, almost every count a 1, instead of the 183 days where several dates carry two deploys.

Break down the requirements

Step 1: Derive the day from deploy_at

deploy_at is text like '2026-02-02 01:11:00'. The day you are asked about is only its date part, so compute DATE(deploy_at) and make that the grouping key. Two deploys at 01:11 and 22:40 on the same date now land in one bucket.

Step 2: Count every row in the bucket

COUNT(*) is the right measure: every logged deploy counts, including a second push of the same service that day, so there is nothing to make unique first.

Step 3: Sort by the derived day

Order by the deploy_date alias, oldest first. Each day appears exactly once, so the date alone fixes the sequence and no tiebreaker is owed.

The solution

SELECT DATE(deploy_at) AS deploy_date,
       COUNT(*) AS deploy_count
FROM deploy_logs
GROUP BY DATE(deploy_at)
ORDER BY deploy_date

Grouping on the stored value

GROUP BY deploy_at makes one group per recorded second. On this table that is roughly one row per deploy, each with a count of 1, and every day with two deploys is split in half.

Grouping on the derived day

GROUP BY DATE(deploy_at) drops the time of day first, so every deploy on a date shares one key and each calendar day comes back once with its true total.

Common follow-up questions

• How would you include days that had zero deployments? (Tests a calendar table or `generate_series` joined back with an outer join.)
• How would you show the running total of deployments over time? (Tests `SUM(COUNT(*)) OVER (ORDER BY deploy_date)` layered on the daily count.)
• What changes if `deploy_at` is stored as a Unix epoch integer instead of a timestamp? (Tests `TO_TIMESTAMP` conversion before the date is derived.)