Quarters Apart

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

ESPN

Problem

The api_calls log holds one row per request with its endpoint, latency and call_time, and the orders endpoint ('/api/v1/orders') has no calls at all in some quarters, which count at an average of 120.0. Over the twelve calendar quarters of the three years before 2026, oldest first, label each quarter like '2023-Q1' and show its average latency for that endpoint beside the previous quarter's average and the change between them, leaving both empty for the first quarter because nothing before the window is compared.

Latency trending up or down? The quarters have the answer.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

Worked solution and explanation

What this problem really is

Strip the dashboard framing and this is a dense time series in disguise. The orders endpoint ('/api/v1/orders') goes quiet in a few quarters, so a plain GROUP BY over api_calls only emits the quarters that actually saw traffic, and every quarter-over-quarter comparison after a dead quarter lines up against the wrong neighbor. The fix is to manufacture the full twelve-quarter spine yourself and LEFT JOIN the real averages onto it, filling each gap with the assumed 120.0 via COALESCE. Anyone can write the average. What separates candidates is noticing that the aggregate decides which rows exist, and handing that job to the spine instead.

Break it into three moves

Step 1: Build the twelve-quarter spine

A recursive CTE generates ordinals 0..11, and each ordinal becomes a (year, quarter) pair: year is 2023 + n/4 and quarter is n%4 + 1. This spine is the whole point: it guarantees a row for every quarter regardless of whether any call landed in it, which a GROUP BY over the raw table cannot promise.

Step 2: Aggregate the quarters the endpoint actually had traffic

Aggregate AVG(latency) per (year, quarter) straight from api_calls, keeping only the '/api/v1/orders' endpoint. The quarter comes from the month as (month - 1) / 3 + 1. You do not need a separate year filter: the spine owns the window, so averages for years outside it simply find no spine row to join to.

Step 3: Stitch them together and walk quarter to quarter

LEFT JOIN the averages onto the spine on year and quarter, fill the gaps with COALESCE(avg_latency, 120.0), and only then take LAG(avg_latency) OVER (ORDER BY ord) for the prior quarter. The year read out of call_time comes back as text while the spine's year is a number, so it is cast to an integer before the join. The label is built last by gluing the spine's year, '-Q' and the quarter number with ||. The first quarter has no predecessor, so its previous average and change stay null.

The solution

WITH RECURSIVE q(n) AS (
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM q WHERE n < 11
),
scaffold AS (
  SELECT 2023 + (n / 4) AS yr,
         (n % 4) + 1          AS qnum,
         n                    AS ord
  FROM q
),
actual AS (
  SELECT CAST(strftime('%Y', call_time) AS INTEGER)  AS yr,
         (strftime('%m', call_time) - 1) / 3 + 1     AS qnum,
         AVG(latency)                                AS avg_latency
  FROM api_calls
  WHERE endpoint = '/api/v1/orders'
  GROUP BY yr, qnum
),
quarterly AS (
  SELECT s.ord,
         s.yr,
         s.qnum,
         COALESCE(a.avg_latency, 120.0)  AS avg_latency
  FROM scaffold s
  LEFT JOIN actual a
    ON a.yr = s.yr AND a.qnum = s.qnum
)
SELECT yr || '-Q' || qnum                                 AS quarter,
       avg_latency,
       LAG(avg_latency) OVER (ORDER BY ord)               AS prev_avg_latency,
       avg_latency - LAG(avg_latency) OVER (ORDER BY ord) AS qoq_change
FROM quarterly
ORDER BY quarter;

Common follow-up questions

• How would you compute year-over-year change instead? (Use `LAG(avg_latency, 4) OVER (ORDER BY ord)` so each quarter compares to the same quarter one year earlier. The 4 is the step in quarter slots.)
• Why average latency instead of p95? (`AVG` gives the mean for free. A p95 in SQLite needs `NTILE(100)` or a sorted-row trick because `PERCENTILE_CONT` does not exist; interviewers often probe whether you know that gap.)
• What breaks if you skip the scaffold and an entire quarter had zero calls? (Without the spine, that quarter is missing from the aggregate, so the comparison silently jumps to the prior present quarter rather than the prior calendar quarter. The generated quarters CTE plus the `LEFT JOIN` is exactly what closes that gap, and the orders endpoint really does go dark in three quarters here.)