Amazon-style Company ChallengeHardVerified answerSQLite live

Customer Order Frequency Analysis

Return each customer with at least 3 orders, including customer name, email, total_orders, avg_gap_days, and max_gap_days between consecutive orders.

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

Customer Lifecycle Team is modelling purchase patterns for customers who order regularly and wants to understand the gaps between consecutive purchases.

Analyse inter-order gap days for repeat customers using LAG() and two CTEs.

Return

  • customer_name (full name)
  • email
  • total_orders
  • avg_gap_days (rounded to 1 decimal)
  • max_gap_days (rounded to 1 decimal)

Constraints

  • Measure each gap from a customer's immediately previous order
  • Exclude the first order, which has no preceding gap, from gap averages
  • Only include customers with at least three total orders
  • Return the largest maximum gap first

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER
  • customer_idINTEGER
  • order_dateDATETIME

customers

  • customer_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • emailVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The LAG() window function looks back at the previous row in a partition. LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) gives you the previous order date for each row. Subtract using julianday() to get the gap. Then aggregate gaps per customer in a second CTE.

Hint 2

CTE 1 order_gaps: SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date, julianday(order_date) - julianday(LAG(...)) AS gap_days FROM orders. CTE 2 customer_gap_stats: SELECT customer_id, COUNT(*)+1 AS total_orders, ROUND(AVG(gap_days),1), ROUND(MAX(gap_days),1) FROM order_gaps WHERE prev_order_date IS NOT NULL GROUP BY customer_id. Main: JOIN to customers, WHERE total_orders >= 3.

Hint 3

Scaffold: first derive previous_order_date and gap_days per customer; then aggregate only real gaps, restore total order count, and join customer details.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH order_gaps AS (SELECT o.customer_id, o.order_date, LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS prev_order_date, julianday(o.order_date) - julianday(LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date)) AS gap_days FROM orders o), customer_gap_stats AS (SELECT customer_id, COUNT(*) + 1 AS total_orders, ROUND(AVG(gap_days), 1) AS avg_gap_days, ROUND(MAX(gap_days), 1) AS max_gap_days FROM order_gaps WHERE prev_order_date IS NOT NULL GROUP BY customer_id) SELECT c.first_name || ' ' || c.last_name AS customer_name, c.email, cgs.total_orders, cgs.avg_gap_days, cgs.max_gap_days FROM customer_gap_stats cgs INNER JOIN customers c ON cgs.customer_id = c.customer_id WHERE cgs.total_orders >= 3 ORDER BY cgs.max_gap_days DESC;

Why this works

The first LAG row for each customer has no previous order — prev_order_date is NULL. Filtering that out in CTE 2 ensures only real gaps are averaged. COUNT(*)+1 compensates: for 3 orders there are 2 gap rows, so we add 1 to get the total order count.

Success check

2 customers — Alice Johnson (avg 13.1 days, max 21.2 days) and Bob Smith (avg 14.9 days, max 16 days)

Expected result

Use this output to verify values, aliases, ordering, and row count.

customer_nameemailtotal_ordersavg_gap_daysmax_gap_days
Alice Johnsonalice.j@email.com313.121.2
Bob Smithbob.s@email.com314.916

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.