Mode disclosure
All modes use one coherent workspace; only disclosure and guidance change. Learn mode keeps theory, concept names, full schema help, progressive hints, and solution review available.
Aggregate Functions and Null Semantics / write query
M13-A06 - Empty-input case - implement the null-versus-zero policy
M13-A06 - Empty-input case - implement the null-versus-zero policy. Summarize filtered input with COUNT, SUM, AVG, MIN, and MAX correctly.
- Result grain
- one aggregate row describing an empty filtered population
- Exact columns
- cancelled_line_count; raw_cancelled_revenue; displayed_cancelled_revenue
SQL editor shortcuts: Ctrl or Command Enter runs the query, Ctrl or Command Shift Enter checks it, Alt H opens the next hint, Ctrl or Command slash toggles a line comment, Ctrl or Command Shift F formats the SQL, and Escape closes transient UI.
Cursor at line 1, column 1.
Scenario
Summarize complete filtered inputs with aggregate functions: preserve one-row report grain, distinguish COUNT star from COUNT expression, and keep empty SUM policy explicit.
Aggregate Functions and Null Semantics / write query
One-sentence task
M13-A06 - Empty-input case - implement the null-versus-zero policy. Summarize filtered input with COUNT, SUM, AVG, MIN, and MAX correctly.
Learn mode disclosure
Theory, concept names, full schema help, and progressive hints are available.
Structured output contract
- Result grain
- one aggregate row describing an empty filtered population
- Exact columns
- cancelled_line_count; raw_cancelled_revenue; displayed_cancelled_revenue
- Source population
- Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.
- Grouping
- Group only at the requested output grain: one aggregate row describing an empty filtered population.
- Ordering
- No display order requirement unless Check reports one.
- Validation
- select-only; hidden deterministic variants.
Relevant tables
Time and difficulty
- Estimated time
- 10 minutes
- Difficulty
- 3/5
Objective and concepts
Debug the requested SQL output contract for aggregate functions and null semantics using source grain, columns, ordering, and edge-case evidence.
Glossary links
Concept material
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
A passing query must handle hidden nulls, ties, boundaries, and no-match rows when the contract makes them relevant.
Syntax card
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;- <requested_columns> means the exact output columns, aliases, and order from the visible contract.
- <source_population_filter> means the row population definition, not a copied visible-row value.
- <deterministic_tie_breakers> means all ordering and tie rules needed for repeatable output.
Why this works
MIN, MAX, and AVG summarize the complete product-price input; no detail product column belongs in this one-row report.
Edge cases
Hidden variants preserve nulls, ties, duplicates, boundaries, no-match rows, and alternate row order when those risks apply.
PostgreSQL note
The local engine uses PostgreSQL-compatible syntax, including explicit NULL predicates, deterministic ORDER BY clauses, and transactional grading.
Worked example
SELECT MIN(price)::numeric(10,2) AS min_price, MAX(price)::numeric(10,2) AS max_price, ROUND(AVG(price), 2) AS avg_price FROM products;Assumptions, dialect notes, and common traps
- Duplicate policy
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
- Null policy
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Tie-breakers
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Zero-related entities
- Preserve requested zero-related, no-match, or complete-period entities and make display fallback explicit.
- Numeric tolerance
- Round only at the requested final stage; hidden checks use the contract precision rather than visible formatting luck.
PostgreSQL-compatible local checks
Queries run in a local PGlite worker with PostgreSQL-style syntax and transactional grading.
- COUNT star and COUNT expression differ: A nullable column count is expected to equal the total row count even when some values are missing. Repair: Use COUNT star for all input rows and COUNT column only for known non-null expression values.
- Global aggregates stay one row: A detail column is selected beside MIN, MAX, AVG, SUM, or COUNT without grouping. Repair: Remove detail columns for a one-row report, or group deliberately when the contract asks for one row per dimension.
- AVG does not treat NULL as zero: Missing values are included as zeros in an average or distinct known-value count. Repair: Let aggregate functions ignore NULL unless the prompt explicitly defines a fallback population.
- SUM over empty input is NULL: An empty filtered population is expected to produce a raw sum of zero. Repair: Preserve the raw NULL result and add COALESCE only when the output contract asks for a displayed zero.
Opened hints
No hints opened yet.