03-sorting-and-distinct.sql

-- ============================================================
-- SQL Masterclass — Chapter 03: Sorting and DISTINCT
-- ============================================================
-- 🟢 BEGINNER
--
-- In this chapter you will learn:
--   • Sorting results with ORDER BY
--   • Ascending (ASC) vs descending (DESC) order
--   • Multi-column sorting
--   • Removing duplicates with DISTINCT
--   • Counting unique values with COUNT(DISTINCT)
-- ============================================================


-- ============================================================
-- 3.1  ORDER BY — ASCENDING (default)
-- ============================================================
-- ASC is the default, you don't need to type it.

-- Show the cheapest items first
SELECT
    order_id,
    product_id,
    price
FROM order_items
ORDER BY price
LIMIT 10;

-- Equivalent to:
SELECT
    order_id,
    product_id,
    price
FROM order_items
ORDER BY price ASC
LIMIT 10;


-- ============================================================
-- 3.2  ORDER BY — DESCENDING
-- ============================================================

-- Show the most expensive items
SELECT
    order_id,
    product_id,
    price
FROM order_items
ORDER BY price DESC
LIMIT 10;


-- Most recent orders first
SELECT
    order_id,
    order_status,
    order_purchase_timestamp
FROM orders
ORDER BY order_purchase_timestamp DESC
LIMIT 10;


-- ============================================================
-- 3.3  MULTI-COLUMN SORTING
-- ============================================================
-- Sort by state first, then by city within each state.

SELECT DISTINCT
    customer_state,
    customer_city
FROM customers
ORDER BY customer_state ASC, customer_city ASC
LIMIT 20;


-- Sort by payment_type, then by highest value first
SELECT
    order_id,
    payment_type,
    payment_value
FROM order_payments
ORDER BY payment_type ASC, payment_value DESC
LIMIT 20;


-- ============================================================
-- 3.4  ORDER BY WITH COLUMN POSITION
-- ============================================================
-- You can use column numbers instead of names.
-- Column 1 = first column in SELECT, etc.

SELECT
    customer_state,
    customer_city
FROM customers
ORDER BY 1, 2
LIMIT 20;

-- ⚠️ This works but is less readable. Avoid in production code!


-- ============================================================
-- 3.5  ORDER BY WITH EXPRESSIONS
-- ============================================================

-- Sort by total cost (price + freight)
SELECT
    order_id,
    price,
    freight_value,
    price + freight_value AS total_cost
FROM order_items
ORDER BY price + freight_value DESC
LIMIT 10;

-- Or use the alias (supported in most databases):
SELECT
    order_id,
    price,
    freight_value,
    price + freight_value AS total_cost
FROM order_items
ORDER BY total_cost DESC
LIMIT 10;


-- ============================================================
-- 3.6  DISTINCT — Remove duplicate rows
-- ============================================================

-- What order statuses exist?
SELECT DISTINCT order_status
FROM orders;

-- Expected: delivered, shipped, canceled, unavailable,
--           invoiced, processing, created, approved


-- What payment types exist?
SELECT DISTINCT payment_type
FROM order_payments;

-- Expected: credit_card, boleto, voucher, debit_card, not_defined


-- What states do sellers operate from?
SELECT DISTINCT seller_state
FROM sellers
ORDER BY seller_state;


-- ============================================================
-- 3.7  DISTINCT ON MULTIPLE COLUMNS
-- ============================================================
-- DISTINCT applies to the combination of ALL selected columns.

-- Unique combinations of state + city
SELECT DISTINCT
    customer_state,
    customer_city
FROM customers
ORDER BY customer_state, customer_city
LIMIT 20;


-- ============================================================
-- 3.8  COUNT(DISTINCT) — Count unique values
-- ============================================================

-- How many unique customers?
SELECT COUNT(DISTINCT customer_unique_id) AS unique_customers
FROM customers;

-- How many unique products were sold?
SELECT COUNT(DISTINCT product_id) AS products_sold
FROM order_items;

-- How many unique sellers made sales?
SELECT COUNT(DISTINCT seller_id) AS active_sellers
FROM order_items;

-- How many distinct states have customers?
SELECT COUNT(DISTINCT customer_state) AS states_with_customers
FROM customers;

-- How many distinct cities have sellers?
SELECT COUNT(DISTINCT seller_city) AS cities_with_sellers
FROM sellers;


-- ============================================================
-- 3.9  COMBINING WHERE + ORDER BY + LIMIT
-- ============================================================

-- Top 10 most expensive boleto payments
SELECT
    order_id,
    payment_type,
    payment_value
FROM order_payments
WHERE payment_type = 'boleto'
ORDER BY payment_value DESC
LIMIT 10;

-- 5 cheapest items shipped from SP sellers
SELECT
    oi.order_id,
    oi.price,
    s.seller_state
FROM order_items oi
JOIN sellers s ON oi.seller_id = s.seller_id
WHERE s.seller_state = 'SP'
ORDER BY oi.price ASC
LIMIT 5;


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

-- Exercise 1: Show the 10 heaviest products (by product_weight_g,
--             descending). Show product_id and weight.

-- Exercise 2: How many DISTINCT product categories exist in the
--             products table?

-- Exercise 3: Show all unique customer states, ordered
--             alphabetically.

-- Exercise 4: Find the 10 highest payment values. Show order_id,
--             payment_type, and payment_value.

-- Exercise 5: How many distinct cities are there among customers?

-- Exercise 6: Show the 15 most recent orders that were canceled.
--             Display order_id, status, and purchase timestamp.


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

-- Exercise 1
SELECT product_id, product_weight_g
FROM products
WHERE product_weight_g IS NOT NULL
ORDER BY product_weight_g DESC
LIMIT 10;

-- Exercise 2
SELECT COUNT(DISTINCT product_category_name) AS category_count
FROM products;

-- Exercise 3
SELECT DISTINCT customer_state
FROM customers
ORDER BY customer_state;

-- Exercise 4
SELECT order_id, payment_type, payment_value
FROM order_payments
ORDER BY payment_value DESC
LIMIT 10;

-- Exercise 5
SELECT COUNT(DISTINCT customer_city) AS city_count
FROM customers;

-- Exercise 6
SELECT
    order_id,
    order_status,
    order_purchase_timestamp
FROM orders
WHERE order_status = 'canceled'
ORDER BY order_purchase_timestamp DESC
LIMIT 15;