04-aggregate-functions.sql

-- ============================================================
-- SQL Masterclass — Chapter 04: Aggregate Functions
-- ============================================================
-- 🟢 BEGINNER
--
-- In this chapter you will learn:
--   • COUNT — counting rows
--   • SUM — totaling numeric values
--   • AVG — calculating averages
--   • MIN and MAX — finding extremes
--   • Combining aggregates in a single query
--   • Aggregates with WHERE filters
-- ============================================================


-- ============================================================
-- 4.1  COUNT — How many?
-- ============================================================

-- Total number of orders
SELECT COUNT(*) AS total_orders
FROM orders;

-- COUNT(*) counts all rows, including NULLs.
-- COUNT(column) counts only non-NULL values!

-- How many orders have a delivery date?
SELECT
    COUNT(*)                            AS total_orders,
    COUNT(order_delivered_customer_date) AS delivered_orders
FROM orders;

-- The difference tells us how many are NOT yet delivered.


-- ============================================================
-- 4.2  SUM — What's the total?
-- ============================================================

-- Total revenue from all order items
SELECT SUM(price) AS total_revenue
FROM order_items;

-- Total freight costs
SELECT SUM(freight_value) AS total_freight
FROM order_items;

-- Total revenue + freight combined
SELECT
    SUM(price)                       AS total_revenue,
    SUM(freight_value)               AS total_freight,
    SUM(price) + SUM(freight_value)  AS grand_total
FROM order_items;

-- Or equivalently:
SELECT
    SUM(price + freight_value) AS grand_total
FROM order_items;


-- ============================================================
-- 4.3  AVG — What's the average?
-- ============================================================

-- Average item price
SELECT AVG(price) AS avg_price
FROM order_items;

-- Average review score
SELECT AVG(review_score) AS avg_review_score
FROM order_reviews;

-- Average payment value
SELECT AVG(payment_value) AS avg_payment
FROM order_payments;

-- Average product weight (in kg for readability)
SELECT AVG(product_weight_g) / 1000.0 AS avg_weight_kg
FROM products
WHERE product_weight_g IS NOT NULL;


-- ============================================================
-- 4.4  MIN and MAX — Finding extremes
-- ============================================================

-- Cheapest and most expensive items
SELECT
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM order_items;

-- Date range of our data
SELECT
    MIN(order_purchase_timestamp) AS first_order,
    MAX(order_purchase_timestamp) AS last_order
FROM orders;

-- Lightest and heaviest products
SELECT
    MIN(product_weight_g) AS lightest_g,
    MAX(product_weight_g) AS heaviest_g
FROM products;

-- Review score range
SELECT
    MIN(review_score) AS lowest_score,
    MAX(review_score) AS highest_score
FROM order_reviews;


-- ============================================================
-- 4.5  COMBINING MULTIPLE AGGREGATES
-- ============================================================

-- Complete pricing overview
SELECT
    COUNT(*)           AS total_items,
    SUM(price)         AS total_revenue,
    AVG(price)         AS avg_price,
    MIN(price)         AS min_price,
    MAX(price)         AS max_price
FROM order_items;


-- Complete payment overview
SELECT
    COUNT(*)               AS total_payments,
    SUM(payment_value)     AS total_payment_value,
    AVG(payment_value)     AS avg_payment,
    MIN(payment_value)     AS min_payment,
    MAX(payment_value)     AS max_payment,
    AVG(payment_installments) AS avg_installments
FROM order_payments;


-- ============================================================
-- 4.6  AGGREGATES WITH WHERE
-- ============================================================

-- Average price of items above 100 BRL
SELECT AVG(price) AS avg_price_above_100
FROM order_items
WHERE price > 100;

-- Total revenue from credit card payments only
SELECT SUM(payment_value) AS credit_card_revenue
FROM order_payments
WHERE payment_type = 'credit_card';

-- Average review score for delivered orders
SELECT AVG(r.review_score) AS avg_delivered_score
FROM order_reviews r
JOIN orders o ON r.order_id = o.order_id
WHERE o.order_status = 'delivered';

-- Count of 5-star reviews
SELECT COUNT(*) AS five_star_count
FROM order_reviews
WHERE review_score = 5;

-- Count of 1-star reviews
SELECT COUNT(*) AS one_star_count
FROM order_reviews
WHERE review_score = 1;


-- ============================================================
-- 4.7  USEFUL PATTERNS
-- ============================================================

-- Ratio: What percentage of orders were delivered?
SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
    ROUND(
        100.0 * SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS delivery_rate_pct
FROM orders;


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

-- Exercise 1: What is the total freight cost for all orders?

-- Exercise 2: What are the average, minimum, and maximum
--             payment installments?

-- Exercise 3: How many unique customers placed orders?
--             (Hint: COUNT(DISTINCT ...))

-- Exercise 4: What is the average review score for orders
--             that were canceled?

-- Exercise 5: What is the total revenue (sum of price) from
--             items sold by sellers in state 'SP'?

-- Exercise 6: What is the average product description length
--             for products with at least 3 photos?


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

-- Exercise 1
SELECT SUM(freight_value) AS total_freight
FROM order_items;

-- Exercise 2
SELECT
    AVG(payment_installments) AS avg_installments,
    MIN(payment_installments) AS min_installments,
    MAX(payment_installments) AS max_installments
FROM order_payments;

-- Exercise 3
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

-- Exercise 4
SELECT AVG(r.review_score) AS avg_canceled_score
FROM order_reviews r
JOIN orders o ON r.order_id = o.order_id
WHERE o.order_status = 'canceled';

-- Exercise 5
SELECT SUM(oi.price) AS sp_revenue
FROM order_items oi
JOIN sellers s ON oi.seller_id = s.seller_id
WHERE s.seller_state = 'SP';

-- Exercise 6
SELECT AVG(product_description_lenght) AS avg_desc_length
FROM products
WHERE product_photos_qty >= 3;