DB Exercises
0
← Exercises/
CTEs•3 of 12
PrevNext
CTEsAdvanced

Customer 30-Day Repeat Repurchase Rate

Scenario

DTC e-commerce brands evaluate product stickiness by calculating what percentage of first-time buyers return to make a second purchase within 30 days of their initial order.

Task
Build a multi-stage query using CTEs: 1. `first_orders`: Find the earliest `order_date` for each `customer_id` from `orders_log`. 2. `repeat_buyers`: Count how many customers made another purchase within 30 days after their first order (`o.order_date > f.first_date AND o.order_date <= f.first_date + INTERVAL '30 days'`). 3. Return: - `total_customers`: Count of distinct customers with a first order - `repeat_customers`: Count of distinct customers who placed a repeat order within 30 days - `repeat_rate_pct`: `ROUND(100.0 * repeat_customers / total_customers, 2)`
Expected Output Format

Summary with total_customers, repeat_customers, and repeat_rate_pct within 30 days.

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 30-Day Repeat Repurchase Rate” SQL Problem

Business Scenario

DTC e-commerce brands evaluate product stickiness by calculating what percentage of first-time buyers return to make a second purchase within 30 days of their initial order.

Expected Output & Criteria

Summary with total_customers, repeat_customers, and repeat_rate_pct within 30 days.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

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

Target SQL Clauses & Operators

WITHCOUNTROUND

Want more CTEs practice?

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

View CTEs Track→