SQL Subqueries — Applied Correlation and Derived Tables

SELECT
  user_id,
  ROUND(AVG(amount)) AS avg_amount  -- ROUND to an integer for display
FROM   orders
WHERE  status = 'completed'         -- pre-aggregation filter (per row)
GROUP BY user_id                    -- group by user
HAVING AVG(amount) > (              -- post-aggregation filter (per group)
  SELECT AVG(amount)                -- scalar SQ: pre-compute the completed overall average
  FROM   orders
  WHERE  status = 'completed'       -- match the same condition as the outer query
)
ORDER BY avg_amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Scalar SQ pre-evaluation    → fix the completed average as the threshold
  2. FROM orders                 → read all rows
  3. WHERE status='completed'    → narrow to completed
  4. GROUP BY user_id            → aggregate by user
  5. HAVING AVG(amount) > thresh → keep groups above the average
  6. SELECT ROUND(AVG(amount))   → output the average
  7. ORDER BY avg_amount DESC    → descending
  */

SELECT
  u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (                   -- condition ①: has at least one completed order
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id
    AND  o.status  = 'completed'  -- completed only
)
  AND NOT EXISTS (                -- condition ②: has no review at all
  SELECT 1
  FROM   reviews r
  WHERE  r.user_id = u.user_id    -- confirm no row exists in reviews
)
ORDER BY u.user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                      → process one row at a time
  2. EXISTS (completed order)          → decide "has a completed one"
  3. AND NOT EXISTS (review absent)    → decide "has no review"
  4. SELECT u.user_id, u.name, u.plan  → select passing rows
  5. ORDER BY u.user_id                → user_id ascending
  */

SELECT
  u.user_id,
  u.name,
  u.plan,
  s.order_count,
  s.total_amount,
  s.avg_amount
FROM   users u
INNER JOIN (                          -- INNER JOIN the aggregate result as a derived table
  SELECT
    user_id,
    COUNT(*)           AS order_count, -- order count
    SUM(amount)         AS total_amount,-- total amount
    ROUND(AVG(amount))  AS avg_amount   -- average amount (rounded to integer)
  FROM   orders
  WHERE  status = 'completed'          -- narrow to completed before aggregating
  GROUP BY user_id
) AS s ON u.user_id = s.user_id      -- the AS alias is required; specify the join key with ON
ORDER BY s.total_amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the derived table (s)
  2. FROM users u                  → read all 5 rows of users
  3. INNER JOIN s ON user_id       → user5 has no row in s, so it is auto-excluded from the join
  4. SELECT u.*, s.*               → select the needed columns
  5. ORDER BY s.total_amount DESC  → total amount descending
  */

SELECT
  product_id, name, category, price
FROM   products
WHERE  category = (              -- extract products matching the best-selling category name
  SELECT p2.category            -- ② get the category name (scalar SQ)
  FROM   products p2
  INNER JOIN order_items oi ON p2.product_id = oi.product_id
  INNER JOIN orders o       ON oi.order_id   = o.order_id
  WHERE  o.status = 'completed'  -- completed orders only
  GROUP BY p2.category
  HAVING SUM(oi.qty) = (       -- per-category total qty equals the maximum value
    SELECT MAX(cat_qty)          -- ① fix the maximum total qty first (innermost SQ)
    FROM (
      SELECT SUM(oi2.qty) AS cat_qty -- per-category total qty
      FROM   products p3
      INNER JOIN order_items oi2 ON p3.product_id = oi2.product_id
      INNER JOIN orders o2      ON oi2.order_id  = o2.order_id
      WHERE  o2.status = 'completed'
      GROUP BY p3.category
    ) AS cat_totals              -- always give the derived table an alias
  )
)
ORDER BY price DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the innermost SQ (derived table cat_totals)
  2. 2nd-level SQ: MAX(cat_qty) = 6 (fix the maximum value)
  3. Middle SQ: get the category where HAVING SUM(oi.qty) = 6
  4. Outer WHERE: narrow to products.category = 'electronics'
  5. SELECT ...           → select the needed columns
  6. ORDER BY price DESC  → price descending
  */