Q by Q

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

Problem

The release engineering team is charting deployment velocity from deploy logs whose deploy times are stored as text timestamps, and wants a steady per-week rate rather than raw counts. Treating each quarter as thirteen weeks, show each quarter in YYYY-QN format alongside its average deploys per week as a decimal, busiest quarters first and equal rates in chronological order.

Thirteen weeks. This is how the team spent them.

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

The disguise

This reads like a reporting ask, but underneath it is two problems stacked. First, turn a raw text deploy_at timestamp into the quarter it belongs to and count per bucket. Second, and this is what separates people, turn that count into an average per-week rate by dividing by the thirteen weeks in a quarter. Everyone can write GROUP BY and COUNT(*); the tell is whether you cast the count to REAL before that division, or let integer division quietly floor every rate.

Why a plain count over 13 lies

COUNT(*) is an integer and 13 is an integer, so COUNT(*) / 13 is integer division: it throws away the remainder. A busy quarter with 41 deploys reports 3 instead of 3.15, and a quiet quarter with 5 deploys reports 0 instead of 0.38. Every low-volume quarter collapses to the same 0, the chart flatlines, and ORDER BY the rate can no longer tell the quarters apart. There is also no quarter directive in strftime, so the YYYY-QN bucket has to be built by hand.

Step 1: Build the quarter bucket from the timestamp

Pull the year with strftime('%Y', deploy_at). For the quarter, read the month with strftime('%m', deploy_at) and map it with plain arithmetic: (month - 1) / 3 + 1 sends months 1 to 3 to 1, 4 to 6 to 2, and so on, with the integer division here doing exactly what you want. Concatenate into the label, so August 2026 becomes 2026-Q3.

Step 2: Cast the count to REAL, then divide

This is the load-bearing line: CAST(COUNT(*) AS REAL) / 13. Casting the count to REAL makes the division floating-point, so 41 over 13 stays 3.15 instead of flooring to 3. Cast either operand and the whole expression goes fractional; leave both as integers and the rate silently truncates.

Step 3: Group, and order by the rate

GROUP BY the derived quarter label collapses every deploy into its bucket. Count every row: the metric is total deploy attempts, so no status or env_name filter belongs here even though both arrive in messy casing. Then ORDER BY deploys_per_week DESC puts the busiest quarters on top, with , quarter as the tiebreak so equal-rate quarters fall in chronological order.

The solution

SELECT strftime('%Y', deploy_at) || '-Q' || ((strftime('%m', deploy_at) - 1) / 3 + 1) AS quarter,
       CAST(COUNT(*) AS REAL) / 13 AS deploys_per_week
FROM deploy_logs
GROUP BY strftime('%Y', deploy_at) || '-Q' || ((strftime('%m', deploy_at) - 1) / 3 + 1)
ORDER BY deploys_per_week DESC, quarter

Common follow-up questions

• The chart needs to read left to right in time. How would you order the output chronologically instead of by rate? (Tests ordering on the derived `YYYY-QN` label instead of the aggregate.)
• Quarters are not all exactly thirteen weeks. How would you divide by the real number of days in each quarter instead? (Probes whether the fixed 13-week divisor is defensible.)
• How would you extend this to show quarter-over-quarter growth in the rate rather than the raw rate? (Tests window functions over the quarter buckets.)