You don't need to write SQL to verify our results — just to recognize a handful of recurring patterns. This page walks through those patterns once, using one real query from a published report, so you can read any of our verification scripts afterward without treating them as a black box.
Almost every query in this series is built from the same small set of pieces:
SELECT ... FROM ... WHERE ...
— the basic shape of any query: which columns to get
(SELECT), which table to get them from
(FROM), and which rows to keep
(WHERE).WITH name AS (...) — a named,
temporary result you can refer to later by that name, so a
long query can be broken into readable steps instead of one
giant nested statement. Think of it as defining a variable
that holds "the rows I care about" before doing anything with
them.COUNT(*)
(how many rows), AVG()/MIN()/MAX()
(average/smallest/largest value in a column),
SUM() (total). These collapse many rows down to
one number.CASE WHEN ... THEN ... ELSE ... END
— an if/else check performed per row, often combined with
SUM() to count how many rows meet a condition
(SUM(CASE WHEN x < 0.5 THEN 1 ELSE 0 END) means
"count the rows where x is below 0.5").UNION ALL — stacks the results
of several small queries into one output table, one row each.
We use this constantly to print several summary statistics
(a count, a percentage, a minimum, a maximum) as neat rows in
a single result instead of four separate outputs..open --readonly some_file.duckdb
— a DuckDB command (not part of SQL itself) that switches
which database file the following queries run against, always
opened read-only so a verification run cannot modify the data
it's checking.This is Part 1 of the verification query behind Technical Report v1.16's Table 1 — finding blocks where a reconstruction's local variance coincidentally matches the ground truth's exactly, even though the two aren't required to match:
WITH candidates AS (
SELECT sigma_gt
FROM blocks
WHERE sigma_gt > 1e-6 AND ssim_c_notta = 1.0
),
total AS (
SELECT COUNT(*) AS n_total_blocks FROM blocks WHERE sigma_gt > 1e-6
)
SELECT 'n_found' AS metric, CAST(COUNT(*) AS VARCHAR) AS value FROM candidates
UNION ALL SELECT 'pct_of_blocks', CAST(ROUND(100.0*COUNT(*)/(SELECT n_total_blocks FROM total),4) AS VARCHAR) FROM candidates
UNION ALL SELECT 'max_sigma_gt', CAST(MAX(sigma_gt) AS VARCHAR) FROM candidates
UNION ALL SELECT 'min_sigma_gt', CAST(MIN(sigma_gt) AS VARCHAR) FROM candidates;Reading it top to bottom:
WITH candidates AS (...) — defines
"candidates" as: every block whose ground-truth variance
isn't essentially zero (sigma_gt > 1e-6, ruling
out floating-point noise) and whose contrast term
equals exactly 1 (ssim_c_notta = 1.0) — the
signature, proven elsewhere in the report, of the
reconstruction's variance matching the ground truth's
exactly.total AS (...) — separately defines "total" as
just a single number: how many blocks in the whole database
have a non-negligible ground-truth variance at all. This is
the denominator for a percentage later.SELECT ... UNION ALL SELECT ... lines
each produce one labeled row: how many candidates were found
(COUNT(*)), what percentage of all blocks that is
(dividing by the "total" defined above and rounding to 4
decimal places), and the largest/smallest ground-truth
variance among the candidates found.CAST(... AS VARCHAR) just converts numbers to
text so that very different kinds of values — a count, a
percentage, a variance — can share one output column without
DuckDB complaining about mismatched types.
That's the whole pattern. Most of our verification queries are
longer only because they repeat this same shape — a
WITH clause narrowing down to the rows that matter,
followed by a handful of UNION ALL-ed summary
rows — for more conditions or more models.
The reports from DOI 1.4 onward use a handful of additional pieces. Explained once here, so the per-report walkthroughs in SQL Reference can just point back to this page instead of re-explaining them every time.
ATTACH 'path' AS alias (READ_ONLY)
— opens an additional database file into the current session
under a short name, so a query can reference several
databases at once (e.g. db_std.blocks,
db_swinir.blocks) instead of switching which
single file is open with .open. Always opened
read-only here too.COUNT(*) FILTER (WHERE ...)
— counts only the rows matching an extra condition, without
writing a whole separate query for each count. Several of
these side by side let one query report several different
conditional counts as columns of a single row.ROW_NUMBER() OVER (ORDER BY x) and
aggregate() OVER () — an aggregate
followed by OVER (...) doesn't collapse rows the
way a normal GROUP BY aggregate does; it computes
a value across a group of rows while keeping every row in the
output. ROW_NUMBER() OVER (ORDER BY x) numbers
rows 1, 2, 3… in order of x;
COUNT(*) OVER () (empty parentheses) attaches the
same total row count to every row.ROW_NUMBER() OVER), then for every possible
split point k, compute
VAR_POP(...) FILTER (WHERE rn <= k) * k + VAR_POP(...) FILTER (WHERE rn > k) * (n-k)
— the total variance if the bins were split into "before k"
and "after k" — and keep whichever k gives the
lowest total. It's the SQL equivalent of trying every possible
threshold and keeping the best one, without leaving the
database.UNPIVOT ... ON a, b, c INTO NAME metric VALUE value
— turns several columns into rows instead: a result with
columns avg/min/max
becomes three rows, each labeled by which column it came
from. Used to keep every printed value short and avoid the
DuckDB CLI's terminal-width truncation on wide result
rows.PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)
— the median (or any percentile: 0.25 for P25, 0.90 for P90,
and so on) of x across the group.CORR(x, y) — the Pearson
correlation coefficient between two columns, from −1
(perfectly opposite) to +1 (perfectly aligned).FROM blocks n JOIN blocks t
ON ...), used to compare two related rows in the same
table — most often here, matching a block's noTTA row to
its own TTA row by coordinate.