This checklist is for the moment before writing SQL. It slows the work down just enough to make the query more useful. The point is not to make reporting complicated. The point is to avoid jumping straight into syntax before the question is clear.
The checklist
Write the business question first.
Example: Which tax types have the highest total assessment amount?
Choose the table that holds the main records.
For Municipal Finance Lab reports, this is often assessments.
Decide what humans need to see.
Use readable names in SELECT, not just internal IDs.
Connect keys to keys.
JOIN IDs to IDs. Display names, categories, and statuses in the output.
Decide whether rows should be filtered.
Use WHERE for rows before grouping, such as a tax year or status.
Choose the grouping and aggregate.
GROUP BY the category. SUM, COUNT, AVG, MIN, or MAX the number.
Put the most useful rows at the top.
ORDER BY the total, count, date, or priority field that matters most.
Translate the result into plain English.
Do not stop at the table. Say what the result means and what someone might do next.
Query pattern
SELECT
tt.tax_type_name,
COUNT(*) AS assessment_count,
SUM(a.assessment_amount) AS total_assessment_amount
FROM assessments AS a
JOIN tax_types AS tt
ON a.tax_type_id = tt.tax_type_id
GROUP BY tt.tax_type_name
ORDER BY total_assessment_amount DESC;Plain-English report frame
After the query runs, write three sentences:
Question:
What did I need to know?
Result:
What did the query show?
Insight:
What should someone pay attention to next?That last step is what turns a SQL result into analyst work. A table can be correct and still not be useful. The insight is where the usefulness starts.