Paradime | WINDOW FUNCTIONS (FIRST_VALUE / LAST_VALUE / NTH

SQL Keywords

WINDOW FUNCTIONS (FIRST_VALUE / LAST_VALUE / NTH_VALUE)

Feb 23, 2026

min read

Category: Window/Analytic Functions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

Access values from specific positions within a window frame. FIRST_VALUE returns the first value, LAST_VALUE returns the last value, and NTH_VALUE returns the value at a specific position. Essential for comparing values across rows in an ordered set.

Syntax

FIRST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

NTH_VALUE(expression, n) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

FIRST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

NTH_VALUE(expression, n) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

FIRST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

NTH_VALUE(expression, n) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    [window_frame]
)

Window Frame:

{ROWS | RANGE} BETWEEN frame_start AND frame_end

frame_start/frame_end:
- UNBOUNDED PRECEDING
- n PRECEDING
- CURRENT ROW

{ROWS | RANGE} BETWEEN frame_start AND frame_end

frame_start/frame_end:
- UNBOUNDED PRECEDING
- n PRECEDING
- CURRENT ROW

{ROWS | RANGE} BETWEEN frame_start AND frame_end

frame_start/frame_end:
- UNBOUNDED PRECEDING
- n PRECEDING
- CURRENT ROW

Platform-Specific Notes

All Platforms:

Standard SQL window functions
Identical syntax and behavior
Default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
LAST_VALUE requires explicit frame to get actual last value

Example 1: FIRST_VALUE - Compare with First in Group

All Platforms:

SELECT 
    product_category,
    product_name,
    price,
    -- Get cheapest product in category
    FIRST_VALUE(product_name) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as cheapest_product,
    FIRST_VALUE(price) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as cheapest_price,
    -- Calculate price premium over cheapest
    price - FIRST_VALUE(price) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as price_premium
FROM products
ORDER BY product_category,

SELECT 
    product_category,
    product_name,
    price,
    -- Get cheapest product in category
    FIRST_VALUE(product_name) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as cheapest_product,
    FIRST_VALUE(price) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as cheapest_price,
    -- Calculate price premium over cheapest
    price - FIRST_VALUE(price) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as price_premium
FROM products
ORDER BY product_category,

SELECT 
    product_category,
    product_name,
    price,
    -- Get cheapest product in category
    FIRST_VALUE(product_name) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as cheapest_product,
    FIRST_VALUE(price) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as cheapest_price,
    -- Calculate price premium over cheapest
    price - FIRST_VALUE(price) OVER (
        PARTITION BY product_category 
        ORDER BY price ASC
    ) as price_premium
FROM products
ORDER BY product_category,

Sample Data (products table):

product_id	product_category	product_name	price
1	Electronics	Mouse	25.00
2	Electronics	Keyboard	89.99
3	Electronics	Monitor	299.99
4	Furniture	Chair	149.99
5	Furniture	Desk	399.99
6	Furniture	Shelf	79.99

Result:

product_category	product_name	price	cheapest_product	cheapest_price	price_premium
Electronics	Mouse	25.00	Mouse	25.00	0.00
Electronics	Keyboard	89.99	Mouse	25.00	64.99
Electronics	Monitor	299.99	Mouse	25.00	274.99
Furniture	Shelf	79.99	Shelf	79.99	0.00
Furniture	Chair	149.99	Shelf	79.99	70.00
Furniture	Desk	399.99	Shelf	79.99	320.00

Example 2: LAST_VALUE with Proper Frame

All Platforms:

