Windowing

Question 1

What are SQL window functions?

Answer 1

SQL window functions compute values based on a set of rows related to the current row (called a window or window frame).
Examples include ROW_NUMBER(), SUM(), LEAD(), and NTILE().
The OVER() clause defines the window for these functions.

Question 2

How do we find duplicates?

Answer 2

GROUP BY with COUNT() and HAVING COUNT() > 1.

Question 3

Why use windowing functions over subqueries?

Answer 3

Windowing functions provide similar results to subqueries but are more efficient.

Question 4

How do we order rows?

Answer 4

Use ORDER BY column_name to sort rows.

Question 5

what is Rank ?

Answer 5

Use RANK() or DENSE_RANK() to assign ranks.

Question 6

ROW_NUMBER() ?

Answer 6

Returns the position of the row in the result set.

Question 7

RANK()

Answer 7

Ranks rows based on a given value.

Question 8

DENSE_RANK()

Answer 8

Ranks rows without leaving gaps.

Question 9

Analytic functions:

Answer 9

EAD(), LAG(), FIRST_VALUE(): Access data from other rows in the same window.

Question 10

How do we compare with previous/next rows?

Answer 10

Use LEAD() and LAG() to access adjacent row values.

Question 11

What are these advanced grouping techniques?

Answer 11

GROUPING SETS, ROLLUP, and CUBE allow flexible aggregation.
They generate multiple levels of subtotals and grand totals.

Question 12

How Are Window Functions Configured?

Answer 12

Window functions use the OVER clause:

Window partition (PARTITION BY): Groups rows into partitions.
Window ordering (ORDER BY): Defines row order within each window.
Window frame (ROWS): Defines the window using offsets from a specified row.