05-group-by-and-having.sql

-- ============================================================
-- SQL Masterclass — Chapter 05: GROUP BY and HAVING
-- ============================================================
-- 🟢 BEGINNER
--
-- In this chapter you will learn:
--   • Grouping rows with GROUP BY
--   • Using aggregates with groups
--   • Multi-column GROUP BY
--   • Filtering groups with HAVING
--   • The difference between WHERE and HAVING
--   • Common GROUP BY patterns in analytics
-- ============================================================


-- ============================================================
-- 5.1  BASIC GROUP BY
-- ============================================================
-- GROUP BY splits rows into groups and lets you aggregate
-- each group independently.

-- How many orders per status?
SELECT
    order_status,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_status
ORDER BY order_count DESC;


-- How many customers per state?
SELECT
    customer_state,
    COUNT(*) AS customer_count
FROM customers
GROUP BY customer_state
ORDER BY customer_count DESC;


-- ============================================================
-- 5.2  GROUP BY WITH DIFFERENT AGGREGATES
-- ============================================================

-- Revenue by payment type
SELECT
    payment_type,
    COUNT(*)           AS num_payments,
    SUM(payment_value) AS total_value,
    AVG(payment_value) AS avg_value,
    MIN(payment_value) AS min_value,
    MAX(payment_value) AS max_value
FROM order_payments
GROUP BY payment_type
ORDER BY total_value DESC;


-- Review score distribution
SELECT
    review_score,
    COUNT(*) AS review_count
FROM order_reviews
GROUP BY review_score
ORDER BY review_score;


-- Sellers per state
SELECT
    seller_state,
    COUNT(*) AS seller_count
FROM sellers
GROUP BY seller_state
ORDER BY seller_count DESC
LIMIT 10;


-- ============================================================
-- 5.3  GROUP BY WITH EXPRESSIONS
-- ============================================================

-- Price range distribution using integer division
SELECT
    CAST(price / 100 AS INTEGER) * 100 AS price_bucket,
    COUNT(*) AS item_count
FROM order_items
GROUP BY CAST(price / 100 AS INTEGER) * 100
ORDER BY price_bucket;


-- ============================================================
-- 5.4  MULTI-COLUMN GROUP BY
-- ============================================================
-- Group by multiple columns for finer granularity.

-- Orders per status per state
SELECT
    c.customer_state,
    o.order_status,
    COUNT(*) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_state, o.order_status
ORDER BY c.customer_state, order_count DESC;


-- Revenue by seller state + payment type
SELECT
    s.seller_state,
    p.payment_type,
    COUNT(*)           AS num_transactions,
    SUM(p.payment_value) AS total_value
FROM order_payments p
JOIN orders o ON p.order_id = o.order_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
GROUP BY s.seller_state, p.payment_type
ORDER BY s.seller_state, total_value DESC;


-- ============================================================
-- 5.5  HAVING — Filtering groups
-- ============================================================
-- WHERE filters ROWS (before grouping).
-- HAVING filters GROUPS (after grouping).

-- States with more than 5,000 customers
SELECT
    customer_state,
    COUNT(*) AS customer_count
FROM customers
GROUP BY customer_state
HAVING COUNT(*) > 5000
ORDER BY customer_count DESC;


-- Payment types with average value over 150
SELECT
    payment_type,
    AVG(payment_value) AS avg_value
FROM order_payments
GROUP BY payment_type
HAVING AVG(payment_value) > 150;


-- Product categories with more than 1,000 items sold
SELECT
    p.product_category_name,
    COUNT(*) AS items_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_category_name
HAVING COUNT(*) > 1000
ORDER BY items_sold DESC;


-- ============================================================
-- 5.6  WHERE vs HAVING
-- ============================================================
-- Key difference:
--   WHERE  → filters individual ROWS before aggregation
--   HAVING → filters the aggregated GROUPS after aggregation

-- Example: Find states where delivered orders average > 200 BRL
-- WHERE filters to delivered orders first,
-- HAVING then keeps only high-value states.

SELECT
    c.customer_state,
    COUNT(*)                AS num_orders,
    AVG(p.payment_value)    AS avg_payment
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'             -- row filter
GROUP BY c.customer_state
HAVING AVG(p.payment_value) > 200              -- group filter
ORDER BY avg_payment DESC;


-- ============================================================
-- 5.7  REAL-WORLD ANALYTICS PATTERNS
-- ============================================================

-- Top 10 product categories by revenue
SELECT
    p.product_category_name,
    COUNT(DISTINCT oi.order_id) AS num_orders,
    SUM(oi.price)               AS total_revenue,
    AVG(oi.price)               AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL
GROUP BY p.product_category_name
ORDER BY total_revenue DESC
LIMIT 10;


-- Average review score by product category (top rated)
SELECT
    p.product_category_name,
    COUNT(*)            AS num_reviews,
    AVG(r.review_score) AS avg_score
FROM order_reviews r
JOIN orders o ON r.order_id = o.order_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL
GROUP BY p.product_category_name
HAVING COUNT(*) > 50                -- only categories with enough data
ORDER BY avg_score DESC
LIMIT 10;


-- Seller performance overview
SELECT
    s.seller_state,
    COUNT(DISTINCT s.seller_id) AS num_sellers,
    COUNT(oi.order_id)          AS items_sold,
    SUM(oi.price)               AS total_revenue,
    SUM(oi.price) / COUNT(DISTINCT s.seller_id) AS revenue_per_seller
FROM order_items oi
JOIN sellers s ON oi.seller_id = s.seller_id
GROUP BY s.seller_state
ORDER BY total_revenue DESC
LIMIT 10;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: How many payments were made with each payment type?
--             Order by count descending.

-- Exercise 2: What is the average price per product category?
--             Show only categories with avg price > 200.
--             Order by avg price descending.

-- Exercise 3: Which states have more than 100 sellers?

-- Exercise 4: What is the total payment value by payment type?
--             Also show the percentage of total.

-- Exercise 5: Find the top 5 cities with the most sellers.

-- Exercise 6: What is the average number of installments
--             per payment type? Exclude payment types with
--             fewer than 100 transactions.


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
SELECT
    payment_type,
    COUNT(*) AS payment_count
FROM order_payments
GROUP BY payment_type
ORDER BY payment_count DESC;

-- Exercise 2
SELECT
    p.product_category_name,
    AVG(oi.price) AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL
GROUP BY p.product_category_name
HAVING AVG(oi.price) > 200
ORDER BY avg_price DESC;

-- Exercise 3
SELECT
    seller_state,
    COUNT(*) AS num_sellers
FROM sellers
GROUP BY seller_state
HAVING COUNT(*) > 100
ORDER BY num_sellers DESC;

-- Exercise 4
SELECT
    payment_type,
    SUM(payment_value) AS total_value,
    ROUND(
        100.0 * SUM(payment_value) / (SELECT SUM(payment_value) FROM order_payments),
        2
    ) AS pct_of_total
FROM order_payments
GROUP BY payment_type
ORDER BY total_value DESC;

-- Exercise 5
SELECT
    seller_city,
    COUNT(*) AS num_sellers
FROM sellers
GROUP BY seller_city
ORDER BY num_sellers DESC
LIMIT 5;

-- Exercise 6
SELECT
    payment_type,
    AVG(payment_installments) AS avg_installments,
    COUNT(*)                  AS num_transactions
FROM order_payments
GROUP BY payment_type
HAVING COUNT(*) >= 100
ORDER BY avg_installments DESC;