Product analytics
A new question lands from the product team: “Which features do our best customers actually use?”
Features? NOVA sells physical products, not software with feature flags.
Fair — no feature-usage table here. But NOVA does have something close: product categories. What repeat customers keep buying is about as good a proxy for 'what they value' as we're going to get without click tracking.
Two building blocks you already have, joined together. First, per-category revenue and volume — the same shape as the revenue-by-category challenge from earlier in this chapter. Second, “best customers” defined the same way as the funnel lesson: customers with two or more orders.
Product analytics, here, is just: segment the customers, then see which categories that segment actually spends on.
Start with the baseline — every completed order, by category:
SELECT p.category, SUM(o.amount) AS revenue, COUNT(*) AS orders
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC;| category | revenue | orders |
|---|---|---|
| Wearables | 429.97 | 3 |
| Audio | 339.95 | 5 |
| Computing | 276.99 | 4 |
| Accessories | 183.94 | 4 |
| Home | 69 | 2 |
Now the segment. A CTE listing each customer’s order count, same subquery shape as the funnel and conversion lessons:
WITH customer_order_counts AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT customer_id, order_count
FROM customer_order_counts
WHERE order_count >= 2
ORDER BY order_count DESC;Join that segment onto the category breakdown, and split each category’s revenue into “from a repeat customer” versus the category total — a CASE WHEN inside SUM, the same conditional aggregation from the cohorts lesson:
WITH customer_order_counts AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT
p.category,
ROUND(SUM(CASE WHEN coc.order_count >= 2 THEN o.amount ELSE 0 END), 2) AS repeat_customer_revenue,
ROUND(SUM(o.amount), 2) AS total_revenue,
ROUND(
SUM(CASE WHEN coc.order_count >= 2 THEN o.amount ELSE 0 END) * 100.0 / SUM(o.amount),
1
) AS pct_from_repeat_customers
FROM orders o
JOIN products p ON p.id = o.product_id
JOIN customer_order_counts coc ON coc.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY pct_from_repeat_customers DESC;Running…
Every dollar of completed Wearables revenue — 100% of it — came from a repeat customer. Home sits at the other end, an even split between one-time and repeat buyers. If NOVA had to pick one category to feature to its best customers, this table just answered that.
Try it: What if you raised the repeat-customer bar to 3+ orders instead of 2+?
Run your query to see results here.
Only three customers clear that bar. “Repeat customer” isn’t one fixed definition — it’s a threshold you choose, and the threshold changes the story. At 2+ orders, Wearables looks entirely repeat-driven; tighten it to 3+ and there isn’t enough data left to say much about any single category. Always state the threshold you used when you share a number like this.
Among repeat customers only (2 or more orders), which product category has the highest average order value? Show category and average order value, using completed orders only, highest first.
Hint 1
You'll need the same customer_order_counts CTE to identify repeat customers.
Hint 2
Filter to repeat customers with a WHERE clause on the joined order_count, not a HAVING — you're not grouping by it.
Hint 3
SELECT p.category, ROUND(AVG(o.amount), 2) AS avg_order_value ... WHERE o.status = 'completed' AND coc.order_count >= 2 GROUP BY p.category ORDER BY avg_order_value DESC;
Solution
Running…
Wearables wins on both counts: it’s the category repeat customers buy exclusively, and it’s the category with by far the highest average order value among them. That’s two independent queries pointing at the same answer — which is usually a much stronger signal than either one alone.
Without event or feature-usage data, category-level purchase behavior is a legitimate stand-in for product analytics — segment customers with a CTE, then compare what each segment actually buys using conditional aggregation.