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 / debug query

One-sentence task

M14-A05 - Debug - remove an accidental extra grouping column. Create one summary row per requested dimension and predict output grain.

Structured output contract

Result grain

one order count row per status after removing detail fragmentation

Exact columns

status; order_count

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 order count row per status after removing detail fragmentation.

Ordering

return order_count; remove order_id from SELECT and GROUP BY; produce one row per status instead of one row per status and order; order by status

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

9 minutes

Difficulty

4/5

Objective and concepts

Debug 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 o.status, p.category, SUM(oi.quantity * oi.unit_price)::numeric(10,2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id GROUP BY o.status, p.category ORDER BY o.status, p.category NULLS LAST;

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

Use exact semantic comparison unless the activity explicitly declares a numeric tolerance.

• 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.