Drill 9 of 12 in E-commerce questions
0

Interview Question Bank · First order from full history · FILTER split · COALESCE

9. First-order vs repeat revenue

HardE-commerceQuestion #93

Concept: First order from full history · FILTER split · COALESCEshow

To split revenue into first purchases and repeat purchases, find each customer's first order over their whole history, then compare every order in the reporting window against it. If the first-order lookup only sees the window, everyone who bought before it looks brand new. Define 'first' using the same rules as the report (here: completed orders only).

Example (different data):

WITH f AS (
  SELECT member_id, MIN(visited_on) AS first_visit
  FROM gym_visits GROUP BY member_id
)
SELECT v.visited_on,
  COUNT(*) FILTER (WHERE v.visited_on = f.first_visit) AS first_visits
FROM gym_visits v
JOIN f ON f.member_id = v.member_id
WHERE v.visited_on >= '2025-06-01'
GROUP BY v.visited_on;

Your task

Quillmere's CFO asks how much of Q1 2025 revenue came from customers buying for the first time versus customers coming back.

A customer's first order is their earliest completed order ever (2024 orders count; no customer has two completed orders on the same day). For completed orders placed in January-March 2025, return month (YYYY-MM), new_customer_revenue (revenue of orders that are the customer's first order), returning_customer_revenue (revenue of their later orders) and returning_pct (returning revenue as a percentage of the month's revenue, rounded to 1 decimal). Revenue is quantity * unit_price. Show 0, not NULL, when a month has no first orders. Sort by month.

Starting Postgres in your browser…

Checking your access…