WITH ranked_scores AS (
  -- ① Rank within each game using DENSE_RANK
  SELECT
    game_id,
    player_name,
    score,
    DENSE_RANK() OVER (
      PARTITION BY game_id  -- partition into groups by game
      ORDER BY score DESC   -- rank by score descending
    ) AS rank
  FROM game_scores
)

-- ② filter for players ranked 3 or higher
SELECT
  game_id,
  player_name,
  score,
  rank
FROM ranked_scores
WHERE rank <= 3               -- include ties through third place
ORDER BY game_id, rank, score DESC;

/*
  【Evaluation order】
  1. inside the CTE → evaluate the window function (DENSE_RANK; row count is preserved)
  2. outer query  → rows(rank in filter)
  3. outer query  → sort and output
*/

WITH
monthly_agg AS (                 -- ① salesis monthonlyaggregation
  SELECT
    order_month,
    SUM(amount) AS total_sales,
    COUNT(*)    AS order_count
  FROM orders
  GROUP BY order_month
)

-- ② allmonth masterto salesaggregation(LEFT JOIN)
SELECT
  m.month,
  COALESCE(a.total_sales, 0) AS total_sales, -- NULL0to 
  COALESCE(a.order_count, 0) AS order_count
FROM month_master m
LEFT JOIN monthly_agg a
  ON m.month = a.order_month                 -- salesis monthis is NULLto 
ORDER BY m.month;

/*
  【Evaluation order】
  1. inside the CTE → define the CTE(monthper aggregation)
  2. outer query  → (allrows)
  3. outer query  → (COALESCE)
  4. outer query  → sort and output
*/

WITH purchase_ranked AS (
  -- ① add first- and latest-purchase details to each row
  SELECT
    user_id,
    order_date,
    product,
    FIRST_VALUE(order_date) OVER (                              --  timespurchasedetails
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_date,
    FIRST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_product,
    LAST_VALUE(order_date) OVER (                               -- latest-purchase details (ROWS BETWEEN covers all rows)
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_date,
    LAST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_product
  FROM purchase_history
)

-- ② keep one row per user
SELECT DISTINCT
  user_id,
  first_date,
  first_product,
  last_date,
  last_product
FROM purchase_ranked
ORDER BY user_id;

/*
  【Evaluation order】
  1. inside the CTE → read the rows
  2. inside the CTE → Window Functions(FIRST/LAST_VALUE、rowsis )
  3. outer query  → remove duplicates (DISTINCT)
  4. outer query  → sort and output
*/

WITH RECURSIVE category_tree AS (
  -- ═══ Anchor: get the root node ═══
  SELECT
    id,
    parent_id,
    name,
    0    AS depth,
    name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- ═══ recursive member: repeatedly get children from each parent ═══
  SELECT
    c.id,
    c.parent_id,
    c.name,
    t.depth + 1,               -- go one level deeper
    t.path || ' > ' || c.name  -- append the child name to the parent path
  FROM categories c
  INNER JOIN category_tree t
    ON c.parent_id = t.id      -- join the child parent_id to the parent id
)

-- output all results after recursion completes
SELECT
  id,
  name,
  depth,
  path
FROM category_tree
ORDER BY path;

/*
  【Evaluation order(Recursive CTEof ー)】
  1. Anchor()
  2. first recursion(expand)
  3. second recursion(expand)
  4. stop when no rows are added
  5. evaluate the outer query   → with ORDER BY sort and output
*/