Dataset and schema

The Olist Brazilian E-Commerce dataset (free on Kaggle) is a strong choice: real, anonymized order data across multiple linked tables, large enough (100,000+ orders) to produce credible findings. A simplified version of its schema:

schema.sql
CREATE TABLE customers (
  customer_id  VARCHAR(50) PRIMARY KEY,
  customer_state VARCHAR(2)
);

CREATE TABLE orders (
  order_id       VARCHAR(50) PRIMARY KEY,
  customer_id    VARCHAR(50) REFERENCES customers(customer_id),
  order_purchase_timestamp TIMESTAMP
);

CREATE TABLE order_items (
  order_id     VARCHAR(50) REFERENCES orders(order_id),
  product_id   VARCHAR(50),
  price        DECIMAL(10,2)
);

CREATE TABLE products (
  product_id       VARCHAR(50) PRIMARY KEY,
  product_category VARCHAR(100)
);

Query 1 — Top-selling products

top-products.sql
SELECT
  p.product_category,
  COUNT(*) AS units_sold,
  ROUND(SUM(oi.price), 2) AS total_revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_category
ORDER BY total_revenue DESC
LIMIT 10;

Query 2 — Monthly revenue trend

monthly-revenue.sql
SELECT
  DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
  ROUND(SUM(oi.price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
ORDER BY month;

Pair this with a year-over-year growth query for a "how did this month compare to last year" follow-up finding.

Query 3 — RFM customer segmentation

Recency, Frequency, and Monetary value, each scored into quintiles with NTILE():

rfm-segmentation.sql
WITH customer_orders AS (
  SELECT
    o.customer_id,
    MAX(o.order_purchase_timestamp) AS last_order_date,
    COUNT(*) AS order_count,
    SUM(oi.price) AS total_spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id
),
scored AS (
  SELECT
    customer_id,
    NTILE(5) OVER (ORDER BY last_order_date DESC) AS recency_score,
    NTILE(5) OVER (ORDER BY order_count)   AS frequency_score,
    NTILE(5) OVER (ORDER BY total_spent)   AS monetary_score
  FROM customer_orders
)
SELECT *,
  CASE
    WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 THEN 'Champion'
    WHEN recency_score <= 2 AND monetary_score >= 4 THEN 'At Risk (high value)'
    ELSE 'Standard'
  END AS segment
FROM scored;

The exact thresholds are a judgment call — worth stating explicitly in the project's README so a reviewer understands the segmentation logic isn't arbitrary.

Query 4 — Average order value by region

aov-by-region.sql
SELECT
  c.customer_state,
  ROUND(SUM(oi.price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o
JOIN customers c   ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_state
ORDER BY avg_order_value DESC;

Query 5 — Repeat purchase rate

repeat-purchase-rate.sql
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT
  ROUND(
    COUNT(DISTINCT CASE WHEN order_count > 1 THEN customer_id END) * 100.0
    / COUNT(DISTINCT customer_id),
  1) AS repeat_purchase_rate_pct
FROM order_counts;

Common mistakes

  • Double-counting revenue when an order has multiple line items. Joining orders to order_items without deduplicating at the right grain can inflate order-level counts.
  • Arbitrary RFM thresholds with no explanation. Segmentation logic should be documented, not just presented as fact.
  • Computing average order value as average line-item price instead of total order value divided by number of distinct orders — these give very different numbers.
  • No written interpretation. "Champions represent 8% of customers but 34% of revenue — a retention campaign targeting the 'At Risk (high value)' segment could be high-leverage" is the kind of sentence that turns a query into a finding.

Key takeaways

  • A relational schema of customers, orders, order_items, and products supports five genuinely different analyses.
  • RFM segmentation uses NTILE() to score recency, frequency, and monetary value independently before combining them.
  • Repeat purchase rate is a customer-level order count, thresholded and divided by total customers.
  • Average order value must be computed per distinct order, not per line item.
  • Document segmentation thresholds and cutoffs explicitly — they're judgment calls, not fixed rules.