Given invoice tables for multiple years, write a query to find the customers with the highest lifetime spend.

Question

Anonymous · Accepted Answer

WITH all_invoices AS (

SELECT customer_id, invoice_amount

FROM invoices_2021

WHERE invoice_status = 'PAID'

UNION ALL

SELECT customer_id, invoice_amount

FROM invoices_2022

WHERE invoice_status = 'PAID'

UNION ALL

SELECT customer_id, invoice_amount

FROM invoices_2023

WHERE invoice_status = 'PAID'

)

SELECT

customer_id,

SUM(invoice_amount) AS lifetime_spend

FROM all_invoices

GROUP BY customer_id

ORDER BY lifetime_spend DESC;