3. Customer Order Summary Mart

Difficulty: Medium · Topic: Refs & marts

Build the customers mart from two staging models, {{ ref('stg_customers') }} and {{ ref('stg_orders') }}. One row per customer, including customers who have never ordered:

• customer_id, and full_name: first and last name separated by a space.
• first_order_on and most_recent_order_on: the earliest and latest ordered_on (null without orders).
• number_of_orders and lifetime_value (the sum of amount): 0 for a customer without orders.

Returned orders (status = 'returned') do not count anywhere: not in the dates, the count or the value.

Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.

Sample inputs

{{ ref('stg_customers') }}

customer_id	first_name	last_name
1	Ana	Silva
2	Bo	Chen
3	Cy	Okafor

{{ ref('stg_orders') }}

order_id	customer_id	ordered_on	status	amount
100	1	2024-01-03	completed	20
101	1	2024-02-10	completed	35.5
102	2	2024-01-15	shipped	12.25
103	2	2024-03-01	returned	99

Expected output

customer_id	full_name	first_order_on	most_recent_order_on	number_of_orders	lifetime_value
1	Ana Silva	2024-01-03	2024-02-10	2	55.5
2	Bo Chen	2024-01-15	2024-01-15	1	12.25
3	Cy Okafor	null	null	0	0

Hints

Hint 1

Aggregate orders per customer in a CTE first, then left join it to customers. Joining first and aggregating after works too, but is easier to get wrong.

Hint 2

Filter returned orders inside the orders CTE. A where status != 'returned' after a left join removes customers whose only orders were returned.

Hint 3

After a left join, missing aggregates are null: coalesce(..., 0) turns them into 0. And count(*) would count the null row as 1.

More dbt problems

All dbt problems