Lesson 13.3

SQL Practice: Customer Lifetime Value

Rank customers by shipped revenue and keep only meaningful lifetime-value totals.

Customer lifetime value is a grouped revenue query. You need customer names, completed orders, and line-item revenue, then one result row per customer.

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 is not a row-level fact. It only exists after all of a customer's shipped line items have been summed, so the filter belongs inHAVING.

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 ShopTable · customers9 columns · 20 rows

Table · customers

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

9 columns · 20 rows

customer_id intfirst_name textlast_name textemail textphone textcity textstate textsignup_date dateis_active bool

Your task

For shipped orders only, return customers with at least 75 in lifetime revenue. Include customer_id, customer_name, distinct orders, and roundedlifetime_revenue. Sort by lifetime_revenue descending, then customer_name.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer