Back to all work

SQL study, 2026

Segmenting online store customers in SQL

Customers with four or more orders are a third of buyers and 54.4% of revenue.

ecommerce-sql / query 2.4
CASE
  WHEN order_count = 1 THEN 'One-time buyer'
  WHEN order_count BETWEEN 2 AND 3 THEN 'Repeat buyer'
  ELSE 'Loyal customer (4+)'
END AS segment

Share of buyersShare of revenue

One-time19.0%7.5%Repeat (2 to 3)47.6%38.1%Loyal (4+)33.3%54.4%
My role
Sole author
Context
PostgreSQL, six-table retail schema
Timeline
620 orders, Jan 2024 to Jun 2025
Tools
PostgreSQL, window functions, cohorts

A six-table retail schema with 620 orders and $112,344 in sales, analyzed with cohort and window-function queries.

The segment query splits buyers by how often they come back, then compares each group’s share of buyers with its share of revenue.

What I found

Loyal customers, with four or more orders, are a third of buyers (63 of 189) and bring in 54.4% of revenue. One-time buyers are 19% of buyers and 7.5% of revenue.

Buyers analyzed189
Loyal customers (4+ orders)33.3% of buyers
Their share of revenue54.4%
One-time buyers19.0% of buyers, 7.5% of revenue

Query 2.4, rerun on the repository’s seed data on Oct 24, 2026. An earlier version of the repository’s README reported different shares; this rerun replaces them.

Next case study: Replacing a manual data pull at Coca-Cola