DB Exercises
0
← Exercises/
Window Functions•11 of 14
PrevNext
Window FunctionsAdvancedPRO

Customer Initial vs Recent Order with FIRST_VALUE()

Scenario

CRM retention teams want to analyze order size expansion over customer lifecycles by contrasting the customer's initial purchase against subsequent orders.

Task
For each customer order in `customer_purchases`, determine the customer's first-ever order amount using `FIRST_VALUE()`. Return: 1. `order_id` 2. `customer_id` 3. `order_date` 4. `order_amount` 5. `initial_order_amount`: `FIRST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC)` Order results by `customer_id` ascending, then by `order_date` ascending.
Expected Output Format

Order history showing order amount alongside the customer first purchase value.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Customer Initial vs Recent Order with FIRST_VALUE()” SQL Problem

Business Scenario

CRM retention teams want to analyze order size expansion over customer lifecycles by contrasting the customer's initial purchase against subsequent orders.

Expected Output & Criteria

Order history showing order amount alongside the customer first purchase value.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE customer_purchases (
        order_id INT PRIMARY KEY,
        customer_id INT NOT NULL,
        order_date DATE NOT NULL,
        order_amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

FIRST_VALUEOVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Window Functions Track→