### 🚀 Stop Using Subqueries for YOY! Ever spent hours debugging slow self-joins or nested subqueries just to calculate **Year-Over-Year (YOY) growth**? Stop! There is a cleaner, faster way. By using the **`LAG()` window function**, you can retrieve historical records from the previous year in exactly **one line of code** without duplicating table scans! #### The 1-Line Trick: ```sql -- Step 1: Aggregate revenue by year WITH yearly_revenue AS ( SELECT STRFTIME('%Y', order_date) AS yr, SUM(total_amount) AS rev FROM orders GROUP BY yr ) -- Step 2: Use LAG() to fetch previous year's revenue in 1 line! SELECT yr, rev, ROUND(((rev - LAG(rev) OVER (ORDER BY yr)) / LAG(rev) OVER (ORDER BY yr)) * 100, 2) AS yoy_pct FROM yearly_revenue; ``` #### Why This Helps You: * **Zero Self-Joins:** Standard self-joins require a heavy duplicate scan. `LAG()` operates in a single, high-performance pass. * **Instant Auditing:** Everything is aggregated in one clean, readable, human-friendly block!