Average Customer Lifetime Spend — Solution & ExplanationProblems / Average Customer Lifetime Spend / EditorialApproach

Level 1 — lifetime spend per customer: join orders to customers (an inner join, so the customer with zero orders drops out) and SUM(amount) GROUP BY customer_id, signup_channel.

Level 2 — average per channel: AVG(lifetime_spend) GROUP BY signup_channel, rounded to 2 decimals.

SQL cannot nest aggregates — AVG(SUM(amount)) is a syntax error — so the CTE/derived table is what makes the two levels legal.

Key InsightAVG(o.amount) per channel answers a different question: the average order size. In the ads channel, one customer placed four $25 orders (spend $100) and another a single $300 order: average lifetime spend is (100 + 300) / 2 = $200, but the naive average of amounts is 400 / 5 = $80. Whenever customers place different numbers of orders, the two averages diverge — the naive one is weighted by order count, not by customer.Hints

Two aggregation levels: first GROUP BY customer, then GROUP BY channel over that result. A CTE or derived table holds the first level — SQL will not let you write AVG(SUM(amount)) directly.

AVG(amount) grouped by channel is the average ORDER size, not the average CUSTOMER spend — the data is built so these two disagree in every channel.

Join orders to customers to pick up signup_channel, SUM per customer in the CTE, then AVG those sums. The inner join naturally drops the customer who never ordered.

Solve Average Customer Lifetime Spend yourself →