SQL Server Window Functions: RANK, LAG, and LEAD

Comparing rows

Trend reports often compare this month's value with the previous month's value. Without window functions, such comparisons may require self joins that complicate the query.

LAG and LEAD

SELECT
    bulan,
    total_penjualan,
    LAG(total_penjualan) OVER (ORDER BY bulan) AS penjualan_bulan_lalu,
    LEAD(total_penjualan) OVER (ORDER BY bulan) AS penjualan_bulan_depan
FROM ringkasan_bulanan;

LAG retrieves a value from the preceding row in the specified order. LEAD retrieves one from the following row. With their default settings, the first row has no preceding value and the last row has no following value, so those results are NULL.

Calculate the change

SELECT
    bulan,
    total_penjualan,
    total_penjualan - LAG(total_penjualan) OVER (ORDER BY bulan) AS selisih
FROM ringkasan_bulanan;

RANK with PARTITION BY

SELECT
    wilayah,
    nama_sales,
    total_penjualan,
    RANK() OVER (PARTITION BY wilayah ORDER BY total_penjualan DESC) AS peringkat
FROM performa_sales;

PARTITION BY wilayah restarts ranking for each region, rather than ranking all regions together.

Window functions preserve row count

Unlike a GROUP BY aggregation that collapses rows, window calculations can retain the original rows while adding group-based calculations.

Exercise

Use FIRST_VALUE to display the first month's sales as a comparison value on every row of the same table.