SQL CTE and WITH — Applied Multiple CTEs and Recursion

WITH
user_totals AS (                  -- ① Sum orders by user (intermediate aggregation)
  SELECT                          -- SELECT: specify the columns to retrieve
    user_id,
    SUM(amount) AS total_amount   -- SUM: add multiple orders; AS assigns an alias
  FROM     orders                 -- FROM: source table
  GROUP BY user_id                -- GROUP BY: group at user level
),
user_ranked AS (                  -- ② Use user_totals and NTILE to assign quartiles (reference the previous CTE)
  SELECT
    user_id,
    total_amount,
    NTILE(4) OVER (               -- NTILE(4): divide all rows into four groups and assign bucket numbers
      ORDER BY total_amount DESC  -- ORDER BY ... DESC: sort purchase totals from highest to lowest
    ) AS quartile
  FROM   user_totals              -- FROM: reference the preceding user_totals CTE as a table
)

-- ③ Main query: JOIN users to user_ranked to add names
SELECT
  r.user_id,
  u.user_name,                           -- Retrieve the name from users and add it through the join
  r.total_amount,
  r.quartile
FROM       user_ranked r                 -- FROM: r is the alias (short name) for user_ranked
INNER JOIN users       u                 -- INNER JOIN: combine only rows present in both tables
        ON r.user_id = u.user_id         -- ON: join rows whose user_id values match
ORDER BY  r.quartile, r.total_amount DESC; -- ORDER BY: quartile ascending, then purchase total descending

/*
  Logical execution order:
  1. CTE user_totals: FROM orders                      → GROUP BY user_id
  2. CTE user_ranked: FROM user_totals
  3. Main: FROM user_ranked INNER JOIN users ON user_id → Add names
  4. Main: ORDER BY quartile, total_amount DESC
  */

WITH ranked_products AS (

  -- ① Assign every product a sales position within its category
  SELECT                     -- SELECT: specify the columns to retrieve
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (      -- ROW_NUMBER(): window function that assigns sequential numbers starting at 1
      PARTITION BY category  -- PARTITION BY: split by category and reset numbering for each one
      ORDER BY sales DESC, product_id  -- Break equal sales by product_id ascending
    ) AS rn                  -- AS rn: define a short alias for row_number
  FROM product_sales         -- FROM: target table
)                            -- rn cannot be used in WHERE here; a window result is unavailable within the same SELECT

-- ② Keep only CTE rows where rn is 2 or less (the top two)
SELECT
  category,
  product_name,
  sales,
  rn
FROM   ranked_products  -- FROM: reference the CTE defined above
WHERE  rn <= 2            -- WHERE: keep positions 1 and 2 in each category
ORDER BY category, rn;  -- ORDER BY: sort by category ascending, then position ascending

/*
  Logical execution order:
  1. CTE FROM product_sales      → Read every product row
  2. CTE ROW_NUMBER() OVER(...)  → Number each category in descending sales order
  3. Outer FROM ranked_products      → Reference the CTE result
  4. Outer WHERE rn <= 2             → Keep the top two rows in each category
  5. Outer ORDER BY category, rn     → Sort and return the output
  */

WITH sales_with_lag AS (

  -- ① Use LAG to add the preceding row’s sales to each row
  SELECT
    order_month,
    sales,
    LAG(sales, 1) OVER (    -- LAG(column, n): get the value n rows earlier (NULL if absent)
      ORDER BY order_month  -- ORDER BY: sort months ascending to define what “previous” means
    ) AS prev_sales         -- AS: name the column
  FROM monthly_sales        -- FROM: source table

)

-- ② Use the CTE to calculate mom_rate (month-over-month growth)
SELECT
  order_month,
  sales,
  prev_sales,
  ROUND(                     -- ROUND(value, digits): round to the specified number of digits
    (sales - prev_sales)     -- Difference between current and previous month (the increase)
    * 100.0                  -- Multiply by 100.0 for a percentage and floating-point division
    / NULLIF(prev_sales, 0)  -- NULLIF(a,b): return NULL when a equals b, preventing division by zero
  , 1)                       -- Second argument: round to one decimal place
  AS mom_rate                -- Name the calculated result mom_rate
FROM     sales_with_lag      -- FROM: reference the CTE defined above
ORDER BY order_month;        -- ORDER BY: sort months ascending

/*
  Logical execution order:
  1. CTE FROM monthly_sales      → Read all rows
  2. CTE LAG(sales,1) OVER(...)  → Add previous-month sales
  3. Outer FROM sales_with_lag       → Reference the CTE result
  4. Outer ROUND(...)                → Calculate month-over-month growth
  5. Outer ORDER BY order_month      → Sort by month
  */

-- Recursive CTEs use WITH RECURSIVE (PostgreSQL and BigQuery)
WITH RECURSIVE org_tree AS (

  -- ① Anchor member: select the CEO row that starts recursion
  SELECT                    -- SELECT: columns to retrieve
    emp_id,
    name,
    manager_id,
    0    AS depth,          -- Set the starting depth to 0 and name the column with AS
    name AS path            -- Initialize path with the employee’s own name
  FROM  employees           -- FROM: data source
  WHERE manager_id IS NULL  -- WHERE ... IS NULL: keep the row with no manager, the top level

  UNION ALL  -- UNION ALL: vertically combine SELECT results, including duplicates

  -- ② Recursive member: keep adding direct reports to org_tree
  SELECT
    e.emp_id,
    e.name,
    e.manager_id,
    t.depth + 1,                      -- Add 1 to the parent depth to descend one level
    t.path || '→' || e.name           -- ||: string concatenation operator in PostgreSQL and similar databases
  FROM       employees  e             -- FROM: e is the report side, the next level
  INNER JOIN org_tree   t             -- INNER JOIN: t is the parent row established in the preceding step
          ON e.manager_id = t.emp_id  -- ON: join where the report’s manager ID equals the parent emp_id
  WHERE t.depth < 10                   -- WHERE: limit recursion to 10 levels to prevent infinite loops

)

SELECT
  emp_id,
  name,
  manager_id,
  depth,
  path
FROM     org_tree        -- FROM: reference the completed recursive CTE
ORDER BY depth, emp_id;  -- ORDER BY: sort by depth ascending, then emp_id ascending

/*
  Logical execution order (recursive iterations):
  1. Anchor                    → Generate the CEO starting row
  2. Iteration 1               → Expand children
  3. Iteration 2               → Expand children
  4. Iteration 3               → Expand children
  5. Iteration 4               → Stop because no rows are added
  6. UNION ALL → ORDER BY  → Stack rows and sort
  */