Daily Error Count Change

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

City and County of San Francisco

Problem

Each row of err_tracks is a single error event stamped with its first_at time, and a day with no errors leaves no rows at all. For every day on record, earliest to latest, show how many errors landed that day, how many landed on the previous day that had any, and how much the count moved.

Errors, trending up or down?

Tables

err_tracks

Columns: err_id INTEGER, err_type TEXT, message TEXT, svc_name TEXT, severity TEXT, count INTEGER, first_at TEXT

Expected output

Worked solution and explanation

The previous day is the previous row

This is a gaps problem dressed up as a trend chart. Rolling err_tracks up to one row per day is the easy half. What separates candidates is what 'the previous day' means when quiet days leave no rows at all. Write it as calendar date minus one, a self-join on error_date - 1, and every day after a gap comes back with a NULL prev_count. On this data that is 01-13, 01-22, 02-01 and dozens more, so the chart shows breaks that are nothing but days without errors.

Build it in two passes

Step 1: Roll up to one row per day

Group err_tracks by DATE(first_at) and count rows with COUNT(*). Each row is one error event. The count column is not a daily total, and 18 of its values are NULL, so SUM(count) answers a different question and silently ignores those events.

Step 2: Reach back to the prior recorded day

Over the daily rows, LAG(error_count) OVER (ORDER BY error_date) returns whichever day precedes this one in the data. Day one has nothing before it, so its prev_count is NULL.

Step 3: Subtract for the change

error_count - prev_count is the movement. NULL minus anything stays NULL, so day one's day_over_day_change is blank without any special case.

Step 4: Project and order

Return error_date, error_count, prev_count and day_over_day_change, ordered by error_date ascending so the chart reads left to right.

The solution

WITH daily_errors AS (
    SELECT
        DATE(first_at) AS error_date,
        COUNT(*) AS error_count
    FROM err_tracks
    GROUP BY DATE(first_at)
),
with_lag AS (
    SELECT
        error_date,
        error_count,
        LAG(error_count) OVER (ORDER BY error_date) AS prev_count
    FROM daily_errors
)
SELECT
    error_date,
    error_count,
    prev_count,
    error_count - prev_count AS day_over_day_change
FROM with_lag
ORDER BY error_date

Calendar date minus one

A LEFT JOIN of daily_errors to itself on prev.error_date = d.error_date - INTERVAL '1 day' matches only when the literal prior date exists. On 01-13 it looks for 01-12, finds nothing, and returns NULL.

Previous row in date order

LAG(error_count) OVER (ORDER BY error_date) takes the row before, which on 01-13 is 01-10. Every day after the first gets a comparison, which is what the chart needs.

Common follow-up questions

• How would you also flag days where the count more than doubled, using a percentage-change threshold? (Tests percentage change with a `NULLIF` guard against dividing by a missing prior count.)
• If the chart must show quiet days as zero, how would you fill the gaps so each appears with `error_count = 0`? (Tests a generated calendar `LEFT JOIN`ed to the daily rollup, and how that changes what the previous day means.)
• How would you replace the day-over-day change with a 7-day rolling average instead? (Tests `AVG(error_count) OVER (ORDER BY error_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)` and rows versus calendar days in the frame.)