🟢 How to Compare Any Customer to the Average (Using Subqueries)

Hey Reader,

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?"

Most analysts learning SQL don't realize that, 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.

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.

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.

Instead of saying "These customers spent a lot," you can now say "These 10 customers spend 3.5-6x 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:

"$516K revenue" → "Interesting."
"$516K revenue, 22% of our total" → "That's our biggest category."
"$516K 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:

vs. average → "This region is 2x the average"
vs. total → "This product accounts for 35% of revenue"
vs. prior period → "Up 12% from last quarter"

One comparison transforms a number into an insight.

Until next time, Brian

Brian Graves, creator of Analytics in Action

Say 👋 on X/Twitter, LinkedIn, or book a call with me. You can always reply to these emails. I check them all.

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.

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.

Starting With Data