Fill With Group Mode — Solution & ExplanationProblems / Fill With Group Mode / EditorialApproach

Frequency table. Count non-NULL (category, color) pairs — the electorate for each category's mode.

Deterministic arg-max. ROW_NUMBER() OVER (PARTITION BY category ORDER BY cnt DESC, color), keep rn = 1. The compound sort key is the whole point: the first key expresses "most frequent", the second encodes the tie-break *as policy*.

Impute with COALESCE. LEFT JOIN modes by category; COALESCE(color, mode_color) fills only the gaps.

Key InsightStatistical imputation in SQL is a derive-then-join pattern (compute the per-group statistic, join it under the holes) — but the deeper lesson is about *determinism as a requirement*. Built-in mode() aggregates are allowed to return either winner of a tie, which means the same query can produce different results across runs, engines, or even row orders — poison for tested pipelines. Building the mode from COUNT + ROW_NUMBER with a total ordering costs three lines and buys reproducibility; the same construction gives deterministic "argmax" for any statistic an engine won't pin down.Hints

The mode is an arg-max over a frequency table: count (category, color) pairs, then take the top row per category. WHERE color IS NOT NULL keeps the missing values from voting for themselves.

Determinism lives in the window's ORDER BY: cnt DESC settles frequency, and the secondary key `color` settles ties alphabetically. Without the second key, ties are decided by whatever order the engine feels like today.

Join the modes back at the category level and COALESCE — original colors pass through untouched; only NULLs pick up the imputed value.

The data plants a tie on purpose (two colors with equal counts in one category); check it lands on the alphabetically-first one.

Solve Fill With Group Mode yourself →