SQL Server interview questions covering aggregate functions, group by & window functions.
Open a question for the full answer, code and interview guidance.
Aggregate functions calculate a single result from a set of rows. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. They are frequently combined with GROUP BY to calculate values for each group, such as total sales per customer or product count per category. Most aggregate functions ignore NULL values, while COUNT(*) counts rows regardless of NULL values in individual columns.
COUNT(*) counts all rows in the result. COUNT(column) counts rows where the specified expression is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. This distinction is important when nullable columns are involved. If a table contains 100 rows but 20 Email values are NULL, COUNT(*) returns 100 while COUNT(Email) returns 80.
SUM calculates the total of a numeric expression over qualifying rows. It can be used over an entire result set or per group. SUM ignores NULL input values. The returned data type depends on the input expression, so developers should pay attention to numeric range and precision when aggregating large financial or transactional datasets.
AVG returns the arithmetic average of non-NULL values in an expression. NULL values are ignored rather than treated as zero. The return type depends on the input type. When exact precision is important, particularly for financial calculations, explicitly using an appropriate DECIMAL type can make the intended precision clear.
MIN returns the smallest value and MAX returns the largest value in the qualifying set. They work with numeric, date/time, and many comparable character data types. They are commonly used for lowest/highest prices, earliest/latest dates, minimum/maximum identifiers, and grouped reporting.
GROUP BY divides rows into groups based on one or more expressions so aggregate calculations can be performed for each group. In a grouped query, selected expressions normally must either be part of the GROUP BY or be aggregated. GROUP BY is widely used for reports, dashboards, analytics, and summary queries.
Yes. GROUP BY can contain multiple expressions. Each unique combination of the grouping values forms a separate group. For example, grouping orders by year and month produces one aggregate row for each year/month combination. Multiple-column grouping is common in dashboard and reporting queries.
HAVING filters groups after aggregation, while WHERE filters individual rows before grouping. HAVING is therefore used when the condition depends on an aggregate result, such as categories containing more than 10 products or customers whose total purchases exceed a specified amount.
WHERE filters source rows before GROUP BY and aggregate calculation. HAVING filters the grouped result after aggregation. Conditions that do not depend on aggregates should generally be placed in WHERE so unnecessary rows can be eliminated earlier. Aggregate conditions belong in HAVING.
Conditional aggregation uses expressions such as CASE inside aggregate functions to calculate multiple conditional metrics in a single grouped query. It is particularly useful for dashboards—for example, calculating total cases, CPO cases, non-CPO cases, open cases, and closed cases without issuing separate queries for each metric.
A window function performs a calculation across a set of rows related to the current row while normally preserving each individual row in the result. This differs from GROUP BY, which collapses multiple input rows into one row per group. Window functions use the OVER clause and include ranking functions, aggregate window functions, and analytic functions such as LAG and LEAD.
OVER defines the window of rows used by a window function. It can contain PARTITION BY, ORDER BY, and—where supported—a window frame such as ROWS BETWEEN. PARTITION BY separates rows into independent groups without collapsing them. ORDER BY defines logical ordering inside each partition. The frame can further restrict which ordered rows participate in a calculation.
ROW_NUMBER assigns a unique sequential integer to rows within each window partition according to the specified ORDER BY. It is useful for ranking, pagination, selecting one row per group, and identifying duplicates. If the ORDER BY values are not unique, the ordering among ties is not guaranteed to be deterministic; add a unique tiebreaker when deterministic results are required.
All three assign ranking values, but ties are handled differently. ROW_NUMBER always assigns a unique sequential number. RANK gives equal values the same rank and leaves gaps after ties. DENSE_RANK also gives equal values the same rank but does not leave gaps. For values 100, 100, and 90 ordered descending, ROW_NUMBER may produce 1,2,3; RANK produces 1,1,3; and DENSE_RANK produces 1,1,2.
A common solution is to calculate ROW_NUMBER or RANK partitioned by the grouping key and then filter the generated ranking in an outer query or CTE. For example, to return the three most expensive products from every category, partition by CategoryId and order each partition by Price descending.
LAG accesses a value from a preceding row in the window, while LEAD accesses a value from a following row. They are useful for comparing adjacent records without writing a self join. Typical uses include comparing current sales with previous-day sales, measuring changes between events, and examining previous or next status values.
FIRST_VALUE returns the first value in the defined window frame, while LAST_VALUE returns the last value in the frame. A frequent interview trap is LAST_VALUE: with an ORDER BY, the default frame may end at the current row rather than the final row of the partition. To obtain the last value across the entire partition, explicitly define an appropriate frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
A running total can be calculated with SUM as a window function and an ordered ROWS frame. Each row then contains the cumulative sum from the start of the partition through the current row. An explicit ROWS frame is often preferable because it makes the intended behavior clear, particularly when multiple rows share the same ORDER BY value.
ROW_NUMBER can partition rows by the columns that define business duplication and assign a sequence within each duplicate group. Rows with a row number greater than 1 are duplicate occurrences according to that definition. Before deleting duplicates, the business key and retention rule must be verified carefully. A deterministic ORDER BY should decide which row is kept.
GROUP BY reduces rows into one result row per group, while window functions usually preserve the original row-level detail and add calculations across related rows. Use GROUP BY when the required output is a summary such as one row per category. Use a window function when each product or transaction must remain visible while also showing information such as category average, ranking, running total, or previous value. Both approaches can be combined in advanced reporting queries, but they solve different logical problems.