Home › Blog › Time-Series SQL for Hardware Interviews

Time-Series SQL for Hardware Interviews — the Ordering Trap

Data interviews at hardware, automotive and semiconductor companies share a shape: the table is ordered, and the order carries the meaning. Sensor readings, production runs, shipments, telemetry. Get the ordering or the frame wrong and your query still returns a number — just the wrong one, silently. These are the patterns those screens test.

The frame clause decides your answer

This is the single highest-value thing to have cold. A window function with ORDER BY and no explicit frame does not give you a moving average — it gives you a running total, because the default frame is everything from the start of the partition up to the current row.

SELECT name, hire_date, salary,
       ROUND(AVG(salary) OVER (
         ORDER BY hire_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS rolling_3
FROM employees
ORDER BY hire_date, name

Delete the ROWS BETWEEN line and the same query returns a cumulative average over all prior rows. Both run. Both look reasonable. Only one answers "what is the recent trend".

Ranking: what ties actually do

"Rank the product lines by volume" is a warm-up. The real question is the follow-up: what happens when two tie? Here are all three functions on the same data so the difference is unmissable.

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS rn
FROM employees
ORDER BY salary DESC, name

Running that against the practice dataset gives exactly this:

Read the tie at 98,000. RANK gives both position 5, then jumps to 7 — position 6 never exists. DENSE_RANK gives both 5 and continues at 6. ROW_NUMBER refuses to tie at all, which is why it needs name in the ORDER BY to stay deterministic.

Period-over-period with LAG

Every trend question reduces to comparing a row against the one before it. LAG reaches backwards in the ordered window.

SELECT order_id, order_date, total,
       LAG(total) OVER (ORDER BY order_date, order_id) AS prev_total,
       ROUND(total - LAG(total) OVER (ORDER BY order_date, order_id), 2) AS delta
FROM orders
ORDER BY order_date, order_id

The first row's prev_total is NULL — there is nothing before it. That NULL is correct and it is also the most common source of a wrong answer: filter it away with WHERE delta IS NOT NULL and your "average change" silently excludes the first period. Decide deliberately, and say which you chose.

Gaps and islands

"Which machines reported continuously?" and "how long was the longest uninterrupted run?" are the same question, and the technique is one of the most satisfying tricks in SQL: subtract a row number from the sequence value. Consecutive rows produce a constant difference; that constant becomes the group id.

WITH d AS (
  SELECT DISTINCT order_date FROM orders
),
g AS (
  SELECT order_date,
         julianday(order_date) - ROW_NUMBER() OVER (ORDER BY order_date) AS grp
  FROM d
)
SELECT MIN(order_date) AS streak_start,
       MAX(order_date) AS streak_end,
       COUNT(*) AS days
FROM g
GROUP BY grp
ORDER BY days DESC

On the practice dataset the longest island runs 2024-02-01 to 2024-02-24 — 24 consecutive days — with a 16-day run before it. The gap between them is exactly what a reliability question is asking about.

Why it works, in one sentence: dates advance by one per day and row numbers advance by one per row, so within an unbroken run their difference never changes; a missing day advances the date without advancing the count, and the difference jumps.

Rates, and why HAVING is not WHERE

Production questions are usually rates — defect rate per line, failure rate per batch. Two things go wrong. First the denominator: a rate needs the count of everything, not just the failures, which usually means conditional aggregation rather than a filter. Second, filtering the result.

SELECT category,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS failures,
       ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS failure_rate
FROM orders
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY failure_rate DESC

Two details interviewers watch for. 100.0 and not 100 — integer division would floor every rate to zero. And HAVING rather than WHERE for the minimum-volume filter, because you are filtering groups after aggregation, not rows before it. A line with two units and one defect is not a 50% defect rate worth escalating.

Practice these patterns

These are muscle memory, not trivia. Each pattern below has a company-tagged practice set that runs in the browser against real datasets, including a manufacturing track built on sensor and production tables.