# Quarters Apart

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

Canonical URL: <https://datadriven.io/problems/quarters_apart>

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

The reliability team reports API latency to leadership one quarter at a time and wants to see how each quarter compares to the one before it. For the three calendar years before 2026, show every quarter in order with its average latency, the previous quarter's average, and the difference between them; if a quarter has no calls, treat its average as 120.0.

## Worked solution and explanation

### What this problem really is

Strip the dashboard framing and this is a dense-time-series problem in disguise: you must emit all twelve quarters even for the ones with no traffic, then walk each one against its predecessor. The trap is reaching for a plain GROUP BY over call_time, which only emits quarters that actually had calls, so a dead quarter silently vanishes and every comparison after it lines up against the wrong neighbor. The fix is to manufacture the twelve-quarter spine yourself and LEFT JOIN the real averages onto it. And because this runs in SQLite, DATE_TRUNC('quarter', ...) and EXTRACT(QUARTER FROM ...) do not exist, so the quarter label has to be assembled from strftime arithmetic by hand.

---

### 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 (YEAR-3) + 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 that actually had traffic

Aggregate AVG(latency) per (year, quarter) straight from api_calls, restricted to the three calendar years before the current one. Derive the quarter from the month with (month-1)/3 + 1 since SQLite has no quarter function.

#### Step 3: Stitch them together and walk quarter to quarter

LEFT JOIN the spine to the aggregates and wrap the average in COALESCE(avg_latency, 120.0) so an empty quarter takes the assumed 120.0. Then LAG over the ordinal gives the prior quarter's average, and a subtraction gives the delta; the earliest quarter has no prior, so both come back null.

---

### The solution

**Hand-built quarter label, AVG aggregate, LAG for the prior, subtract for the delta**

```sql
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,
         (CAST(strftime('%m', call_time) AS INTEGER) - 1) / 3 + 1   AS qnum,
         AVG(latency)                                               AS avg_latency
  FROM api_calls
  WHERE CAST(strftime('%Y', call_time) AS INTEGER) >= 2023
    AND CAST(strftime('%Y', call_time) AS INTEGER) < 2026
  GROUP BY yr, qnum
),
quarterly AS (
  SELECT s.ord,
         CAST(s.yr AS TEXT) || '-Q' || s.qnum  AS quarter,
         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 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 ord;
```

> **Where the cost actually lives**
>
> api_calls has 500M rows, so the only real cost is the scan: the call_time year filter should ride a btree index on call_time, otherwise you pay a full scan. Everything downstream collapses to 12 rows, so the join, the COALESCE, and the window are effectively free. The per-row quarter arithmetic is cheap.

> **Interviewers watch for**
>
> The tell is whether you build the quarter label from strftime arithmetic instead of a Postgres-only function, scope the scan with a clean year filter, and produce all twelve quarters rather than only the populated ones. Candidates who reach for DATE_TRUNC('quarter', ...) in SQLite get a syntax error and stall.

> **Common pitfall**
>
> The off-by-one in the month-to-quarter formula is the classic miss: (month-1)/3 + 1 is correct, but month/3 + 1 pushes March into Q2. Sanity-check month 1 (must give Q1) and month 12 (must give Q4) before you submit.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/quarters_apart)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.