From Requirement to Query
A reporting query is fundamentally different from a transactional query: it reads a large volume of historical data, joins across several tables, and aggregates or ranks the results into a shape a stakeholder can consume, rather than touching a handful of rows to serve a single request. The discipline of building one well starts before any SQL is typed -- clarifying the exact grain of the report (one row per order? per customer per month?), the required filters, and how ties or missing data should be handled -- because getting the grain wrong produces a report that looks plausible but silently double-counts or drops data.
Cricket analogy: A statistician compiling a season summary first decides the grain -- per-match figures or per-innings figures -- before pulling any numbers, since Sachin Tendulkar's career average changes completely depending on whether not-outs are included.
Joining and Filtering the Base Data Set
The base of most reporting queries is a chain of joins from a fact-like table (Orders, Transactions) out to its dimension tables (Customers, Products, Territories), using LEFT JOIN when a related row might legitimately be missing (an order with no assigned salesperson) and INNER JOIN when the relationship is mandatory. Filters that scope the report -- a date range, a status, a region -- belong in the WHERE clause when applied to the base table, but must move into the ON clause of a LEFT JOIN when they should only restrict which related rows are matched without eliminating the base row entirely; putting them in WHERE by mistake silently turns a LEFT JOIN back into an INNER JOIN.
Cricket analogy: Joining Orders to Customers is like a scorecard joining ball-by-ball data to player profiles -- if a substitute fielder's profile is missing, you still want the dismissal recorded, which is why LEFT JOIN behaves like keeping the delivery on the scorecard regardless.
Aggregating and Ranking with Window Functions
Once the joined data set is correctly filtered, GROUP BY with SUM, COUNT, and AVG produces the headline totals, but many reports also need per-group ranking or running totals that GROUP BY alone cannot express -- that's where window functions come in. ROW_NUMBER() OVER (PARTITION BY Region ORDER BY TotalSales DESC) ranks each row within its region without collapsing the detail rows the way GROUP BY would, and SUM(TotalSales) OVER (PARTITION BY Region ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) produces a running total per region. The key distinction to internalize is that GROUP BY reduces the row count, while window functions preserve every row and simply attach a calculated value alongside it.
Cricket analogy: ROW_NUMBER() OVER (PARTITION BY Team ORDER BY RunsScored DESC) is like ranking each batter's score within their own team's innings without collapsing the full list of batters into a single team total, the way GROUP BY would.
WITH RegionalSales AS (
SELECT
c.Region,
c.CustomerName,
o.OrderDate,
o.TotalAmount,
SUM(o.TotalAmount) OVER (
PARTITION BY c.Region
ORDER BY o.OrderDate
ROWS UNBOUNDED PRECEDING
) AS RunningRegionTotal,
ROW_NUMBER() OVER (
PARTITION BY c.Region
ORDER BY o.TotalAmount DESC
) AS RankInRegion
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c
ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
)
SELECT Region, CustomerName, OrderDate, TotalAmount, RunningRegionTotal
FROM RegionalSales
WHERE RankInRegion <= 5
ORDER BY Region, RankInRegion;Applying a filter like WHERE o.SalesRepId = 42 after a LEFT JOIN to a Salespeople table silently converts the LEFT JOIN into an INNER JOIN, because NULL never satisfies an equality comparison. If you need to filter on the joined table's column while still keeping unmatched base rows, move the condition into the join's ON clause instead.
Pivoting for Stakeholder-Ready Output
Business stakeholders frequently want a report shaped like a spreadsheet -- one row per category with one column per month -- which T-SQL's PIVOT operator or a conditional-aggregation pattern using CASE WHEN inside SUM() can both produce. PIVOT requires knowing the column values in advance (or building the column list dynamically with dynamic SQL), while the CASE WHEN approach is more verbose but easier to extend and debug, and does not require a fixed, hard-coded list of pivoted values when combined with GROUP BY. For most reporting queries destined for a BI tool like Power BI or Tableau, it is actually better practice to leave the data in tall (unpivoted) form and let the BI tool's own pivot/matrix visual handle the reshaping, since that keeps the T-SQL layer simpler and reusable across multiple reports.
Cricket analogy: Pivoting a tall table of ball-by-ball data into an over-by-over spreadsheet is like a scorer converting raw delivery data into the familiar six-columns-per-over scorecard format fans expect to read.
Conditional aggregation is often preferable to PIVOT in production reporting code: SUM(CASE WHEN MONTH(OrderDate) = 1 THEN TotalAmount ELSE 0 END) AS Jan works with a normal GROUP BY, needs no fixed list of pivot values baked into the query syntax, and is easier for another developer to read and extend.
- Define the report's grain (one row per what?) before writing any SQL.
- Use LEFT JOIN when a related row may legitimately be missing; keep join-scoping filters in the ON clause.
- A WHERE filter on a LEFT-JOINed table's column silently turns it into an INNER JOIN.
- GROUP BY reduces row count; window functions like ROW_NUMBER() and SUM() OVER() preserve every row.
- PARTITION BY resets a window function's calculation for each group, similar to GROUP BY but without collapsing rows.
- PIVOT and conditional aggregation (CASE WHEN inside SUM) both reshape tall data into a spreadsheet layout.
- Consider leaving data tall for BI tools like Power BI, which can pivot in the visualization layer instead.
Practice what you learned
1. What is the first thing to define before writing a reporting query?
2. What happens if you put a filter on a LEFT-JOINed table's column in the WHERE clause instead of the ON clause?
3. How does a window function differ from GROUP BY?
4. What is a key limitation of the PIVOT operator compared to conditional aggregation?
5. Why might you leave reporting data in tall (unpivoted) form for a BI tool like Power BI?
Was this page helpful?
You May Also Like
T-SQL Best Practices
Practical, production-tested guidelines for writing T-SQL that is correct, performant, and maintainable in SQL Server.
T-SQL Quick Reference
A fast-lookup reference covering core T-SQL syntax: data types, joins, window functions, string/date functions, and common statements.
SQL Server Interview Questions
Commonly asked SQL Server and T-SQL interview questions with the reasoning behind strong answers, from indexing to isolation levels.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics