Basic ROWS vs RANGE Comparison with Ties

intermediate

Understanding how ROWS and RANGE handle duplicate ORDER BY values differently

-- Sample data with tied dates to demonstrate the difference

WITH daily_sales AS (

SELECT * FROM VALUES

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

('2024-01-01'::date, 'Product B', 150), -- Same date (tie)

('2024-01-01'::date, 'Product C', 120), -- Same date (tie)

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

('2024-01-03'::date, 'Product A', 180),

('2024-01-03'::date, 'Product B', 160) -- Same date (tie)

AS t(sale_date, product, amount)

)

SELECT

sale_date,

product,

amount,

-- ROWS: Counts exactly 2 physical rows (current + 1 preceding)

STRING_AGG(amount::text, ', ') OVER (

ORDER BY sale_date, product

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

) as rows_frame_values,

SUM(amount) OVER (

ORDER BY sale_date, product

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

) as rows_sum,

COUNT(*) OVER (

ORDER BY sale_date, product

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

) as rows_count,

-- RANGE: Includes all rows with same or preceding dates

STRING_AGG(amount::text, ', ') OVER (

ORDER BY sale_date

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) as range_frame_values,

SUM(amount) OVER (

ORDER BY sale_date

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) as range_sum,