LAG function in BigQuery

LAG() Function in BigQuery The LAG() function is a window function used to access data from a preceding row in the result set without using a self-join. It works with the OVER() clause and supports partitioning and ordering.

LAG() Function Syntax

LAG(expression, offset, default_value)
OVER (PARTITION BY column_name ORDER BY column_name)

Parameters Explained

expression → Column from which the previous value is fetched.
offset → Number of rows behind the current row. Default is 1.
default_value → Optional value returned when no previous row exists (for example, the first row).

Example: Create Dataset and Table in BigQuery

Step 1: Create Dataset

SQL Query

CREATE SCHEMA IF NOT EXISTS window_practice;

Step 2: Create Table

SQL Query

CREATE OR REPLACE TABLE window_practice.sales_data (
  sale_date DATE,
  employee STRING,
  sales_amount INT64
);

Step 3: Insert Sample Data

SQL Query

INSERT INTO window_practice.sales_data
VALUES
('2024-01-01', 'Ashish', 500),
('2024-01-02', 'Ashish', 700),
('2024-01-03', 'Ashish', 650),
('2024-01-04', 'Ashish', 800),

('2024-01-01', 'Rahul', 400),
('2024-01-02', 'Rahul', 420),
('2024-01-03', 'Rahul', 390),
('2024-01-04', 'Rahul', 500);

View Data

SQL Query

SELECT *
FROM `ashishcoder.window_practice.sales_data`;

Basic LAG() Example (Previous Day Sales)

SQL Query

SELECT
  sale_date,
  employee,
  sales_amount,

  -- Previous day's sales
  LAG(sales_amount)
    OVER (
      PARTITION BY employee
      ORDER BY sale_date
    ) AS previous_day_sales

FROM window_practice.sales_data;

How It Works

Data is grouped by employee
Rows are ordered by sale_date
LAG() fetches sales from the immediately previous row

LAG() with Offset and Default Value

SQL Query

SELECT
  sale_date,
  employee,
  sales_amount,

  -- Previous day's sales with default value
  LAG(sales_amount, 1, 0)
    OVER (
      PARTITION BY employee
      ORDER BY sale_date
    ) AS previous_day_sales

FROM window_practice.sales_data;

Previous Next