FIRST_VALUE() function in BigQuery

The FIRST_VALUE() function is a window function that returns the first value in a defined window. It does not reduce rows (unlike GROUP BY) and returns a value for every row in the result set.

FIRST_VALUE() Function Syntax

FIRST_VALUE(column_name)
OVER (
  PARTITION BY partition_column
  ORDER BY order_column
  [window_frame]
)

Note: The window frame is optional but becomes critical when using LAST_VALUE().

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`;

FIRST_VALUE() Explained

Example: First sales_amount per employee based on the earliest sale_date.

SQL Query

SELECT
  sale_date,
  employee,
  sales_amount,

  FIRST_VALUE(sales_amount)
    OVER (
      PARTITION BY employee
      ORDER BY sale_date
    ) AS first_sales_amount

FROM window_practice.sales_data;

Previous Next