SQL query patterns, best practices, and templates. Use when writing queries, exploring databases, or analyzing data. Triggers: SQL, database queries, data analysis, schema exploration, "write a query"
Common patterns and best practices for database work.
-- List all tables
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
-- Describe table structure
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'your_table';
-- Get foreign key relationships
SELECT
tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
-- Table sizes and row counts
SELECT
schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- List tables
SHOW TABLES;
-- Describe table
DESCRIBE table_name;
SHOW CREATE TABLE table_name;
-- Table sizes
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE();
-- Sample data
SELECT * FROM table_name LIMIT 10;
-- Row count
SELECT COUNT(*) FROM table_name;
-- Column value distribution
SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name
ORDER BY count DESC
LIMIT 20;
-- Date range of data
SELECT
MIN(created_at) as earliest,
MAX(created_at) as latest,
COUNT(*) as total_records
FROM table_name;
-- Check for NULLs
SELECT
COUNT(*) as total,
COUNT(column_name) as non_null,
COUNT(*) - COUNT(column_name) as nulls
FROM table_name;
-- Find duplicates
SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
-- Group by with multiple aggregates
SELECT
category,
COUNT(*) as count,
AVG(price) as avg_price,
SUM(revenue) as total_revenue,
MIN(price) as min_price,
MAX(price) as max_price
FROM products
GROUP BY category
ORDER BY total_revenue DESC;
-- Daily counts
SELECT
DATE_TRUNC('day', created_at) as date,
COUNT(*) as daily_count
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY date
ORDER BY date;
-- Month-over-month comparison
SELECT
DATE_TRUNC('month', created_at) as month,
COUNT(*) as orders,
SUM(total_amount) as revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '12 months'
GROUP BY month
ORDER BY month;
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(created_at)) as cohort_month
FROM orders
GROUP BY user_id
)
SELECT
cohort_month,
COUNT(*) as cohort_size
FROM user_cohorts
GROUP BY cohort_month
ORDER BY cohort_month;
-- Running total
SELECT
date,
revenue,
SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_revenue;
-- Rank within groups
SELECT
category,
product_name,
revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
FROM products;
-- Moving average
SELECT
date,
value,
AVG(value) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7d
FROM metrics;
WITH
active_users AS (
SELECT DISTINCT user_id
FROM user_activities
WHERE activity_date >= CURRENT_DATE - INTERVAL '30 days'
),
user_orders AS (
SELECT user_id, SUM(total_amount) as total_spent
FROM orders
GROUP BY user_id
)
SELECT
COUNT(*) as active_customers,
AVG(total_spent) as avg_spend
FROM active_users au
JOIN user_orders uo ON au.user_id = uo.user_id;
-- ✅ GOOD
SELECT * FROM large_table LIMIT 100;
-- ❌ BAD
SELECT * FROM large_table;
-- Before UPDATE/DELETE, check scope
SELECT COUNT(*) FROM table WHERE condition;
-- Then proceed with caution
UPDATE table SET ... WHERE condition;
EXPLAIN ANALYZE
SELECT ...
FROM large_join;
BEGIN;
-- Make changes
UPDATE ...;
-- Verify results
SELECT ...;
-- If correct:
COMMIT;
-- If wrong:
ROLLBACK;
SELECT * FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
FROM products
) ranked
WHERE rn <= 5;
SELECT
user_id,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed
FROM orders
GROUP BY user_id;
WITH numbered AS (
SELECT
date,
ROW_NUMBER() OVER (ORDER BY date) as rn
FROM daily_data
)
SELECT
a.date as gap_start,
b.date as gap_end
FROM numbered a
JOIN numbered b ON a.rn = b.rn - 1
WHERE b.date - a.date > INTERVAL '1 day';