GROUP BY / Aggregates

-- 1) Basic — count per group
SELECT  country, COUNT(*) AS users
FROM    users
GROUP   BY country
ORDER   BY users DESC;

-- 2) Multiple aggregates
SELECT  category,
        COUNT(*)        AS n,
        AVG(price)::numeric(10,2) AS avg_price,
        MIN(price)      AS min_price,
        MAX(price)      AS max_price,
        SUM(stock)      AS total_stock
FROM    products
GROUP   BY category;

-- 3) HAVING — filter the GROUPS (not the rows)
SELECT  user_id, COUNT(*) AS posts
FROM    posts
GROUP   BY user_id
HAVING  COUNT(*) >= 10
ORDER   BY posts DESC;

-- 4) GROUP BY ROLLUP / CUBE / GROUPING SETS — subtotals + totals in one query
SELECT  country, plan, COUNT(*) AS n
FROM    users
GROUP   BY ROLLUP (country, plan);
-- → per (country, plan) + per (country) + grand total

-- 5) JSON / array aggregates — pull related rows along
SELECT  u.id, u.email,
        JSON_AGG(p ORDER BY p.created_at DESC) AS posts,
        STRING_AGG(t.name, ', ' ORDER BY t.name) FILTER (WHERE t.id IS NOT NULL) AS tags
FROM    users u
LEFT    JOIN posts p ON p.user_id = u.id
LEFT    JOIN user_tags ut ON ut.user_id = u.id
LEFT    JOIN tags t ON t.id = ut.tag_id
GROUP   BY u.id, u.email;

-- 6) Filtered aggregates — FILTER clause (PostgreSQL extension)
SELECT
    COUNT(*)                                  AS total,
    COUNT(*) FILTER (WHERE status = 'paid')   AS paid,
    SUM(total) FILTER (WHERE status = 'paid') AS revenue,
    AVG(total) FILTER (WHERE status = 'paid') AS avg_paid
FROM    orders
WHERE   created_at >= now() - INTERVAL '30 days';

-- 7) GROUP BY vs WINDOW functions
--    GROUP BY collapses rows. Windows attach a per-row aggregate.
SELECT  user_id, post_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS n,
        COUNT(*)   OVER (PARTITION BY user_id) AS total_posts
FROM    posts;

SELECT date_trunc('day', created_at) AS day, count(*) AS n
FROM events
WHERE created_at >= now() - interval '7 days'
GROUP BY 1
ORDER BY 1;