codesnips
sql 190 lines · 2 tabs

Common Table Expressions (CTEs) for readable queries

Maria Garcia Feb 2026
2 tabs 
-- Basic CTE
WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(total) as lifetime_value
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT
  u.name,
  u.email,
  hvc.lifetime_value
FROM users u
INNER JOIN high_value_customers hvc ON u.id = hvc.user_id
ORDER BY hvc.lifetime_value DESC;

-- Multiple CTEs
WITH
  monthly_sales AS (
    SELECT
      DATE_TRUNC('month', order_date) as month,
      SUM(total) as total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
  ),
  monthly_costs AS (
    SELECT
      DATE_TRUNC('month', expense_date) as month,
      SUM(amount) as total_costs
    FROM expenses
    GROUP BY DATE_TRUNC('month', expense_date)
  )
SELECT
  COALESCE(s.month, c.month) as month,
  COALESCE(s.total_sales, 0) as sales,
  COALESCE(c.total_costs, 0) as costs,
  COALESCE(s.total_sales, 0) - COALESCE(c.total_costs, 0) as profit
FROM monthly_sales s
FULL OUTER JOIN monthly_costs c ON s.month = c.month
ORDER BY month;

-- CTEs referencing other CTEs
WITH
  active_users AS (
    SELECT id, name, email
    FROM users
    WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
  ),
  user_orders AS (
    SELECT
      au.id,
      au.name,
      COUNT(o.id) as order_count
    FROM active_users au
    LEFT JOIN orders o ON au.id = o.user_id
    GROUP BY au.id, au.name
  )
SELECT *
FROM user_orders
WHERE order_count > 0
ORDER BY order_count DESC;

-- CTE for data transformation pipeline
WITH
  cleaned_data AS (
    SELECT
      TRIM(LOWER(email)) as email,
      name,
      created_at
    FROM raw_users
    WHERE email IS NOT NULL
  ),
  deduped_data AS (
    SELECT DISTINCT ON (email)
      email,
      name,
      created_at
    FROM cleaned_data
    ORDER BY email, created_at DESC
  )
SELECT * FROM deduped_data;
2 files · sql Explain with highlit

CTEs improve query readability and maintainability. WITH clauses define named subqueries referenced in main query. I use CTEs to break complex queries into logical steps. Recursive CTEs handle hierarchical data—org charts, category trees, graph traversal. Multiple CTEs can reference each other. CTEs are query-scoped temporary result sets. They're more readable than nested subqueries. Some databases materialize CTEs, others inline them. MATERIALIZED hint forces materialization in PostgreSQL. CTEs excel for code clarity but may impact performance versus derived tables. Understanding when to use CTEs versus subqueries optimizes both readability and speed. CTEs are essential for complex data analysis.

Related snips

sql 
-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions
by Maria Garcia 2 tabs
ruby 
# BAD: N+1 query problem
@users = User.all
@users.each do |user|
  puts user.posts.count  # Fires query for each user!
end

ActiveRecord query optimization and N+1 prevention

ruby rails activerecord
by Sarah Mitchell 3 tabs
sql 
-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

Advanced query optimization techniques

database optimization query-performance
by Maria Garcia 2 tabs
ruby 
class CreateTopSellersMv < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      CREATE MATERIALIZED VIEW top_sellers AS
        SELECT p.id            AS product_id,
               p.name          AS product_name,

Cache-Friendly “Top N” with Materialized View Refresh

rails postgres performance
by codesnips 4 tabs
ruby 
class ReportQuery
  SQL = <<~SQL.freeze
    SELECT date_trunc('day', events.created_at) AS day,
           count(*) AS total,
           count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
    FROM events

Safe Raw SQL with exec_query + Binds

rails activerecord sql
by codesnips 2 tabs
go 
package store

import (
  "context"

  "github.com/jackc/pgx/v5"

Postgres transaction pattern with pgx: defer rollback, commit explicitly

go postgres pgx
by Leah Thompson 1 tab

Share this code

Here's the card — post it anywhere.

Common Table Expressions (CTEs) for readable queries — share card
Link copied