# The Loudest Failures > Twelve months of errors. Which types showed up most? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The reliability team is running a postmortem on the error types that piled up across 2026. For each type, find how many were recorded that year, listed from most to least frequent. ## Worked solution and explanation ### What this really is Strip away the postmortem framing and this is a one table tally: bucket rows by err_type and count them, after fencing off a single year. Everyone writes the GROUP BY. What actually separates people are the two traps the data is quietly laying. First, err_tracks ships a count column, so half of candidates write SUM(count) and report occurrence totals instead of how many records exist. Second, err_type is stored with inconsistent casing (TypeError, TYPEERROR, nullpointerexception, NullPointerException), and the moment you LOWER() it to tidy things up you merge buckets the expected answer deliberately keeps apart. > **The year fence is a string compare** > > first_at is a timestamp, so pull the year with strftime('%Y', first_at) and compare it to the four character year string '2026'. BETWEEN two dates works too, but the strftime form is the shortest correct fence in SQLite and sidesteps month boundary and timezone slips. #### Step 1: Fence the rows to the target year Apply WHERE strftime('%Y', first_at) = '2026' before any aggregation. Filtering first means the count only ever sees rows inside the window, and it keeps the grouping working on the smallest possible input. #### Step 2: Bucket by the raw err_type GROUP BY err_type on the value exactly as stored. Do not fold case: the answer treats TypeError and TYPEERROR as separate categories, so any normalization here silently changes the result. #### Step 3: Count records, not the count column COUNT(*) tallies how many error rows landed in each bucket, which is the metric the question asks for. SUM(count) would answer a different question about total occurrences and is the single most common wrong turn on this shape. #### Step 4: Order most frequent first ORDER BY err_count DESC floats the noisiest categories to the top. Tied buckets share a count, and their relative order carries no meaning unless you add a secondary key. **Canonical solution** ```sql SELECT err_type, COUNT(*) AS err_count FROM err_tracks WHERE strftime('%Y', first_at) = '2026' GROUP BY err_type ORDER BY err_count DESC ``` *Filter to the year, group on the raw type, count rows, sort descending.* > **COUNT(*) versus SUM(count)** > > The count column is a magnet. It looks like it holds the number you want, but it represents per-record occurrence weights, not the number of records. Summing it answers 'how many total occurrences' rather than 'how many logged error rows'. The flat err_count of 20 in the expected preview is your tell: those are record tallies, not summed weights running into the hundreds. **COUNT(*) (correct here)** Counts how many error rows exist per type inside the year. Matches the expected output and ignores nulls in the count column entirely. **SUM(count) (different question)** Adds up the occurrence weights per type. A single null in count poisons the sum unless you COALESCE, and the totals no longer mean 'how many records'. > **Interviewers watch for the normalization reflex** > > Many candidates instinctively LOWER(err_type) because the casing looks dirty. Saying out loud 'I will keep the raw values unless you want them merged, since collapsing case would combine TypeError and TYPEERROR into one bucket' is exactly the clarifying instinct that reads as senior. Acting on the reflex without asking quietly fails the case. > **One scan, one aggregate** > > At billions of error rows this is a single sequential scan feeding a hash aggregate. The year predicate is the only thing that shrinks the input, so an index on first_at (or a date partitioned table) lets the engine skip every other year before it groups. No join, no subquery, no window: the plan stays flat and cheap. ## Common follow-up questions - Return only the single most frequent error type per service. _(Pushes from a flat aggregate to a per group top-n, usually a window function or correlated filter.)_ - Add the total occurrence count alongside the record count in the same row. _(Forces the candidate to hold COUNT(*) and SUM(count) side by side and explain how they differ.)_ - Break ties so equal counts come back in a deterministic order. _(Tests adding a secondary sort key such as earliest first_at or err_type.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_loudest_failures) - [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.