Агрегация с FILTER и условными подсчётами

Несколько срезов статистики одним проходом по таблице вместо нескольких запросов.

SELECT
    DATE_TRUNC('month', created_at)                       AS month,
    COUNT(*)                                              AS total,
    COUNT(*) FILTER (WHERE status = 'paid')               AS paid,
    COUNT(*) FILTER (WHERE status = 'cancelled')          AS cancelled,
    SUM(total) FILTER (WHERE status = 'paid')             AS revenue,
    ROUND(AVG(total) FILTER (WHERE status = 'paid'), 2)   AS avg_check,
    ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'paid') / COUNT(*), 1) AS paid_pct,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total)    AS median
FROM orders
WHERE created_at >= NOW() - INTERVAL '1 year'
GROUP BY 1
ORDER BY 1 DESC;

-- Переносимый вариант (MySQL, SQLite): CASE вместо FILTER
SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END)  AS paid,
    SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS revenue
FROM orders;