Think of window frames as defining the "view" for each calculation - like looking through a sliding window on a train. Without frame specifications, you get the default view (usually from the beginning to current position), which might not be what you want. With explicit frames, you control exactly what each row "sees" - maybe just the 3 rows around it for a moving average, or the entire partition for a total. It's like adjusting your camera lens: wide angle (UNBOUNDED) to see everything, telephoto (specific PRECEDING/FOLLOWING) to focus on nearby rows, or macro (CURRENT ROW only) for precise detail. The frame determines which rows participate in each calculation, making the difference between correct and incorrect analytics.

**

**Understanding Window Frames:**

A window frame defines the subset of rows within a partition that are used for the current row's calculation. Every window function operates on a frame, whether you specify it explicitly or use the default.

**Frame Syntax Structure:**

```sql

FUNCTION() OVER (

[PARTITION BY columns]

[ORDER BY columns]

{ROWS | RANGE} BETWEEN frame_start AND frame_end

)

```

**Frame Types: ROWS vs RANGE**

**ROWS** - Physical row counting:

- Counts actual rows regardless of values

- More predictable and commonly used

- Better performance in most cases

- Perfect for moving averages, sliding windows

**RANGE** - Logical value-based:

- Groups rows with same ORDER BY values

- Handles ties differently than ROWS

- More complex but useful for time-based analysis

- Default frame type when ORDER BY is present

```sql

-- Sample data with duplicate dates

WITH sales AS (

SELECT * FROM VALUES

('2024-01-01'::date, 100),

('2024-01-01'::date, 150), -- Same date!

('2024-01-02'::date, 200),

('2024-01-03'::date, 120)

AS t(sale_date, amount)

)

SELECT

sale_date,

amount,

-- ROWS: Counts physical rows

SUM(amount) OVER (

ORDER BY sale_date

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW