┌─────────────────────────────────────────────────────────────────────────┐
│                          RELATIONAL MODEL                               │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  USERS TABLE                          ORDERS TABLE                      │
│  ┌────┬───────────┬─────────────┐    ┌────┬─────────┬────────┬───────┐ │
│  │ id │   name    │    email    │    │ id │ user_id │ total  │ status│ │
│  ├────┼───────────┼─────────────┤    ├────┼─────────┼────────┼───────┤ │
│  │  1 │ Alice     │ [email protected] │◄───│  1 │    1    │ 150.00 │ done  │ │
│  │  2 │ Bob       │ [email protected] │◄───│  2 │    1    │  75.50 │ done  │ │
│  │  3 │ Charlie   │ [email protected] │◄───│  3 │    2    │ 200.00 │pending│ │
│  └────┴───────────┴─────────────┘    └────┴─────────┴────────┴───────┘ │
│         ▲                                      │                        │
│         │          PRIMARY KEY                 │                        │
│         └──────────────────────────────────────┘                        │
│                     FOREIGN KEY                                         │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

-- Select all columns from users table
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with column alias
SELECT 
    name AS customer_name,
    email AS contact_email
FROM users;

-- Basic equality
SELECT * FROM users WHERE id = 1;

-- String matching
SELECT * FROM users WHERE email = '[email protected]';

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE stock <= 10;

-- Not equal
SELECT * FROM users WHERE status != 'deleted';
SELECT * FROM users WHERE status <> 'deleted';  -- Same as !=

-- AND: Both conditions must be true
SELECT * FROM products 
WHERE category = 'electronics' 
  AND price < 500;

-- OR: At least one condition must be true
SELECT * FROM products 
WHERE category = 'electronics' 
   OR category = 'computers';

-- NOT: Negates a condition
SELECT * FROM users 
WHERE NOT status = 'banned';

-- Complex combinations (use parentheses!)
SELECT * FROM products 
WHERE (category = 'electronics' OR category = 'computers')
  AND price BETWEEN 100 AND 1000
  AND stock > 0;

-- IN: Match any value in a list
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

-- BETWEEN: Range inclusive
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE: Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';     -- Ends with
SELECT * FROM users WHERE name LIKE 'John%';            -- Starts with
SELECT * FROM users WHERE name LIKE '%son%';            -- Contains

-- ILIKE: Case-insensitive LIKE (PostgreSQL)
SELECT * FROM users WHERE name ILIKE '%john%';

-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE shipped_at IS NULL;          -- Not yet shipped
SELECT * FROM users WHERE phone IS NOT NULL;            -- Has phone number

-- Sort ascending (default)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;

-- Sort descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple sort columns
SELECT * FROM products 
ORDER BY category ASC, price DESC;

-- Sort with NULL handling
SELECT * FROM products 
ORDER BY discount NULLS LAST;  -- NULLs at the end

-- Get first N rows
SELECT * FROM products ORDER BY created_at DESC LIMIT 10;

-- Pagination with OFFSET
SELECT * FROM products 
ORDER BY id 
LIMIT 20 OFFSET 40;  -- Page 3 (rows 41-60)

-- Better pagination with keyset (cursor-based)
SELECT * FROM products 
WHERE id > 1000      -- Last seen ID
ORDER BY id 
LIMIT 20;

-- COUNT: Number of rows
SELECT COUNT(*) FROM orders;                    -- All rows
SELECT COUNT(shipped_at) FROM orders;           -- Non-NULL values only
SELECT COUNT(DISTINCT user_id) FROM orders;     -- Unique users

-- SUM: Total of numeric values
SELECT SUM(total) FROM orders WHERE status = 'completed';

-- AVG: Average
SELECT AVG(price) FROM products WHERE category = 'electronics';

-- MIN / MAX
SELECT MIN(price), MAX(price) FROM products;

-- Combined
SELECT 
    COUNT(*) AS total_orders,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value,
    MIN(total) AS smallest_order,
    MAX(total) AS largest_order
FROM orders
WHERE status = 'completed';

-- Orders per user
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id;

-- Sales by category
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    SUM(stock) AS total_inventory
FROM products
GROUP BY category;

-- Multiple grouping columns
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM created_at),
    EXTRACT(MONTH FROM created_at)
ORDER BY year, month;

