A claim about Overwater

The top fifth of customers accounts for most of the revenue.

Quintile 1 accounts for between 55% and 75% of revenue. The 90-day repeat rate lands near 30%.

holds on the current window

as of moments ago

Revenue by customer quintile

WITH revenue AS (
  SELECT customer_id, SUM(total_usd) AS spent, COUNT(*) AS orders
  FROM orders GROUP BY customer_id
),
ranked AS (
  SELECT spent, orders, NTILE(5) OVER (ORDER BY spent DESC) AS quintile
  FROM revenue
)
SELECT quintile,
       COUNT(*)                                                     AS customers,
       ROUND(SUM(spent))                                            AS revenue,
       ROUND(100.0 * SUM(spent) / (SELECT SUM(spent) FROM revenue), 1) AS revenue_share_pct,
       ROUND(AVG(orders), 2)                                        AS orders_per_customer
FROM ranked
GROUP BY quintile
ORDER BY quintile;

View data table

quintile	customers	revenue	revenue_share_pct	orders_per_customer
1	1867	1.27212e+06	69.1	5.02
2	1867	306198	16.6	1.85
3	1866	143587	7.8	1.33
4	1866	79396	4.3	1.1
5	1866	39606	2.2	1.01

holds on the current window

Repeat orders within 90 days

WITH firsts AS (
  SELECT customer_id, MIN(ordered_at) AS first_at FROM orders GROUP BY customer_id
),
observed AS (
  SELECT f.customer_id, f.first_at,
         EXISTS (SELECT 1 FROM orders o
                 WHERE o.customer_id = f.customer_id
                   AND o.ordered_at > f.first_at
                   AND o.ordered_at <= datetime(f.first_at, '+90 days')) AS repeated
  FROM firsts f
  WHERE datetime(f.first_at, '+90 days') <= (SELECT MAX(ordered_at) FROM orders)
)
SELECT COUNT(*) AS customers, ROUND(100.0 * SUM(repeated) / COUNT(*), 1) AS repeat_90d_pct
FROM observed;

customers: 8680
repeat_90d_pct: 21

holds on the current window

Real stores have whales. Overwater’s customers come from a mixture of three archetypes, drawn once per customer and never written to the schema. Gift buyers order once. Casual buyers come back now and then. Collectors keep ordering because their plants die and their collections grow. Their visit rate, dropout, spend and basket size are correlated, so the people who buy often also spend more per order. The result is a revenue distribution that looks like a real one when you cut it into fifths.

What you should see

The first quintile holds well over half the revenue with several orders per customer. The fifth quintile accounts for a few percent, one order each. The repeat query returns a rate near 30 percent. There is no segment column anywhere; the segments are in the behavior.

Run it yourself

SQLite one file, ready to query CSV tar.gz, one file per table