Query Caching with Materialized CTEs

Finding the top spenders above the average needs the per-customer totals twice: once to compare each customer with the average, and once to produce the rows. Aggregating 5,000 orders two times is wasteful. In SQLite you can tell the engine to compute a CTE once and reuse the stored result for the rest of the statement with AS MATERIALIZED, a small in-query cache.

Write a query that defines totals (customer_id and the sum of total as spend) as a materialized CTE, then returns customer_id and spend for the 5 highest customers whose spend is greater than the average spend of all customers. Order by spend descending, then customer_id.

Constraints

• Columns, in order: customer_id, spend
• Define the totals with WITH totals AS MATERIALIZED (...)
• Only customers above the average spend, top 5, ordered by spend descending then customer_id

Hints