A correlated subquery is a form in which the inner subquery references the value of the row currently being processed by the outer query. Each time the outer query processes a row, the inner query runs. Use it for patterns that contrast a per-group aggregate with each row, such as "compare each order against that user's average."

SELECT o.order_id, o.amount
FROM   orders o
WHERE  o.amount > (
  SELECT AVG(o2.amount)
  FROM   orders o2
  WHERE  o2.user_id = o.user_id  -- references the outer o.user_id (correlation condition)
);

From the orders table, retrieve only orders that exceed each user's average order amount. The columns to retrieve are order_id, user_id, amount, user_avg (attaching that user's average), ordered by user_id ascending and, within the same user, by amount descending.

Expected output (only orders exceeding each user's average):

※ user2 and user4, who have only one order each, have average = their own amount, so the "exceeds" condition is always FALSE. Only 102 (12000>10000) passes for user1, and only 104 (9500>7750) passes for user3.

SELECT
  o.order_id,
  o.user_id,
  o.amount,
  (SELECT ROUND(AVG(o2.amount))    -- correlated SQ: returns that user's average
   FROM   orders o2
   WHERE  o2.user_id = o.user_id    -- references the outer o.user_id (correlation condition)
  ) AS user_avg
FROM   orders o
WHERE  o.amount > (               -- compare against each user's average to filter
  SELECT AVG(o2.amount)
  FROM   orders o2
  WHERE  o2.user_id = o.user_id   -- the same correlated SQ used in WHERE
)
ORDER BY o.user_id, o.amount DESC;

/*
  Execution order (logical evaluation order in SQL):
  1. FROM orders o                  → process every row one at a time
  2. WHERE o.amount > (correlated SQ) → run the subquery for each row
  3. SELECT ... user_avg            → attach each user's average via the correlated SQ
  4. ORDER BY user_id, amount DESC  → sort
  */

To "retrieve the highest-priced product in each category," a useful pattern is to first aggregate the MAX price per category and then JOIN that result table with the original table. You can also write it with a correlated subquery, but a derived table + JOIN is more efficient on large data.

SELECT p.*
FROM   products p
INNER JOIN (
  SELECT category, MAX(price) AS max_price  -- aggregate MAX in a derived table
  FROM   products
  GROUP BY category
) AS cat_max
  ON  p.category = cat_max.category
  AND p.price    = cat_max.max_price;   -- only rows matching the MAX value

From the products table, retrieve the highest-priced product in each category. The columns to retrieve are product_id, name, category, price, ordered by price descending.

Expected output (only the highest-priced product per category, price descending):

※ service max=9800 (Plan A) / content max=5500 (Template X) / option max=3500 (API Add-on)

SELECT
  p.product_id,
  p.name,
  p.category,
  p.price
FROM   products p
INNER JOIN (                       -- place the derived table on the right side of the JOIN
  SELECT
    category,
    MAX(price) AS max_price       -- aggregate the highest price per category
  FROM   products
  GROUP BY category
) AS cat_max                      -- always give the derived table an alias
  ON  p.category = cat_max.category  -- category matches
  AND p.price    = cat_max.max_price  -- and price matches the MAX value
ORDER BY p.price DESC;

/*
  Execution order (logical evaluation order in SQL):
  1. Evaluate the derived table
  2. FROM products p         → read all of products
  3. INNER JOIN ... ON ...   → join only rows where category matches and price = MAX value
  4. SELECT p.product_id...  → select the needed columns
  5. ORDER BY p.price DESC   → sort by price descending
  */

A CTE (Common Table Expression) is defined with WITH name AS (subquery) and is a "temporary named table" that subsequent queries can reference by name. It is equivalent to a FROM-clause subquery (derived table) in many cases, but is superior in readability and reusability.

-- ■ Subquery version (deeply nested)
SELECT * FROM (
  SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status
) AS sub WHERE cnt >= 2;

-- ■ CTE version (flat and easy to read)
WITH sub AS (
  SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status
)
SELECT * FROM sub WHERE cnt >= 2;

Using the orders table, aggregate the order count (order_cnt) and total amount (total_amount) per status, and return only the statuses whose order_cnt is 2 or more, by total_amount descending. Answer with both the subquery version and the CTE version.

Expected output (order_cnt ≥ 2, total_amount descending):

※ pending (1) and cancelled (1) are excluded because order_cnt < 2. Only completed (4 orders: 8000+12000+9500+6000=35500) remains.

-- ■ Subquery version (derived table)
SELECT
  status,
  order_cnt,
  total_amount
FROM (
  SELECT
    status,
    COUNT(*)    AS order_cnt,   -- aggregate the count
    SUM(amount) AS total_amount -- aggregate the total amount
  FROM   orders
  GROUP BY status
) AS summary                    -- always give the derived table an alias
WHERE  order_cnt >= 2           -- filter by the post-aggregation count
ORDER BY total_amount DESC;

-- ■ CTE version (WITH clause) — returns exactly the same result as above
WITH summary AS (               -- CTE definition (name: summary)
  SELECT
    status,
    COUNT(*)    AS order_cnt,
    SUM(amount) AS total_amount
  FROM   orders
  GROUP BY status
)
SELECT                           -- reference by CTE name (just like an ordinary table)
  status,
  order_cnt,
  total_amount
FROM   summary
WHERE  order_cnt >= 2
ORDER BY total_amount DESC;

/*
  Execution order (the same logical evaluation order for both):
  1. Evaluate the aggregation query (SQ or CTE)  → aggregate per status
  2. FROM summary                → reference the aggregated result
  3. WHERE order_cnt threshold   → filter by the condition
  4. SELECT ...                  → select the 3 columns
  5. ORDER BY total_amount DESC  → total descending
  */

Real-world SQL often combines several kinds of subqueries. In this problem you combine ① WHERE IN + HAVING (identify high-value users), ② a scalar SQ (correlated) (get the latest order date), and ③ a scalar SQ (correlated) (attach the order count) into a single query.

Write a query that satisfies the following conditions:
① From the orders table, retrieve only the single latest order of each user whose total completed amount is 10,000 or more
② The columns to retrieve are name (from users), order_id, amount, ordered_at, total_orders (that user's total number of orders)
③ Sort by total_orders descending, and by amount descending for the same count.

Expected output (latest order of users with total ≥ 10000, total_orders descending):

※ user1 (completed total 20000≥10000) / user3 (completed total 15500≥10000) → latest order of each user: user1→102 (2024-05-20) / user3→105 (2024-05-25)

SELECT
  u.name,
  o.order_id,
  o.amount,
  o.ordered_at,
  (SELECT COUNT(*)                    -- correlated scalar SQ: attach the total order count
   FROM   orders o3
   WHERE  o3.user_id = u.user_id
  ) AS total_orders
FROM   users u
INNER JOIN orders o
  ON  u.user_id = o.user_id
WHERE  u.user_id IN (              -- ① WHERE IN + HAVING: identify high-value users
  SELECT user_id
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
  HAVING SUM(amount) >= 10000     -- list of user_ids whose completed total is 10000 or more
)
  AND o.ordered_at = (            -- ② correlated scalar SQ: only the row matching the latest order date
    SELECT MAX(o2.ordered_at)
    FROM   orders o2
    WHERE  o2.user_id = u.user_id
  )
ORDER BY total_orders DESC, o.amount DESC;

/*
  Execution order (logical evaluation order in SQL):
  1. Evaluate the IN subquery                       → generate the list via a HAVING aggregation
  2. FROM users INNER JOIN orders               → join on user_id
  3. WHERE user_id IN (...)                     → narrow to the matching users
  4. AND o.ordered_at = (correlated scalar SQ)  → narrow to each user's latest order
  5. SELECT (correlated scalar SQ)              → attach the total order count
  6. ORDER BY total_orders DESC, o.amount DESC  → count → amount descending
  */