# Quarter-over-Quarter Latency Trend

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

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

The SRE dashboard needs a quarter-over-quarter view of API latency from Q1 2023 through Q4 2025. For each quarter, show the average latency, the previous quarter's average, and the difference between them. If a quarter has no data, assume an average latency of 120.0.

## Worked solution and explanation

### Why this problem exists in real interviews

Reliability and capacity teams report latency on a quarterly cadence to leadership, and the interviewer wants to see whether you can build a quarter label by hand in SQLite (no DATE_TRUNC, no EXTRACT(QUARTER)) and pair each quarter with its prior using LAG.

---

### Break down the requirements

#### Step 1: Build the quarter label without DATE_TRUNC

SQLite has no quarter function. Use strftime('%Y', call_time) for the year, and ((CAST(strftime('%m', call_time) AS INTEGER) - 1) / 3 + 1) to map month 1..3 to Q1, 4..6 to Q2, etc. Concatenate with '-Q' to get '2023-Q1' style labels.

#### Step 2: Bound the scan to the requested 12 quarters

WHERE call_time >= '2023-01-01' AND call_time < '2026-01-01' restricts the scan to three calendar years (12 quarters). The half-open upper bound prevents off-by-one inclusion of January of the next year.

#### Step 3: LAG over the quarter label for the prior

Because the label sorts lexicographically (YYYY-Qn), LAG(avg_latency) OVER (ORDER BY quarter) returns the prior quarter's average. The first quarter's prev_avg_latency and qoq_change are NULL, which is exactly what the prompt requires.

---

### The solution

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

```sql
WITH quarterly AS (
  SELECT strftime('%Y', call_time) || '-Q' || ((CAST(strftime('%m', call_time) AS INTEGER) - 1) / 3 + 1) AS quarter,
         AVG(latency) AS avg_latency
  FROM api_calls
  WHERE call_time >= '2023-01-01' AND call_time < '2026-01-01'
  GROUP BY quarter
)
SELECT quarter,
       avg_latency,
       LAG(avg_latency) OVER (ORDER BY quarter) AS prev_avg_latency,
       avg_latency - LAG(avg_latency) OVER (ORDER BY quarter) AS qoq_change
FROM quarterly
ORDER BY quarter
```

> **Cost Analysis**
>
> api_calls has 500M rows. The WHERE on call_time should hit a btree index on call_time; without one the scan is the cost. The aggregate collapses to 12 rows so the window is trivial. Quarter label computation is per-row but cheap arithmetic.

> **Interviewers Watch For**
>
> Did you build the quarter label with strftime arithmetic (not a Postgres-only function), bound the scan with a half-open range, and let LAG produce NULL for the first quarter? Candidates who reach for DATE_TRUNC('quarter', ...) in SQLite get a syntax error.

> **Common Pitfall**
>
> The off-by-one in the month-to-quarter formula is real: ((m - 1) / 3 + 1) is correct, but (m / 3 + 1) puts March in Q2. Always test with month 1 (should give Q1) and month 12 (should give Q4) before submitting.

---

## Common follow-up questions

- How would you compute YoY change instead? _(Use LAG(avg_latency, 4) OVER (ORDER BY quarter) 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? _(Average is what AVG gives you for free. p95 in SQLite requires a NTILE(100) or a sorted-row trick because PERCENTILE_CONT does not exist; interviewers often probe whether you know this.)_
- What if an entire quarter had zero calls? _(That quarter is missing from the aggregate, so LAG returns the prior present quarter, not the prior calendar quarter. Calendar gaps need a generated quarters CTE and a LEFT JOIN.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/quarter_over_quarter_latency_trend)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.