SELECT 
    sale_date,
    product_name,
    daily_sales,
    -- Wrong: Uses default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    LAST_VALUE(daily_sales) OVER (
        ORDER BY sale_date
    ) as incorrect_last_value,
    -- Correct: Explicitly define frame to include all rows
    LAST_VALUE(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as correct_last_value,
    -- Get last value in each week
    LAST_VALUE(daily_sales) OVER (
        PARTITION BY DATE_TRUNC('WEEK', sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as week_final_sales
FROM daily_sales
ORDER BY

SELECT 
    sale_date,
    product_name,
    daily_sales,
    -- Wrong: Uses default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    LAST_VALUE(daily_sales) OVER (
        ORDER BY sale_date
    ) as incorrect_last_value,
    -- Correct: Explicitly define frame to include all rows
    LAST_VALUE(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as correct_last_value,
    -- Get last value in each week
    LAST_VALUE(daily_sales) OVER (
        PARTITION BY DATE_TRUNC('WEEK', sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as week_final_sales
FROM daily_sales
ORDER BY

SELECT 
    sale_date,
    product_name,
    daily_sales,
    -- Wrong: Uses default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    LAST_VALUE(daily_sales) OVER (
        ORDER BY sale_date
    ) as incorrect_last_value,
    -- Correct: Explicitly define frame to include all rows
    LAST_VALUE(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as correct_last_value,
    -- Get last value in each week
    LAST_VALUE(daily_sales) OVER (
        PARTITION BY DATE_TRUNC('WEEK', sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as week_final_sales
FROM daily_sales
ORDER BY

Sample Data (daily_sales table):

sale_date	product_name	daily_sales
2024-01-15	Laptop	1000
2024-01-16	Laptop	1200
2024-01-17	Laptop	1100
2024-01-18	Laptop	1300
2024-01-19	Laptop	900

Result:

sale_date	product_name	daily_sales	incorrect_last_value	correct_last_value	week_final_sales
2024-01-15	Laptop	1000	1000	900	1100
2024-01-16	Laptop	1200	1200	900	1100
2024-01-17	Laptop	1100	1100	900	1100
2024-01-18	Laptop	1300	1300	900	900
2024-01-19	Laptop	900	900	900	900

Example 3: NTH_VALUE - Access Specific Positions

All Platforms:

SELECT 
    employee_id,
    employee_name,
    salary,
    department,
    -- Get top 3 salaries in department
    NTH_VALUE(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as highest_salary,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as second_highest,
    NTH_VALUE(salary, 3) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as third_highest,
    -- Get median (middle value)
    NTH_VALUE(salary, (COUNT(*) OVER (PARTITION BY department) + 1) / 2) OVER (
        PARTITION BY department 
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as median_salary
FROM employees
ORDER BY department, salary DESC

SELECT 
    employee_id,
    employee_name,
    salary,
    department,
    -- Get top 3 salaries in department
    NTH_VALUE(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as highest_salary,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as second_highest,
    NTH_VALUE(salary, 3) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as third_highest,
    -- Get median (middle value)
    NTH_VALUE(salary, (COUNT(*) OVER (PARTITION BY department) + 1) / 2) OVER (
        PARTITION BY department 
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as median_salary
FROM employees
ORDER BY department, salary DESC

SELECT 
    employee_id,
    employee_name,
    salary,
    department,
    -- Get top 3 salaries in department
    NTH_VALUE(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as highest_salary,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as second_highest,
    NTH_VALUE(salary, 3) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as third_highest,
    -- Get median (middle value)
    NTH_VALUE(salary, (COUNT(*) OVER (PARTITION BY department) + 1) / 2) OVER (
        PARTITION BY department 
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as median_salary
FROM employees
ORDER BY department, salary DESC

Sample Data (employees table):

employee_id	employee_name	department	salary
1	Alice	Sales	95000
2	Bob	Sales	75000
3	Carol	Sales	85000
4	David	Sales	70000
5	Emma	IT	110000
6	Frank	IT	95000
7	Grace	IT	100000

Result:

employee_id	employee_name	salary	department	highest_salary	second_highest	third_highest	median_salary
1	Alice	95000	Sales	95000	85000	75000	80000
3	Carol	85000	Sales	95000	85000	75000	80000
2	Bob	75000	Sales	95000	85000	75000	80000
4	David	70000	Sales	95000	85000	75000	80000
5	Emma	110000	IT	110000	100000	95000	100000
7	Grace	100000	IT	110000	100000	95000	100000
6	Frank	95000	IT	110000	100000	95000	100000

Example 4: Stock Price Analysis

All Platforms:

SELECT 
    trade_date,
    symbol,
    close_price,
    -- Opening price (first of period)
    FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_open,
    -- Closing price (last of period)
    LAST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_close,
    -- Calculate monthly return
    (LAST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) - FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )) * 100.0 / FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as monthly_return_pct
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY

SELECT 
    trade_date,
    symbol,
    close_price,
    -- Opening price (first of period)
    FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_open,
    -- Closing price (last of period)
    LAST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_close,
    -- Calculate monthly return
    (LAST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) - FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )) * 100.0 / FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as monthly_return_pct
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY

SELECT 
    trade_date,
    symbol,
    close_price,
    -- Opening price (first of period)
    FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_open,
    -- Closing price (last of period)
    LAST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_close,
    -- Calculate monthly return
    (LAST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) - FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )) * 100.0 / FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('MONTH', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as monthly_return_pct
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY

Sample Result:

trade_date	symbol	close_price	month_open	month_close	monthly_return_pct
2024-01-02	AAPL	185.50	185.50	190.25	2.56
2024-01-03	AAPL	187.20	185.50	190.25	2.56
...	AAPL	...	185.50	190.25	2.56
2024-01-31	AAPL	190.25	185.50	190.25	2.56

Example 5: Customer Lifetime Value Tracking

All Platforms:

SELECT 
    customer_id,
    order_date,
    order_total,
    -- First order details
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as first_order_date,
    FIRST_VALUE(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as first_order_amount,
    -- Latest order details
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_order_date,
    LAST_VALUE(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_order_amount,
    -- Days since first purchase
    DATEDIFF(
        order_date,
        FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
    ) as days_since_first_purchase
FROM orders
ORDER BY customer_id,

SELECT 
    customer_id,
    order_date,
    order_total,
    -- First order details
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as first_order_date,
    FIRST_VALUE(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as first_order_amount,
    -- Latest order details
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_order_date,
    LAST_VALUE(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_order_amount,
    -- Days since first purchase
    DATEDIFF(
        order_date,
        FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
    ) as days_since_first_purchase
FROM orders
ORDER BY customer_id,

SELECT 
    customer_id,
    order_date,
    order_total,
    -- First order details
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as first_order_date,
    FIRST_VALUE(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as first_order_amount,
    -- Latest order details
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_order_date,
    LAST_VALUE(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_order_amount,
    -- Days since first purchase
    DATEDIFF(
        order_date,
        FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
    ) as days_since_first_purchase
FROM orders
ORDER BY customer_id,

Example 6: Running vs Period Comparisons

All Platforms:

SELECT 
    sale_date,
    daily_revenue,
    -- Compare to first day
    daily_revenue - FIRST_VALUE(daily_revenue) OVER (
        ORDER BY sale_date
    ) as change_from_first_day,
    -- Compare to period start (month)
    daily_revenue - FIRST_VALUE(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('MONTH', sale_date)
        ORDER BY sale_date
    ) as change_from_month_start,
    -- Running total
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    -- Period total (month)
    SUM(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('MONTH', sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_total
FROM daily_revenue
ORDER BY

SELECT 
    sale_date,
    daily_revenue,
    -- Compare to first day
    daily_revenue - FIRST_VALUE(daily_revenue) OVER (
        ORDER BY sale_date
    ) as change_from_first_day,
    -- Compare to period start (month)
    daily_revenue - FIRST_VALUE(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('MONTH', sale_date)
        ORDER BY sale_date
    ) as change_from_month_start,
    -- Running total
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    -- Period total (month)
    SUM(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('MONTH', sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_total
FROM daily_revenue
ORDER BY

SELECT 
    sale_date,
    daily_revenue,
    -- Compare to first day
    daily_revenue - FIRST_VALUE(daily_revenue) OVER (
        ORDER BY sale_date
    ) as change_from_first_day,
    -- Compare to period start (month)
    daily_revenue - FIRST_VALUE(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('MONTH', sale_date)
        ORDER BY sale_date
    ) as change_from_month_start,
    -- Running total
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    -- Period total (month)
    SUM(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('MONTH', sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_total
FROM daily_revenue
ORDER BY

Window Frame Importance

Default Frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):

FIRST_VALUE: Works correctly (always gets first)
LAST_VALUE: Gets current row, NOT actual last! ❌
NTH_VALUE: May not reach desired position

Correct Frame for LAST_VALUE:

ROWS BETWEEN UNBOUNDED PRECEDING AND

ROWS BETWEEN UNBOUNDED PRECEDING AND

ROWS BETWEEN UNBOUNDED PRECEDING AND

Common Use Cases

FIRST_VALUE: Baseline comparisons, cohort analysis
LAST_VALUE: Period-end values, final status
NTH_VALUE: Percentile values, top-N access
Combined: Opening/closing prices, start/end comparisons

Performance Tips

Partition wisely: Smaller partitions = faster processing
Reuse window definitions: Define once, reference multiple times
Index ORDER BY columns: Improves sorting performance
Consider materialized views: For frequently used calculations
Limit frame size: UNBOUNDED can be expensive

Best Practices

Always specify frame for LAST_VALUE: Avoid default frame confusion
Use appropriate frame: ROWS vs RANGE
Handle NULLs: Consider IGNORE NULLS clause (Snowflake)
Document window logic: Explain frame and partition choices
Test edge cases: Empty partitions, single rows
Reuse window definitions: WINDOW w AS (PARTITION BY ... ORDER BY ...)

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources