# PipeCraft: Advanced SQL Query Optimization Cheat Sheet

A comprehensive catalog of advanced analytical queries, window functions, table joins, and indexing structures.

---

## 📊 1. Analytical Window Functions

### `SUM(amount) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`
* **What it does:** Computes a running total of the `amount` column for each user partition, sorted by date.
* **Why it's used:** Calculates cumulative spending trends, running balances, or daily growth curves.

### `DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)`
* **What it does:** Assigns a rank to each employee within their department based on salary. If two employees share the same salary, they get the same rank number without skipping the next consecutive rank index.

---

## 🛢️ 2. Core Joining & CTEs

### `INNER JOIN` vs `LEFT JOIN`
* **INNER JOIN:** Returns only the matching rows present in both tables.
* **LEFT JOIN:** Returns all rows from the left table, and matching rows from the right table. If no match exists, it fills nulls.

### `WITH cte_name AS (...)` (Common Table Expression)
* **What it does:** Defines a temporary, named result set that can be referenced like a table within a single query.
* **Why it's used:** Break down complex, nested subqueries into readable, modular steps.

---

## ⚡ 3. Schema Control & Speed Optimization

### `CREATE INDEX idx_name ON table(col1, col2) INCLUDE(col3)`
* **What it does:** Creates a composite b-tree index on col1 and col2, storing col3 data directly inside the leaf nodes.
* **Why it's used:** Allows query engines to perform rapid Index Seeks instead of slow, expensive full table scans.

### Slowly Changing Dimension (SCD Type 2) Update
* **What it does:**
  1. `UPDATE active_flag = false` on current customer row.
  2. `INSERT` new customer profile row with updated level and `active_flag = true`.
* **Why it's used:** Keeps complete historical attributes of customer movements over time to ensure historical sales reporting remains accurate.