All MCQs

RANK assigns equal positions to ties.
The next rank reflects the number of preceding rows, which creates gaps.

ROW_NUMBER gives every row a different sequence value according to the specified order.
Tied sort values still receive different row numbers.

Window partitions define the peer group over which a calculation operates.
Unlike GROUP BY, window functions preserve row-level output.

CUBE returns every subtotal combination across the listed dimensions.
It can produce many groups, so designers should consider result size.

ROLLUP follows a hierarchy from detailed groups toward higher-level totals.
Column order determines which subtotal levels are generated.

GROUPING SETS allow several group combinations without separate UNION queries.
They can produce totals by region, product, and overall in one statement.

AVG computes the sum of non-NULL values divided by their non-NULL count.
Replacing NULL with zero changes the business meaning and must be deliberate.

SUM adds additive measure values across the selected group.
It is appropriate when each fact row contributes an amount to the total.

COUNT DISTINCT removes repeated customer keys before counting.
COUNT(*) would count order rows rather than unique customers.

Filtering early reduces the data that must be joined and aggregated.
It also distinguishes row predicates from conditions on aggregate groups.

HAVING applies conditions to grouped results such as SUM or COUNT.
WHERE filters individual rows before grouping occurs.

GROUP BY partitions detail rows into logical groups for aggregate functions.
It is commonly used to summarize facts by dimensions such as month or region.