Derived tables power complex analytics: pre-aggregating sales data before joining with products, filtering large datasets before expensive operations, calculating intermediate results for multi-step analysis, creating virtual tables for reporting, and simplifying complex business logic into readable query steps.

E-commerce Order Analytics

Pre-aggregate order data before joining with customer information

SELECT c.name, o.order_count, o.total_spent FROM customers c JOIN (SELECT customer_id, COUNT(*) as order_count, SUM(total) as total_spent FROM orders GROUP BY customer_id) AS o ON c.id = o.customer_id;

Reduces query time from 5s to 500ms by aggregating 1M orders to 10K customers before joining

All