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.
Scalar Functions, Types, and Casting / write query
M11-A07 - Transfer checkpoint - create normalized, rounded, and typed output columns
M11-A07 - Transfer checkpoint - create normalized, rounded, and typed output columns. Apply scalar functions and explicit casts with type-sensitive behavior.
- Result grain
- one normalized rounded typed transfer row per order item
- Exact columns
- order_item_id; normalized_product_name; line_total; quantity_numeric
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
Apply scalar functions and casts with type awareness: normalize stored text, round final numeric expressions, cast before integer division, and guard unsafe text conversion.
Scalar Functions, Types, and Casting / write query
One-sentence task
M11-A07 - Transfer checkpoint - create normalized, rounded, and typed output columns. Apply scalar functions and explicit casts with type-sensitive behavior.
Learn mode disclosure
Theory, concept names, full schema help, and progressive hints are available.
Structured output contract
- Result grain
- one normalized rounded typed transfer row per order item
- Exact columns
- order_item_id; normalized_product_name; line_total; quantity_numeric
- Source population
- Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.
- Grouping
- Do not collapse rows unless the contract explicitly asks for aggregation, distinct tuples, or set semantics.
- Ordering
- return order_item_id; order by order_item_id
- Validation
- select-only; hidden deterministic variants.
Relevant tables
Time and difficulty
- Estimated time
- 11 minutes
- Difficulty
- 3/5
Objective and concepts
Debug the requested SQL output contract for scalar functions, types, and casting 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
Casting before division changes the operation type; pg_typeof exposes the resulting PostgreSQL type family.
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 expression_text, result_family FROM (VALUES ('5 / 2', pg_typeof(5 / 2)::text), ('5::numeric / 2', pg_typeof(5::numeric / 2)::text), ('ROUND(5::numeric / 2, 2)', pg_typeof(ROUND(5::numeric / 2, 2))::text)) AS type_checks(expression_text, result_family) ORDER BY expression_text;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
- Do not invent zero rows unless the contract asks for preserved parents, missing entities, or complete periods.
- 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.
- Scalar functions preserve row grain: A text or length function is treated like an aggregate and expected to collapse rows. Repair: Apply scalar functions per row and keep the original row grain unless an aggregate is requested.
- Round after the full calculation: Source values are rounded before multiplication, producing near-boundary drift. Repair: Calculate first, then round the final expression at the requested precision.
- Cast before division: An integer division result is cast after the fractional part has already been discarded. Repair: Cast an operand to numeric before dividing.
- Not all text casts safely: Every raw text sample is cast directly and invalid or empty text aborts the query. Repair: Guard controlled text before casting and flag invalid rows explicitly.
Opened hints
No hints opened yet.