The ROWS vs RANGE distinction is critical for accurate analytics when data contains ties. Financial firms like Goldman Sachs use ROWS for precise moving averages ("exactly 20 trading days") but RANGE for regulatory reporting ("all trades on the same day"). E-commerce sites like Amazon use ROWS for product ranking ("top 10 products by sales") but RANGE for daily summaries ("all orders placed today"). Netflix uses ROWS for episode sequences ("next 3 episodes") but RANGE for viewing sessions ("all episodes watched on same day"). The wrong choice leads to incorrect calculations: ROWS might split tied values inappropriately, while RANGE might include more data than expected. Understanding this difference prevents analytics bugs that can cost millions in wrong business decisions.

Goldman Sachs Trading Analytics - ROWS for Precise Risk Calculations

Goldman Sachs uses ROWS frames for precise risk management calculations where exact time periods and trade counts are critical for regulatory compliance and algorithmic trading.

-- Goldman Sachs-style precise risk calculations using ROWS

SELECT

trading_date,

closing_price,

daily_return,

-- Exact 20-day moving average (industry standard)

AVG(closing_price) OVER (

ORDER BY trading_date

ROWS BETWEEN 19 PRECEDING AND CURRENT ROW

) as sma_20_day,

-- Exact 20-day volatility (standard deviation of returns)

STDDEV(daily_return) OVER (

ORDER BY trading_date

ROWS BETWEEN 19 PRECEDING AND CURRENT ROW

) as volatility_20_day,

-- VaR calculation (5th percentile of last 100 trading days)

PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY daily_return) OVER (

ORDER BY trading_date

ROWS BETWEEN 99 PRECEDING AND CURRENT ROW

) as var_95_100days,

-- Bollinger Bands (exactly 20 days)

AVG(closing_price) OVER (

ORDER BY trading_date

ROWS BETWEEN 19 PRECEDING AND CURRENT ROW

) + 2 * STDDEV(closing_price) OVER (

ORDER BY trading_date

ROWS BETWEEN 19 PRECEDING AND CURRENT ROW

) as upper_bollinger,

-- Sharpe ratio (return vs risk over exact 60-day period)

AVG(daily_return) OVER (

ORDER BY trading_date

ROWS BETWEEN 59 PRECEDING AND CURRENT ROW

) / NULLIF(STDDEV(daily_return) OVER (

ORDER BY trading_date

ROWS BETWEEN 59 PRECEDING AND CURRENT ROW

), 0) as sharpe_ratio_60day,

-- Ensure exactly the right number of trading days