HardCTEs High Frequency

Customer 30-Day Repeat Repurchase Rate

Frequently Asked By:AmazonShopifyUber

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

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

Why Technical Interviewers Ask This

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.

Common Pitfalls & Interview Traps

Forgetting required sorting and deterministic tie-breaking.

Not accounting for NULL or zero-value denominators during calculations.

Using inefficient quadratic subqueries instead of single-pass ctes constructs.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH first_purchases AS (
        SELECT 
          customer_id, 
          MIN(order_date) AS first_date
        FROM orders_log
        GROUP BY customer_id
      ),
      repeats AS (
        SELECT DISTINCT f.customer_id
        FROM first_purchases f
        JOIN orders_log o ON f.customer_id = o.customer_id
        WHERE o.order_date > f.first_date 
          AND o.order_date <= f.first_date + 30
      )
      SELECT 
        COUNT(f.customer_id) AS total_customers,
        COUNT(r.customer_id) AS repeat_customers,
        ROUND(100.0 * COUNT(r.customer_id) / COUNT(f.customer_id), 2) AS repeat_rate_pct
      FROM first_purchases f
      LEFT JOIN repeats r ON f.customer_id = r.customer_id;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in CTEs. It meets the target criteria: Summary with total_customers, repeat_customers, and repeat_rate_pct within 30 days.

Edge Cases to Verbally Clarify with Your Interviewer

The dataset contains zero matching records (must return valid empty result without crashing).

Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).

Foreign keys or secondary attributes have NULL values.

Frequently Asked Follow-Up Questions

What makes this question a favorite among Amazon and Shopify interviewers?

Interviewers use this problem to test whether candidates write defensive, scalable CTEs queries that run efficiently on multi-million row production databases without excessive memory usage.

How does the query optimizer execute this query?

Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.