Brightlane's finance team is auditing revenue from recently acquired customers — those who registered on or after 2025-01-01 00:00:00+00.
Write a query to return the total order value from these customers as a single figure.
Assumptions:
- Recently acquired customers are those whose
created_atis on or after2025-01-01 00:00:00+00. Every such customer must contribute to the figure, including those who have placed no orders. - If no recently acquired customer has placed any order, the figure should be
0rather than missing.
Output:
- A single row with one column,
total_revenue, containing the total order value.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
COALESCE(SUM(o.total_amount), 0) AS total_revenue
FROM
customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE
c.created_at >= '2025-01-01 00:00:00+00'::TIMESTAMPTZThe shape
The LEFT JOIN plus the WHERE on a left-side column keeps every recently acquired customer in the calculation, even those with no orders. Their orders contribute missing values to the SUM, SUM skips missing values, and COALESCE rewrites the all-missing result to the presentational 0 the finance report needs.
Clause by clause
SELECT COALESCE(SUM(o.total_amount), 0) AS total_revenuereturns the audit figure.SUM(o.total_amount)adds up every non-missing order amount across the qualifying customers. If no qualifying customer placed any order, every attachedo.total_amountis missing,SUMreturns missing, andCOALESCE(..., 0)converts that missing result to0.FROM customers c LEFT JOIN orders o ON c.id = o.customer_idpairs each customer with their orders. A customer with no orders still appears, with everyo.* column missing.WHERE c.created_at >= '2025-01-01 00:00:00+00'::timestamptzrestricts to recently acquired customers. The condition is onc.created_at— a left-side column — so it filters customers rather than dropping unmatched rows. The cast pins the comparison to atimestamptzvalue so the literal is interpreted in the same time zone as the stored column.
Why this and not WHERE o.total_amount IS NOT NULL
That filter would defeat the LEFT JOIN for any customer with no orders and produce a result equal to the inner-join sum. On this data the answer would happen to look the same, but the meaning is different. The version here computes "total revenue from the recently acquired population, including those with zero revenue." The WHERE o.total_amount IS NOT NULL version computes "total revenue across recently acquired customers who placed at least one order." The first matches the prompt; the second silently changes the population the figure is about.
The trap
A bare SUM on an all-missing set returns missing, not zero, and a missing total displayed in a finance report reads as a data gap rather than as the genuine zero it is. COALESCE(SUM(...), 0) is the deliberate presentational rewrite. The choice matters: a region with no qualifying customers and a region with qualifying customers who spent nothing are analytically different, and only the second is a true zero. On this prompt the population is "any qualifying customer including zero-spenders," so the rewrite is correct; on a different population, leaving the missing value alone would be the right call.
You practiced COALESCE(SUM(...), 0) — when SUM operates over only missing values it returns missing, so wrapping it in COALESCE produces the presentational zero a report needs.
