medium · ~12 min
Growth wants to compare customer value across acquisition channels using customers and orders.
customers
orders
A customer's lifetime spend is the sum of amount across all orders placed by that customer. Write a query that reports the average customer lifetime spend for each signup_channel:
amount
signup_channel
avg_lifetime_spend
Input contract: customer_id is unique and non-null in customers; signup_channel is non-null. order_id is unique and non-null in orders; orders.customer_id is a non-null foreign key to customers, and amount is a non-null positive float. A customer may have zero, one, or many orders.
customer_id
order_id
orders.customer_id