Hello ,
Imagine you’re presenting a finding to your team: “Customer A spent $4,200 with us.”
The first question from the room: “Is that a lot?”
Without context, the number means nothing. You need comparison. Is the average customer spend $500 or $5,000? Is $4,200 in the top 10% or the middle of the pack?
This is the “compared to what?” problem, and subqueries solve it elegantly.
The Business Problem
Numbers without context don’t drive decisions. Executives don’t want to hear “$4,200.” They want to hear “two times the average customer spend” or “top 5% of all customers.”
The challenge: the comparison values (averages, totals, benchmarks) live in the same tables as the individual records. You need a way to calculate both simultaneously.
That’s exactly what subqueries do.
The Query: Customers vs. Average
Here’s what I ran against the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics):
-- Compare each customer's spending to the overall average
SELECT
c.first_name || ' ' || c.last_name AS customer_name,
c.experience_level,
SUM(p.amount) AS total_spent,
(SELECT ROUND(AVG(customer_total), 2)
FROM (
SELECT SUM(p2.amount) AS customer_total
FROM customers c2
INNER JOIN bookings b2 ON c2.customer_id = b2.customer_id
INNER JOIN payments p2 ON b2.booking_id = p2.booking_id
WHERE p2.payment_status = 'completed'
AND b2.status IN ('completed', 'confirmed')
GROUP BY c2.customer_id
) avg_calc
) AS overall_avg_spend,
ROUND(SUM(p.amount) /
(SELECT AVG(customer_total)
FROM (
SELECT SUM(p2.amount) AS customer_total
FROM customers c2
INNER JOIN bookings b2 ON c2.customer_id = b2.customer_id
INNER JOIN payments p2 ON b2.booking_id = p2.booking_id
WHERE p2.payment_status = 'completed'
AND b2.status IN ('completed', 'confirmed')
GROUP BY c2.customer_id
) avg_calc
), 1) AS times_above_average
FROM customers c
INNER JOIN bookings b ON c.customer_id = b.customer_id
INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
AND b.status IN ('completed', 'confirmed')
GROUP BY c.customer_id, c.first_name, c.last_name, c.experience_level
HAVING SUM(p.amount) > (
SELECT AVG(customer_total)
FROM (
SELECT SUM(p2.amount) AS customer_total
FROM customers c2
INNER JOIN bookings b2 ON c2.customer_id = b2.customer_id
INNER JOIN payments p2 ON b2.booking_id = p2.booking_id
WHERE p2.payment_status = 'completed'
AND b2.status IN ('completed', 'confirmed')
GROUP BY c2.customer_id
) avg_calc
)
ORDER BY total_spent DESC
LIMIT 10;
This shows each above-average customer, their total spending, the overall average, and how many times above average they are.
The insight:
Instead of saying “These customers spent a lot,” you can now say “These 10 customers spend 3-8x the average. They represent our highest-value segment and deserve dedicated attention.”
Same data. Completely different impact.
A Cleaner Approach: CTEs
That query above has repeated subqueries, which makes it harder to read. Here’s the same logic using a CTE:
-- Cleaner version using a CTE for the benchmark
WITH customer_spending AS (
SELECT
c.customer_id,
c.first_name || ' ' || c.last_name AS customer_name,
c.experience_level,
SUM(p.amount) AS total_spent
FROM customers c
INNER JOIN bookings b ON c.customer_id = b.customer_id
INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
AND b.status IN ('completed', 'confirmed')
GROUP BY c.customer_id, c.first_name, c.last_name, c.experience_level
),
benchmark AS (
SELECT ROUND(AVG(total_spent), 2) AS avg_spend
FROM customer_spending
)
SELECT
cs.customer_name,
cs.experience_level,
cs.total_spent,
b.avg_spend AS overall_average,
ROUND(cs.total_spent / b.avg_spend, 1) AS times_above_avg
FROM customer_spending cs
CROSS JOIN benchmark b
WHERE cs.total_spent > b.avg_spend
ORDER BY cs.total_spent DESC
LIMIT 10;
Same result, much more readable. The CTE calculates the average once and makes it available to the final query.
Three “Compared to What?” Patterns
Pattern 1: Individual vs. Overall Average (shown above)
“How does this customer compare to the average customer?”
Pattern 2: Category vs. Category
“How does hiking revenue compare to climbing revenue?”
-- Each category's share of total revenue
SELECT
e.expedition_type,
SUM(p.amount) AS category_revenue,
(SELECT SUM(p2.amount) FROM payments p2
WHERE p2.payment_status = 'completed') AS total_revenue,
ROUND(SUM(p.amount) * 100.0 /
(SELECT SUM(p2.amount) FROM payments p2
WHERE p2.payment_status = 'completed'), 1) AS revenue_share_pct
FROM expeditions e
INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
INNER JOIN bookings b ON ei.instance_id = b.instance_id
INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
GROUP BY e.expedition_type
ORDER BY category_revenue DESC;
Pattern 3: This Period vs. Last Period
“How does this month compare to last month?”
-- Monthly revenue with month-over-month change
WITH monthly_revenue AS (
SELECT
TO_CHAR(payment_date, 'YYYY-MM') AS month,
SUM(amount) AS revenue
FROM payments
WHERE payment_status = 'completed'
GROUP BY TO_CHAR(payment_date, 'YYYY-MM')
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 /
NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1
) AS pct_change
FROM monthly_revenue
ORDER BY month DESC
LIMIT 6;
Why “Compared to What?” Is the Most Important Question
Early in my career, I learned that raw numbers almost never drive decisions. It’s the comparison that creates urgency:
“$370K revenue” → “Interesting.”
“$370K revenue, 22% of our total” → “That’s our biggest category.”
“$370K revenue, up 15% from last quarter” → “Let’s invest more there.”
Every time you present a metric, ask yourself: “Compared to what?” Then add that context to your query.
Try This At Your Job
Pick any metric you report regularly — revenue, customer count, transaction volume — and add one comparison:
1. vs. average → “This region is 2x the average”
2. vs. total → “This product accounts for 35% of revenue”
3. vs. prior period → “Up 12% from last quarter”
One comparison transforms a number into an insight.
Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))
—
P.S. Adding context to your analysis is the core concept behind the So What Framework in Module 6 of SQL for Business Impact. Every number needs a “so what” — and subqueries are one way to build that context directly into your queries. Check it out at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).
P.P.S. What’s one metric you report that always gets the response “is that good or bad?” Hit reply and tell me — I can probably help you add the right comparison. I read every response.
Want More SQL Insights Like This?
Join thousands of analysts getting weekly tips on turning data into business impact.