Advanced SQL Techniques for Modern Applications

Learn powerful SQL techniques for building high-performance database queries and optimizations.

Window Functions

Window functions are powerful tools for analyzing data across rows:

WITH monthly_sales AS (
  SELECT 
    date_trunc('month', order_date) as month,
    product_id,
    SUM(amount) as total_sales,
    -- Calculate running total per product
    SUM(SUM(amount)) OVER (
      PARTITION BY product_id 
      ORDER BY date_trunc('month', order_date)
    ) as running_total,
    -- Calculate percent of total sales
    SUM(amount) / SUM(SUM(amount)) OVER (
      PARTITION BY product_id
    ) * 100 as percentage_of_total
  FROM orders
  GROUP BY 1, 2
)
SELECT * FROM monthly_sales;

Common Table Expressions (CTEs)

Use CTEs to break down complex queries into manageable pieces:

WITH active_users AS (
  SELECT user_id
  FROM user_sessions
  WHERE last_active > NOW() - INTERVAL '30 days'
),
user_purchases AS (
  SELECT 
    user_id,
    COUNT(*) as purchase_count,
    SUM(amount) as total_spent
  FROM orders
  WHERE order_date > NOW() - INTERVAL '90 days'
  GROUP BY user_id
)
SELECT 
  u.name,
  COALESCE(up.purchase_count, 0) as purchases,
  COALESCE(up.total_spent, 0) as spent
FROM users u
JOIN active_users au ON u.id = au.user_id
LEFT JOIN user_purchases up ON u.id = up.user_id;

Performance Optimization

Indexing Strategies

-- Create composite index for common query patterns
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- Create partial index for active items
CREATE INDEX idx_active_items ON products (id)
WHERE status = 'active';

-- Create covering index for frequent queries
CREATE INDEX idx_users_email_name ON users (email)
INCLUDE (first_name, last_name);

Query Optimization

-- Use EXISTS instead of IN for better performance
SELECT *
FROM orders o
WHERE EXISTS (
  SELECT 1 
  FROM premium_users pu 
  WHERE pu.user_id = o.user_id
);

-- Use EXPLAIN ANALYZE to understand query performance
EXPLAIN ANALYZE
SELECT 
  p.category,
  COUNT(*) as order_count,
  AVG(o.amount) as avg_amount
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= NOW() - INTERVAL '1 month'
GROUP BY p.category
HAVING COUNT(*) > 100;

Working with JSON

Modern applications often need to work with JSON data:

-- Create a table with JSONB column
CREATE TABLE user_preferences (
  user_id UUID PRIMARY KEY,
  preferences JSONB NOT NULL
);

-- Query JSON data
SELECT 
  user_id,
  preferences->>'theme' as theme,
  preferences->'notifications'->>'email' as email_notifications
FROM user_preferences
WHERE preferences @> '{"theme": "dark"}';

-- Update JSON data
UPDATE user_preferences
SET preferences = preferences || 
  '{"notifications": {"email": true, "push": false}}'::jsonb
WHERE user_id = 'some-uuid';

Materialized Views

Use materialized views for complex reporting queries:

CREATE MATERIALIZED VIEW monthly_sales_report AS
SELECT 
  date_trunc('month', order_date) as month,
  p.category,
  COUNT(*) as order_count,
  SUM(o.amount) as total_sales,
  AVG(o.amount) as avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY 1, 2
WITH DATA;

-- Create unique index for faster refreshes
CREATE UNIQUE INDEX idx_monthly_sales_report 
ON monthly_sales_report (month, category);

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_report;