Scenario

Create grouped reports at the requested grain: predict row meaning before grouping, group by every selected dimension, and avoid accidental extra grouping columns.

GROUP BY and Report Grain / write query

One-sentence task

M14-A04 - Write - calculate a metric by two dimensions. Create one summary row per requested dimension and predict output grain.

Structured output contract

Result grain

one revenue row per order status and product category combination

Exact columns

status; category; 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 revenue row per order status and product category combination.

Ordering

order by status then category nulls last

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

8 minutes

Difficulty

3/5

Objective and concepts

State the requested SQL output contract for group by and report grain using source grain, columns, ordering, and edge-case evidence.

SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;

SELECT status, 'one row per order status' AS row_meaning, COUNT(*)::int AS order_count FROM orders GROUP BY status ORDER BY status;

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.

• Grouped rows have a new meaning: A grouped result is read as if each output row still represented one source order or product. Repair: State the grouped row grain before writing or interpreting aggregate values.
• Every selected dimension defines grain: An extra selected column is added to GROUP BY and fragments the report into too many rows. Repair: Keep only the requested dimensions in SELECT and GROUP BY.
• GROUP BY is not DISTINCT with math: The grouping column is treated as duplicate suppression instead of the row promise for aggregate metrics. Repair: Use GROUP BY to define one output row per dimension value, then aggregate all input rows inside each group.
• Grouped reports still need deterministic order: Rows tied on metrics or containing NULL dimensions move around across hidden variants. Repair: Order by the grouped dimensions and state NULLS FIRST or NULLS LAST when null groups can appear.

Opened hints

No hints opened yet.