dbdbexercises
Practice Free
← Syllabus|
Easy

17. Sequential Customer Order Indexing & First-Time Flag

EasyWindow FunctionsPostgreSQL 16PRO
Table: customer_purchases
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| order_id    | varchar(16) |
| customer_id | int         |
| order_time  | timestamp   |
| amount      | numeric(10  |
+-------------+-------------+

order_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Growth analytics pipelines index customer transaction streams chronologically to flag whether an order represents a new customer acquisition (Order #1) or repeat retention.

For each transaction in `customer_purchases`, use `ROW_NUMBER()` partitioned by `customer_id` and ordered by `order_time` ascending to calculate `order_sequence`. Include a boolean/text flag `is_first_order`: - `'YES'` if `order_sequence = 1` - `'NO'` otherwise Return: 1. `order_id` 2. `customer_id` 3. `order_time` 4. `amount` 5. `order_sequence` 6. `is_first_order` Order results by `customer_id` ascending, then by `order_sequence` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Customer orders indexed chronologically with first-time acquisition flag.

Constraints:
  • Query must utilize clause(s): ROW_NUMBEROVERPARTITION BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Sequential Customer Order Indexing & First-Time Flag” SQL Problem

Business Scenario

Growth analytics pipelines index customer transaction streams chronologically to flag whether an order represents a new customer acquisition (Order #1) or repeat retention.

Expected Output & Criteria

Customer orders indexed chronologically with first-time acquisition flag.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE customer_purchases (
        order_id VARCHAR(16) PRIMARY KEY,
        customer_id INT NOT NULL,
        order_time TIMESTAMP NOT NULL,
        amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

ROW_NUMBEROVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 18 exercises and syntax guides.

View Window Functions Track