-- Find users with more than 5 orders
SELECT 
    user_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Categories with average price over $100
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;

-- Combined WHERE and HAVING
SELECT 
    user_id,
    SUM(total) AS total_spent
FROM orders
WHERE status = 'completed'           -- Filter rows first
GROUP BY user_id
HAVING SUM(total) > 1000             -- Then filter groups
ORDER BY total_spent DESC;

-- Get order details with user names
SELECT 
    o.id AS order_id,
    o.total,
    o.created_at,
    u.name AS customer_name,
    u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

┌─────────────────────────────────────────────────────────────────────┐
│                           INNER JOIN                                │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│   USERS                    ORDERS                 RESULT            │
│   ┌───┬───────┐           ┌───┬─────────┐       ┌───┬───────┬────┐ │
│   │ 1 │ Alice │ ─────────▶│ 1 │ user: 1 │──────▶│ 1 │ Alice │ O1 │ │
│   │ 2 │ Bob   │ ─────────▶│ 2 │ user: 1 │──────▶│ 2 │ Alice │ O2 │ │
│   │ 3 │ Carol │ ✗         │ 3 │ user: 2 │──────▶│ 3 │ Bob   │ O3 │ │
│   └───┴───────┘           └───┴─────────┘       └───┴───────┴────┘ │
│                                                                     │
│   Carol has no orders ──▶ Not in result                            │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

-- All users, with their orders (if any)
SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

┌─────────────────────────────────────────────────────────────────────┐
│                           LEFT JOIN                                 │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│   USERS                    ORDERS                 RESULT            │
│   ┌───┬───────┐           ┌───┬─────────┐       ┌───┬───────┬────┐ │
│   │ 1 │ Alice │ ─────────▶│ 1 │ user: 1 │──────▶│ 1 │ Alice │ O1 │ │
│   │ 2 │ Bob   │ ─────────▶│ 2 │ user: 1 │──────▶│ 2 │ Alice │ O2 │ │
│   │ 3 │ Carol │ ─────────▶│ 3 │ user: 2 │──────▶│ 3 │ Bob   │ O3 │ │
│   └───┴───────┘           └───┴─────────┘       │ 4 │ Carol │NULL│ │
│                                                  └───┴───────┴────┘ │
│                                                                     │
│   Carol included with NULL for order data                          │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

-- RIGHT JOIN: All rows from right table
SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN: All rows from both tables
SELECT u.name, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Orders with user info and product details
SELECT 
    o.id AS order_id,
    u.name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.id, p.name;

-- Employees with their managers
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find products with the same category and similar prices
SELECT 
    p1.name AS product1,
    p2.name AS product2,
    p1.category,
    p1.price AS price1,
    p2.price AS price2
FROM products p1
INNER JOIN products p2 
    ON p1.category = p2.category 
    AND p1.id < p2.id  -- Avoid duplicates
    AND ABS(p1.price - p2.price) < 50;

-- All possible size/color combinations
SELECT s.name AS size, c.name AS color
FROM sizes s
CROSS JOIN colors c;

-- Generate date series with categories
SELECT 
    d.date,
    c.name AS category
FROM generate_series('2024-01-01', '2024-12-31', '1 day') AS d(date)
CROSS JOIN categories c;

-- Users who have placed orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Products priced above average
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Users with more than 5 orders
SELECT * FROM users
WHERE id IN (
    SELECT user_id 
    FROM orders 
    GROUP BY user_id 
    HAVING COUNT(*) > 5
);

-- Products with price above their category average
SELECT p1.name, p1.price, p1.category
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category  -- References outer query
);

-- Users with their latest order
SELECT 
    u.name,
    (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

-- Users who have placed at least one order
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Products that have never been ordered
SELECT p.name, p.price
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

-- Monthly revenue with growth calculation
SELECT 
    current_month.month,
    current_month.revenue,
    prev_month.revenue AS prev_revenue,
    current_month.revenue - COALESCE(prev_month.revenue, 0) AS growth
FROM (
    SELECT 
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
) current_month
LEFT JOIN (
    SELECT 
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
) prev_month ON current_month.month = prev_month.month + INTERVAL '1 month'
ORDER BY current_month.month;

WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT 
    month,
    revenue,
    AVG(revenue) OVER () AS avg_monthly_revenue,
    revenue - AVG(revenue) OVER () AS diff_from_avg
FROM monthly_revenue
ORDER BY month;

WITH 
-- Step 1: Calculate user order stats
user_stats AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(total) AS total_spent,
        AVG(total) AS avg_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),

