The Ultimate SQL Patterns Cheatsheet
From Foundations to Advanced Mastery

-- Method A: Repeat the underlying expression
SELECT 
  product_id,
  gross_sales - discount_amount AS net_rev
FROM sales
WHERE (gross_sales - discount_amount) > 1000;

-- Method B: Encapsulate with a CTE (Clean & Readable)
WITH calculated_sales AS (
  SELECT 
    product_id,
    gross_sales - discount_amount AS net_rev
  FROM sales
)
SELECT * 
FROM calculated_sales 
WHERE net_rev > 1000;

-- Approach 1: NOT EXISTS (Null-safe, optimizer-friendly)
SELECT e.employee_name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 
  FROM departments d 
  WHERE d.manager_id = e.employee_id
);

-- Approach 2: Explicit NULL filter inside NOT IN
SELECT employee_name
FROM employees
WHERE employee_id NOT IN (
  SELECT manager_id 
  FROM departments 
  WHERE manager_id IS NOT NULL
);

-- Approach 1: Put right-table filter directly in the ON clause
SELECT 
  c.customer_name, 
  o.order_id, 
  o.amount
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id 
 AND o.status = 'COMPLETED';

-- Approach 2: If filtering in WHERE, explicitly account for NULLs
SELECT 
  c.customer_name, 
  o.order_id, 
  o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL OR o.status = 'COMPLETED';

-- 1. Truncate to Month / Week grain
SELECT 
  date_trunc('month', order_date) AS order_month,
  date_trunc('week', order_date)  AS order_week,
  SUM(amount) AS monthly_revenue
FROM orders
GROUP BY 1, 2;

-- 2. Interval Arithmetic (Adding / Subtracting Time)
SELECT 
  order_id,
  order_date,
  order_date + INTERVAL 30 DAY   AS warranty_expiry,
  order_date - INTERVAL 1 MONTH  AS previous_month_benchmark
FROM orders;

-- 3. Calculate Date Difference in Days
SELECT 
  customer_id,
  date_diff('day', signup_date, first_purchase_date) AS days_to_activate
FROM user_lifecycle;

SELECT 
  student_name,
  score,
  -- 1, 2, 3, 4 (Always unique, arbitrary tie breaker if unconstrained)
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  
  -- 1, 2, 2, 4 (Leaves gaps after ties — classic competition rank)
  RANK()       OVER (ORDER BY score DESC) AS rank_pos,
  
  -- 1, 2, 2, 3 (No gaps — dense rank)
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_pos,
  
  -- 1, 1, 2, 2 (Divides rows into N roughly equal quartiles/buckets)
  NTILE(2)     OVER (ORDER BY score DESC) AS quartile
FROM exam_results;

