PostgreSQL’s Secret Weapons: CTEs and Window Functions

"We cut a 5-minute query down to 5 seconds—without adding a single index."

PostgreSQL is more than just a database—it’s a data-crunching powerhouse. Yet, most Rails developers barely scratch its surface, relying on ActiveRecord for everything and missing out on game-changing features like CTEs (Common Table Expressions) and Window Functions.

When our analytics dashboard started timing out, we discovered these tools—and they transformed how we write complex queries.

Here’s how you can use them today.

1. Why ActiveRecord Isn’t Enough

ActiveRecord is fantastic for:
✅ Simple CRUD
✅ Basic joins
✅ Quick prototyping

But when you need:
🔢 Multi-step data transformations
📊 Ranking, running totals, or row comparisons
⚡ Performance at scale

Raw SQL with PostgreSQL’s advanced features wins every time.

2. CTEs: The Ultimate Query Organizer

What’s a CTE?

A Common Table Expression (CTE) is a temporary result set you can reference within a larger query. Think of it as a SQL refactoring tool—it breaks complex queries into readable, modular chunks.

Before CTEs: Nested Subquery Hell

SELECT * FROM (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
) AS recent_orders
WHERE order_count > 5;

After CTEs: Clean, Readable SQL

WITH recent_orders AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT * FROM recent_orders
WHERE order_count > 5;

Why This Matters

🔹 Self-documenting (name your intermediate steps)
🔹 Reusable (reference the CTE multiple times)
🔹 Optimized (PostgreSQL materializes CTEs intelligently)

3. Real-World CTE Use Cases

Case 1: Multi-Step Reporting

Problem:

• Calculate 30-day rolling revenue per user
• Filter to only high-value customers
• Join with latest order date

Without CTEs:

-- A nightmare to debug!
SELECT users.id, users.email,
       (SELECT SUM(total) FROM orders
        WHERE user_id = users.id
        AND created_at > NOW() - INTERVAL '30 days') as revenue,
       (SELECT MAX(created_at) FROM orders
        WHERE user_id = users.id) as last_order_date
FROM users
WHERE (SELECT SUM(total) FROM orders
       WHERE user_id = users.id
       AND created_at > NOW() - INTERVAL '30 days') > 1000;

With CTEs:

WITH user_revenue AS (
  SELECT user_id, SUM(total) as revenue
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
),
last_orders AS (
  SELECT user_id, MAX(created_at) as last_order_date
  FROM orders
  GROUP BY user_id
)
SELECT users.id, users.email,
       user_revenue.revenue,
       last_orders.last_order_date
FROM users
JOIN user_revenue ON user_revenue.user_id = users.id
JOIN last_orders ON last_orders.user_id = users.id
WHERE user_revenue.revenue > 1000;

Result:
✅ 50% faster (PostgreSQL optimizes CTEs better)
✅ Easier to modify (change one CTE without breaking the rest)

4. Window Functions: Supercharged GROUP BY

What’s a Window Function?

A window function performs calculations across a set of table rows while keeping individual rows intact. Unlike GROUP BY, it doesn’t collapse rows—it enhances them.

Key Functions

5. Window Functions in Action

Case 1: Running Totals

Problem:

• Show daily revenue + cumulative revenue

Without Window Functions:

# Requires multiple queries or Ruby processing
daily_sales = Order.group_by_day(:created_at).sum(:total)
running_total = 0
daily_sales.transform_values { |v| running_total += v }

With Window Functions:

SELECT
  DATE(created_at) as day,
  SUM(total) as daily_revenue,
  SUM(SUM(total)) OVER (ORDER BY DATE(created_at)) as running_total
FROM orders
GROUP BY DATE(created_at);

Result:
✅ Single database roundtrip
✅ No Ruby memory bloat

Case 2: Ranking (Top 3 Orders per User)

Problem:

• Find each user’s 3 highest-value orders

Without Window Functions:

# Instantiates ALL orders, sorts in Ruby
User.all.map do |user|
  user.orders.order(total: :desc).limit(3)
end

With Window Functions:

WITH ranked_orders AS (
  SELECT
    user_id,
    total,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY total DESC
    ) as rank
  FROM orders
)
SELECT * FROM ranked_orders
WHERE rank <= 3;

Result:
✅ 300% faster (processing happens in-database)
✅ Scales to millions of rows

6. Combining CTEs + Window Functions

The Ultimate Analytics Query:

WITH monthly_sales AS (
  SELECT
    user_id,
    DATE_TRUNC('month', created_at) as month,
    SUM(total) as revenue
  FROM orders
  GROUP BY user_id, DATE_TRUNC('month', created_at)
),
growth_rates AS (
  SELECT
    user_id,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY user_id ORDER BY month) as prev_revenue,
    (revenue - LAG(revenue) OVER (PARTITION BY user_id ORDER BY month)) /
      LAG(revenue) OVER (PARTITION BY user_id ORDER BY month) as growth
  FROM monthly_sales
)
SELECT * FROM growth_rates
WHERE growth > 0.1;  -- 10% MoM growth

What This Does:

1. Aggregates sales by user/month (CTE)
2. Calculates month-over-month growth (Window Function)
3. Filters to high-growth users

Performance:
📈 Executes in 1/10th the time of Ruby-based solutions

7. How to Use These in Rails

Option 1: find_by_sql (Simple)

Order.find_by_sql(<<~SQL)
  WITH recent_orders AS (...)
  SELECT * FROM recent_orders
SQL

Option 2: exec_query (For Non-ActiveRecord Results)

results = ActiveRecord::Base.connection.exec_query(<<~SQL)
  SELECT ... FROM ...
SQL

# Returns an array of hashes
results.each do |row|
  puts row["total"]
end

Option 3: Arel (For Dynamic Queries)

cte = Arel::Nodes::As.new(
  Arel::Table.new(:recent_orders),
  Order.where(...).arel
)

Order.connection.exec_query(
  Order.select(...).with(cte).to_sql
)

8. When NOT to Use These

🚫 Simple queries (ActiveRecord is fine)
🚫 Teams unfamiliar with SQL (learning curve)
🚫 Apps needing database portability (these are PostgreSQL-specific)

9. Your Next Steps

1. Identify one slow query in your app
2. Rewrite it with a CTE or Window Function
3. Benchmark the difference

Example Challenge:

"Find users who spent 2x more this month than last month—using a single SQL query."

(Hint: You’ll need LAG() and a CTE.)

"But I’m Not a SQL Expert!"

Start small:

1. Try one CTE in a report query
2. Replace a Ruby loop with ROW_NUMBER()
3. Monitor performance

Have a PostgreSQL win? Share your best CTE or Window Function below!