SQL Patterns I Use Every Week

CTEs for Readability

I almost never write subqueries anymore. CTEs make complex queries readable and debuggable:

WITH monthly_revenue AS (
  SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
growth AS (
  SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
          LAG(revenue) OVER (ORDER BY month) * 100, 1) AS growth_pct
  FROM monthly_revenue
)
SELECT * FROM growth ORDER BY month DESC;

Window Functions for Context

Window functions let you add context without collapsing your data. Running totals, rankings, and comparisons become trivial.

The 80/20 of SQL

You don't need to know every SQL function. Master these and you'll handle 80% of analytics work:

• GROUP BY + aggregate functions
• JOIN types (especially LEFT JOIN)
• Window functions (ROW_NUMBER, LAG, SUM OVER)
• CASE WHEN for conditional logic
• CTEs for organization

The rest you can look up when you need it.