Advanced Queries

-- A CTE names a temporary result set, then you query it.
-- Read it top-to-bottom like a recipe instead of inside-out.

WITH cheap_products AS (        -- 1) define a named subquery
    SELECT product_name, price
    FROM products
    WHERE price < 30
)
SELECT *                         -- 2) now query it like a normal table
FROM cheap_products
ORDER BY price DESC;

-- "cheap_products" exists only for this one statement.
-- The real products table is never changed.

-- The SAME logic written as a nested subquery (harder to read)
SELECT *
FROM (
    SELECT product_name, price
    FROM products
    WHERE price < 30
) AS cheap_products
ORDER BY price DESC;

-- A CTE pulls that inner query OUT and gives it a name,
-- so you read 1 -> 2 instead of decoding it from the middle.
-- You can also reuse the name several times in one query.

-- GROUP BY COLLAPSES rows: one row PER group, detail is gone
SELECT category, COUNT(*) AS items, AVG(price) AS avg_price
FROM products
GROUP BY category;

-- You can no longer see individual products here —
-- they have been folded into one row per category.

-- A window function KEEPS every row and adds a summary column.
-- OVER () means "the window is the whole result set".

SELECT
    product_name,
    category,
    price,
    AVG(price) OVER () AS avg_all   -- same value on every row
FROM products;

-- Every one of the 6 products is still here,
-- now with the overall average price beside it.

-- ROW_NUMBER() gives each row a unique position.
-- OVER (ORDER BY ...) decides the order it counts in.

SELECT
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- It numbers 1..6 with NO ties — even if two prices were equal,
-- ROW_NUMBER would still pick a 1 and a 2 (order undefined).

-- RANK() vs ROW_NUMBER() — the difference only shows on TIES.
-- Imagine two products both priced 24.99:
--   ROW_NUMBER -> 1, 2, 3, 4 ...  (always unique, ties broken arbitrarily)
--   RANK       -> 1, 2, 2, 4 ...  (ties share a rank, then it SKIPS)
--   DENSE_RANK -> 1, 2, 2, 3 ...  (ties share a rank, NO skip)

-- Our 6 products all have different prices, so here the three
-- functions return identical numbers 1..6:
SELECT
    product_name,
    price,
    RANK()       OVER (ORDER BY price DE
...

-- PARTITION BY restarts the window for each group.
-- Here the ranking counter resets to 1 inside every category.

SELECT
    category,
    product_name,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY category        -- start a fresh count per category
        ORDER BY price DESC          -- dearest = 1 within that category
    ) AS rank_in_category
FROM products;

-- PARTITION BY is "GROUP BY for windows" — but it keeps every row
-- instead of collapsing the group into one.

-- A running (cumulative) total: SUM that grows row by row.
-- ORDER BY tells it which direction to accumulate in.

SELECT
    product_name,
    price,
    stock,
    SUM(stock) OVER (ORDER BY price DESC) AS running_stock
FROM products;

-- Each row's running_stock = its own stock PLUS every row above it
-- in the price-DESC order. Row 1 is just 45; the last row is the
-- grand total of all stock (1245).

-- LAG() looks at the PREVIOUS row; LEAD() looks at the NEXT row.
-- Perfect for "compared to the row before me" calculations.

SELECT
    product_name,
    price,
    LAG(price)  OVER (ORDER BY price DESC) AS dearer_price,
    LEAD(price) OVER (ORDER BY price DESC) AS cheaper_price
FROM products;

-- The first row has no previous row, so LAG is NULL.
-- The last row has no next row, so LEAD is NULL.
-- price - LAG(price) tells you the gap to the row above.