Grouping defines the unit of each output row. Aggregates must match the question’s intended grain and avoid duplicate contributions.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Sum line-item amounts per order before summarizing per customer. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Write the required total
Define whether revenue is per item, order or customer.
Step 2: Inspect intermediate rows
Count rows before summation, especially across multiple one-to-many relationships.
Step 3: Reduce each child relation
Aggregate at its own grain before combining where necessary.
Worked scenario
Sum line-item amounts per order before summarizing per customer.
One order has two items totaling 30 and two payment records. Joining both child tables directly produces four combinations and can sum item amounts to 60. An item subtotal grouped by order keeps the intended 30 before another relation is combined.
Common mistake
Joining another one-to-many table can multiply amounts before SUM.
Verify the behavior
Use hand-calculated totals and inspect every intermediate result’s grain.
Interview exercise
Validate a revenue query.
Answer and reasoning
Use small known data with multiple items and payments, checking each intermediate grain and expected total.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.