dbdbexercises
Practice Free
← Syllabus|
Hard

15. Average Days Elapsed Between First and Second Order

HardCTEsPostgreSQL 16PRO
Table: customer_orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  |
| customer_id | int  |
| order_date  | date |
+-------------+------+

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

E-commerce retention marketers measure the velocity of repeat buyer conversion by calculating the exact calendar days between customer Order #1 and Order #2.

Write a query using CTEs to compute the repeat purchase latency for each customer who made at least 2 orders: 1. In a CTE `ranked_orders`, use `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC)` to rank orders chronologically. 2. In a second CTE `paired_orders`, join order 1 to order 2 for each customer and calculate the day difference (`o2.order_date - o1.order_date`). Return: 1. `customer_id` 2. `first_order_date` 3. `second_order_date` 4. `days_to_second_order` Order results by `days_to_second_order` ascending, then by `customer_id` 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 latency in days between first and second e-commerce purchases.

Constraints:
  • Query must utilize clause(s): WITHROW_NUMBERAVG
  • 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 “Average Days Elapsed Between First and Second Order” SQL Problem

Business Scenario

E-commerce retention marketers measure the velocity of repeat buyer conversion by calculating the exact calendar days between customer Order #1 and Order #2.

Expected Output & Criteria

Customer latency in days between first and second e-commerce purchases.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE customer_orders (
        order_id INT PRIMARY KEY,
        customer_id INT NOT NULL,
        order_date DATE NOT NULL
      );

Target SQL Clauses & Operators

WITHROW_NUMBERAVG

Want more CTEs practice?

Explore the full syllabus of 16 exercises and syntax guides.

View CTEs Track