-- Step 2: Classify users into tiers
user_tiers AS (
    SELECT 
        user_id,
        order_count,
        total_spent,
        CASE 
            WHEN total_spent >= 10000 THEN 'platinum'
            WHEN total_spent >= 5000 THEN 'gold'
            WHEN total_spent >= 1000 THEN 'silver'
            ELSE 'bronze'
        END AS tier
    FROM user_stats
),

-- Step 3: Count users per tier
tier_summary AS (
    SELECT 
        tier,
        COUNT(*) AS user_count,
        SUM(total_spent) AS tier_revenue
    FROM user_tiers
    GROUP BY tier
)

-- Final result
SELECT 
    tier,
    user_count,
    tier_revenue,
    ROUND(100.0 * tier_revenue / SUM(tier_revenue) OVER (), 2) AS revenue_pct
FROM tier_summary
ORDER BY tier_revenue DESC;

-- Employee hierarchy (org chart)
WITH RECURSIVE org_chart AS (
    -- Base case: top-level employees (no manager)
    SELECT id, name, manager_id, 1 AS level, name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' > ' || e.name
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY path;

-- Category tree with all descendants
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth, ARRAY[id] AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || c.id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    REPEAT('  ', depth) || name AS category_tree,
    depth,
    path
FROM category_tree
ORDER BY path;

-- Rank products by price within each category
SELECT 
    name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

┌──────────────────────────────────────────────────────────────────────┐
│           ROW_NUMBER vs RANK vs DENSE_RANK                           │
├──────────────────────────────────────────────────────────────────────┤
│ Product    │ Price │ ROW_NUMBER │ RANK │ DENSE_RANK                  │
├────────────┼───────┼────────────┼──────┼────────────                  │
│ Laptop     │ 1200  │     1      │  1   │     1                        │
│ Desktop    │ 1000  │     2      │  2   │     2                        │
│ Tablet     │ 1000  │     3      │  2   │     2      ◄── Same price    │
│ Phone      │  800  │     4      │  4   │     3      ◄── RANK skips 3  │
│ Earbuds    │  100  │     5      │  5   │     4                        │
└──────────────────────────────────────────────────────────────────────┘

-- Compare each order to the previous order for the same user
SELECT 
    user_id,
    id AS order_id,
    total,
    created_at,
    LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_total,
    total - LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS change,
    LEAD(total) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_total
FROM orders;

-- Calculate month-over-month revenue growth
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / 
          LAG(revenue) OVER (ORDER BY month), 2) AS growth_pct
FROM monthly_revenue;

-- Running total of sales
SELECT 
    created_at::date AS date,
    total,
    SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- 7-day moving average
SELECT 
    date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

-- Running total within each month
SELECT 
    created_at::date AS date,
    total,
    SUM(total) OVER (
        PARTITION BY DATE_TRUNC('month', created_at) 
        ORDER BY created_at
    ) AS monthly_running_total
FROM orders;

-- Compare each product to the most expensive in its category
SELECT 
    name,
    category,
    price,
    FIRST_VALUE(name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) AS top_product,
    FIRST_VALUE(price) OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) AS top_price,
    ROUND(100.0 * price / FIRST_VALUE(price) OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ), 2) AS pct_of_top
FROM products;

-- Percentile ranks
SELECT 
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- Median salary per department
SELECT DISTINCT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department) AS median_salary
FROM employees;

-- 1. Find all products in the 'Electronics' category priced under $500
-- 2. Find products with low stock (less than 10) that need reordering
-- 3. List the 5 most recently added products
-- 4. Find products with names containing 'Pro' or 'Ultra'

-- 1. Count products per category
-- 2. Find the average price per category, only for categories with avg > $200
-- 3. Find the total inventory value (price * stock) per category
-- 4. Find categories with more than 50 products

-- 1. List all orders with customer name and email
-- 2. Find customers who have never placed an order
-- 3. Get the top 10 customers by total spending
-- 4. List products that have been ordered more than 100 times

-- 1. Rank products by price within each category
-- 2. For each order, show the running total for that user
-- 3. Calculate the percentage each product contributes to its category's total stock
-- 4. Find the second-highest priced product in each category