Lesson 14.3

SQL Practice: Customer Lifetime Revenue

Rank customers by shipped revenue and keep only customers above a revenue threshold.

This lesson calculates lifetime revenue: shipped sales summed per customer. That is sometimes used as a simple CLV proxy, but true customer lifetime value can also account for margin, acquisition cost, refunds, retention, and future expected value.

This lesson brings together the most common analytics pieces: multi-table joins, a calculated money value, GROUP BY, and HAVING.

Use HAVING for the threshold

The 75-dollar cutoff isn't a row-level fact. It only exists after all of a customer's shipped line items have been summed, so the filter belongs in HAVING.

Pattern

select customer_id,
       customer_name,
       count(distinct order_id) as orders,
       round(sum(line_revenue), 2) as lifetime_revenue
from joined_sales
where status = 'shipped'
group by customer_id, customer_name
having sum(line_revenue) >= 75
order by lifetime_revenue desc

Schema · Garden Shop Tables · customers, orders, order_items 3 tables

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Your task

For shipped orders only, return customers with at least 75 in lifetime revenue. Include customer_id, customer_name (first and last name joined by a space), distinct orders, and lifetime_revenue rounded to two decimals. Sort by lifetime_revenue descending, then customer_name.