SELECT
    COUNT(*)          AS total_rows,      -- all rows including NULLs
    COUNT(amount)     AS non_null_amounts, -- excludes NULLs
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount)       AS total_revenue,
    AVG(amount)       AS average_order,
    MIN(amount)       AS smallest_order,
    MAX(amount)       AS largest_order
FROM orders;

total_rows | non_null_amounts | unique_customers | total_revenue | average_order | smallest_order | largest_order
-----------|-----------------|-----------------|---------------|---------------|----------------|---------------
10         | 10              | 5               | 2350.00       | 235.00        | 45.00          | 780.00

-- Revenue per customer
SELECT
    customer_id,
    COUNT(*)       AS order_count,
    SUM(amount)    AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

-- Revenue per customer (with name)
SELECT
    c.name,
    COUNT(o.id)    AS order_count,
    SUM(o.amount)  AS total_spent,
    AVG(o.amount)  AS avg_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC NULLS LAST;

-- Only customers who spent more than 500
SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;

-- Customers with 3 or more orders
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;

-- WHERE — filter before grouping (on row data)
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'shipped'    -- only count shipped orders
GROUP BY customer_id
HAVING COUNT(*) > 1;        -- HAVING — filter after grouping

-- Revenue per customer per month
SELECT
    customer_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                     AS monthly_total
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
ORDER BY customer_id, month;

-- Orders by price tier
SELECT
    CASE
        WHEN amount < 100  THEN 'small'
        WHEN amount < 500  THEN 'medium'
        ELSE 'large'
    END AS tier,
    COUNT(*) AS count,
    SUM(amount) AS total
FROM orders
GROUP BY
    CASE
        WHEN amount < 100  THEN 'small'
        WHEN amount < 500  THEN 'medium'
        ELSE 'large'
    END
ORDER BY total DESC;

-- List products per order
SELECT
    order_id,
    STRING_AGG(p.name, ', ' ORDER BY p.name) AS products
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY order_id;

order_id | products
---------|-------------------------------
1        | Headphones, Mouse, Keyboard
2        | Monitor

-- Revenue by category and product, with subtotals
SELECT
    category,
    product_name,
    SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(category, product_name)
ORDER BY category NULLS LAST, product_name NULLS LAST;

category   | product_name | total
-----------|--------------|-------
Electronics | Phone       | 5000
Electronics | Laptop      | 12000
Electronics | NULL        | 17000   -- category subtotal
Clothing    | Shirt       | 800
Clothing    | NULL        | 800     -- category subtotal
NULL        | NULL        | 17800   -- grand total

SELECT
    p.name,
    SUM(oi.quantity)              AS units_sold,
    SUM(oi.quantity * p.price)    AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units_sold DESC
LIMIT 5;

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*)                         AS orders,
    SUM(amount)                      AS revenue,
    ROUND(AVG(amount), 2)            AS avg_order_value
FROM orders
WHERE order_date >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

SELECT c.name, MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING MAX(o.order_date) < NOW() - INTERVAL '90 days'
    OR MAX(o.order_date) IS NULL
ORDER BY last_order_date NULLS FIRST;

-- WRONG: selecting a non-aggregated column not in GROUP BY
SELECT customer_id, name, SUM(amount)  -- name is not in GROUP BY!
FROM orders
GROUP BY customer_id;

-- RIGHT: include name in GROUP BY
SELECT customer_id, name, SUM(amount)
FROM orders
GROUP BY customer_id, name;

-- WRONG: using WHERE on aggregate
SELECT customer_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 500  -- can't use aggregate in WHERE!
GROUP BY customer_id;

-- RIGHT: use HAVING
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;