JOIN Master

SELECT p.name, p.price, cat.name AS category
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
ORDER BY p.price DESC
LIMIT 10;

SELECT
    p.name AS product,
    cat.name AS category,
    s.company_name AS supplier
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
INNER JOIN suppliers AS s ON p.supplier_id = s.id
ORDER BY p.name
LIMIT 20;

SELECT p.name, p.price
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id
WHERE r.id IS NULL
ORDER BY p.price DESC;

SELECT c.name, c.created_at
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.id IS NULL
ORDER BY c.created_at;

SELECT
    c.name, c.grade,
    COUNT(o.id) AS order_count,
    ROUND(SUM(o.total_amount), 2) AS total_spent
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status NOT IN ('cancelled')
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 10;

SELECT
    o.order_number,
    c.name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
ORDER BY o.ordered_at DESC
LIMIT 5;

SELECT
    cat.name AS category,
    SUM(oi.quantity) AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items AS oi
INNER JOIN orders AS o ON oi.order_id = o.id
INNER JOIN products AS p ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE o.status NOT IN ('cancelled')
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT
    p.name,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(r.id) AS review_count
FROM products AS p
INNER JOIN reviews AS r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING COUNT(r.id) >= 5
ORDER BY avg_rating DESC
LIMIT 15;

SELECT
    ROUND(AVG(JULIANDAY(sh.delivered_at) - JULIANDAY(o.ordered_at)), 1) AS avg_delivery_days
FROM shipping AS sh
INNER JOIN orders AS o ON sh.order_id = o.id
WHERE sh.status = 'delivered'
  AND sh.delivered_at IS NOT NULL;

SELECT
    carrier,
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
    ROUND(100.0 * SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*), 1) AS delivery_rate
FROM shipping
GROUP BY carrier
ORDER BY total DESC;

SELECT
    s.company_name,
    COUNT(p.id) AS product_count,
    ROUND(AVG(p.price), 2) AS avg_price,
    ROUND(MAX(p.price), 2) AS max_price
FROM suppliers AS s
INNER JOIN products AS p ON s.id = p.supplier_id
GROUP BY s.id, s.company_name
ORDER BY product_count DESC;

SELECT
    c.name,
    c.grade,
    MAX(o.ordered_at) AS last_order_date,
    o.total_amount AS last_order_amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status NOT IN ('cancelled')
GROUP BY c.id, c.name, c.grade
ORDER BY last_order_date DESC
LIMIT 15;

SELECT
    s.id,
    s.name       AS staff_name,
    s.department,
    s.role,
    m.name       AS manager_name,
    m.department AS manager_department
FROM staff AS s
LEFT JOIN staff AS m ON s.manager_id = m.id
ORDER BY s.department, s.name;

SELECT
    p.name        AS discontinued_product,
    p.price       AS old_price,
    p.discontinued_at,
    succ.name     AS successor_product,
    succ.price    AS new_price,
    ROUND(succ.price - p.price, 0) AS price_diff
FROM products AS p
INNER JOIN products AS succ ON p.successor_id = succ.id
WHERE p.discontinued_at IS NOT NULL
ORDER BY p.discontinued_at DESC
LIMIT 20;

SELECT
    p.name        AS product_name,
    q.content     AS question,
    c.name        AS asked_by,
    q.created_at  AS asked_at,
    a.content     AS answer,
    s.name        AS answered_by,
    a.created_at  AS answered_at
FROM product_qna AS q
INNER JOIN products AS p ON q.product_id = p.id
LEFT JOIN customers AS c ON q.customer_id = c.id
LEFT JOIN product_qna AS a ON a.parent_id = q.id
LEFT JOIN staff AS s ON a.staff_id = s.id
WHERE q.parent_id IS NULL
ORDER BY q.created_at DESC
LIMIT 20;

SELECT
    cal.date_key,
    cal.day_name,
    cal.is_weekend,
    cal.is_holiday,
    cal.holiday_name
FROM calendar AS cal
LEFT JOIN (
    SELECT DISTINCT SUBSTR(ordered_at, 1, 10) AS order_date
    FROM orders
) AS od ON cal.date_key = od.order_date
WHERE cal.year = 2024
  AND od.order_date IS NULL
ORDER BY cal.date_key;

SELECT
    p.name   AS product_name,
    p.brand,
    p.price,
    cat.name AS category
FROM product_tags AS pt
INNER JOIN tags       AS t   ON pt.tag_id     = t.id
INNER JOIN products   AS p   ON pt.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE t.name = 'Gaming'
  AND p.is_active = 1
ORDER BY p.price DESC;