SELECT 
  recorded_date,
  daily_revenue,
  
  -- Cumulative YTD Total (from start of partition to today)
  SUM(daily_revenue) OVER (
    ORDER BY recorded_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue,

  -- 7-Day Rolling Moving Average (Today + Past 6 Days = 7 rows)
  AVG(daily_revenue) OVER (
    ORDER BY recorded_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg,

  -- Centered 3-Day Window (Yesterday + Today + Tomorrow)
  AVG(daily_revenue) OVER (
    ORDER BY recorded_date 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_3d_avg
FROM daily_metrics;

WITH ordered_logins AS (
  -- Step 1: Deduplicate distinct login dates per user
  SELECT DISTINCT 
    user_id, 
    CAST(login_time AS DATE) AS login_date
  FROM user_logins
),
numbered_logins AS (
  -- Step 2: Subtract ROW_NUMBER() from login_date to create an island group key
  SELECT 
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL 1 DAY) AS island_id
  FROM ordered_logins
)
-- Step 3: Group by user and island_id to measure streak length
SELECT 
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS consecutive_days
FROM numbered_logins
GROUP BY user_id, island_id
HAVING COUNT(*) >= 3
ORDER BY user_id, streak_start;

WITH event_deltas AS (
  -- Step 1: Find previous event timestamp per user
  SELECT 
    user_id,
    event_time,
    page_url,
    LAG(event_time) OVER (
      PARTITION BY user_id 
      ORDER BY event_time
    ) AS prev_event_time
  FROM clickstream_events
),
session_flags AS (
  -- Step 2: Flag 1 if gap > 30 minutes or first event, else 0
  SELECT 
    user_id,
    event_time,
    page_url,
    CASE 
      WHEN prev_event_time IS NULL 
        OR date_diff('minute', prev_event_time, event_time) > 30 THEN 1 
      ELSE 0 
    END AS is_new_session
  FROM event_deltas
),
session_assigned AS (
  -- Step 3: Cumulative sum of flags forms unique session IDs
  SELECT 
    user_id,
    event_time,
    page_url,
    SUM(is_new_session) OVER (
      PARTITION BY user_id 
      ORDER BY event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_number
  FROM session_flags
)
SELECT 
  user_id,
  session_number,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*) AS total_pageviews
FROM session_assigned
GROUP BY user_id, session_number;

-- Method 1: Standard ANSI SQL (Works everywhere: Postgres, BigQuery, Snowflake)
WITH ranked_orders AS (
  SELECT 
    order_id,
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id 
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
)
SELECT order_id, customer_id, order_date, amount
FROM ranked_orders
WHERE rn = 1;

-- Method 2: Modern DuckDB / Snowflake QUALIFY Clause (No CTE required!)
SELECT 
  order_id,
  customer_id,
  order_date,
  amount
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id 
  ORDER BY order_date DESC, order_id DESC
) = 1;

-- Approach 1: LEFT JOIN with IS NULL check (High performance on indexed keys)
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o 
  ON c.customer_id = o.customer_id 
 AND o.order_date >= '2024-01-01'
WHERE c.signup_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND o.order_id IS NULL;

-- Approach 2: NOT EXISTS (Clear correlated subquery intent)
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.signup_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.order_date >= '2024-01-01'
  );

-- 1. Pivoting: Long to Wide using Conditional Aggregation
SELECT 
  product_id,
  SUM(CASE WHEN quarter = 'Q1' THEN sales_amount ELSE 0 END) AS q1_sales,
  SUM(CASE WHEN quarter = 'Q2' THEN sales_amount ELSE 0 END) AS q2_sales,
  SUM(CASE WHEN quarter = 'Q3' THEN sales_amount ELSE 0 END) AS q3_sales,
  SUM(CASE WHEN quarter = 'Q4' THEN sales_amount ELSE 0 END) AS q4_sales
FROM quarterly_sales
GROUP BY product_id;

-- 2. Modern DuckDB PIVOT Syntax
PIVOT quarterly_sales 
ON quarter 
USING SUM(sales_amount) 
GROUP BY product_id;

-- 3. Unpivoting: Wide to Long using UNION ALL or UNPIVOT
SELECT product_id, 'Q1' AS quarter, q1_sales AS sales FROM wide_sales
UNION ALL
SELECT product_id, 'Q2' AS quarter, q2_sales AS sales FROM wide_sales
UNION ALL
SELECT product_id, 'Q3' AS quarter, q3_sales AS sales FROM wide_sales;

WITH RECURSIVE org_hierarchy AS (
  -- Anchor Member: Find the CEO / Top-level roots (manager_id IS NULL)
  SELECT 
    employee_id,
    employee_name,
    manager_id,
    1 AS hierarchy_level,
    CAST(employee_name AS VARCHAR) AS management_path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive Member: Join employees to their managers in the hierarchy
  SELECT 
    e.employee_id,
    e.employee_name,
    e.manager_id,
    h.hierarchy_level + 1,
    h.management_path || ' -> ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
  employee_id,
  employee_name,
  hierarchy_level,
  management_path
FROM org_hierarchy
ORDER BY hierarchy_level, employee_id;

-- 1. Unnesting Arrays to Rows
SELECT 
  article_id,
  tag_element
FROM blog_posts,
UNNEST(tags_array) AS t(tag_element);

-- 2. DuckDB / Postgres JSON extraction
SELECT 
  event_id,
  -- Extract raw text value
  raw_payload->>'user_id' AS user_id,
  -- Extract nested JSON object or int
  CAST(raw_payload->'device'->>'screen_width' AS INTEGER) AS screen_width
FROM raw_logs;

-- Optimizer uses partition pruning & binary range scans!
SELECT * 
FROM transactions
WHERE transaction_date >= '2024-01-01' 
  AND transaction_date < '2025-01-01'
  AND country_code IN ('US', 'us');