SQL-kyselyn rakentaja

WITH customer_totals AS (
    SELECT
        o.customer_id,
        SUM(o.total) AS total_order_value
    FROM orders AS o
    WHERE o.created_at >= CURRENT_TIMESTAMP - INTERVAL '90' DAY
    GROUP BY o.customer_id
)
SELECT
    c.id,
    c.name,
    ct.total_order_value
FROM customer_totals AS ct
JOIN customers AS c
    ON c.id = ct.customer_id
ORDER BY ct.total_order_value DESC
FETCH FIRST 10 ROWS ONLY;

Explanation: The CTE filters orders from the last 90 days and sums their values by customer. The outer query joins customer details, sorts by total order value descending, and returns the top 10.

Suggested indexes:

• customers(id) — normally provided by the primary key.
• orders(created_at, customer_id) — supports the date filter and grouping/join key.
• If the database supports included columns, consider orders(created_at, customer_id) INCLUDE (total) to enable a covering index.

Biggest performance risk: Scanning and aggregating a very large number of orders within the 90-day window. An index helps with filtering, but if most orders fall within that period, the database may still process a substantial portion of the table. Pre-aggregated daily customer totals may be preferable at high scale.