SQL Queries You'll Use Every Day (But Nobody Teaches in Tutorials)

SQL tutorials teach you SELECT, INSERT, UPDATE, DELETE. Then they stop.

But production SQL is 80% window functions, CTEs, and conditional aggregation. Here are the patterns I use daily.

1. CTEs (Common Table Expressions)

The single most useful SQL feature:

-- Find users who signed up last month and made a purchase
WITH recent_users AS (
    SELECT id, email, created_at
    FROM users
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
),
purchasers AS (
    SELECT DISTINCT user_id
    FROM orders
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
)
SELECT r.email, r.created_at
FROM recent_users r
JOIN purchasers p ON r.id = p.user_id;

CTEs make complex queries readable. Each WITH block is a named subquery you can reference.

2. Window Functions

Running Total

SELECT 
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

Rank Within Groups

-- Top 3 products per category
SELECT * FROM (
    SELECT 
        name, category, sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank
    FROM products
) ranked
WHERE rank <= 3;

Previous/Next Row

SELECT 
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) as prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) as change
FROM daily_sales;

3. CASE Expressions for Conditional Logic

-- Categorize users by activity
SELECT 
    email,
    CASE 
        WHEN last_login > CURRENT_DATE - INTERVAL '7 days' THEN 'active'
        WHEN last_login > CURRENT_DATE - INTERVAL '30 days' THEN 'at_risk'
        ELSE 'churned'
    END as status,
    COUNT(*) OVER () as total_users
FROM users;

Conditional Aggregation (Pivot)

-- Count orders by status in one query
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) FILTER (WHERE status = 'completed') as completed,
    COUNT(*) FILTER (WHERE status = 'pending') as pending,
    COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled
FROM orders
GROUP BY 1
ORDER BY 1;

4. COALESCE for Default Values

-- Handle NULL values gracefully
SELECT 
    name,
    COALESCE(phone, email, 'No contact info') as contact,
    COALESCE(discount, 0) as discount
FROM customers;

5. EXISTS vs IN

-- FASTER: EXISTS (stops at first match)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- SLOWER: IN (loads all results first)
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

EXISTS is almost always faster for subqueries. IN is fine for small static lists.

6. LATERAL JOIN

-- Get latest 3 orders per user
SELECT u.name, o.id, o.total, o.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT id, total, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

7. UPSERT (INSERT ON CONFLICT)

-- Insert or update in one statement
INSERT INTO metrics (user_id, date, page_views)
VALUES (123, CURRENT_DATE, 1)
ON CONFLICT (user_id, date) 
DO UPDATE SET page_views = metrics.page_views + 1;

8. Generate Series (for filling gaps)

-- Revenue for every day, even days with no sales
WITH dates AS (
    SELECT generate_series(
        '2026-01-01'::date,
        '2026-03-25'::date,
        '1 day'
    )::date as date
)
SELECT 
    d.date,
    COALESCE(SUM(o.total), 0) as revenue
FROM dates d
LEFT JOIN orders o ON DATE(o.created_at) = d.date
GROUP BY d.date
ORDER BY d.date;

Cheatsheet

Pattern	Use When
CTE	Breaking complex queries into steps
Window functions	Rankings, running totals, row comparison
CASE	Conditional logic in queries
COALESCE	Default values for NULLs
EXISTS	Checking if related rows exist (faster than IN)
LATERAL	Correlated subqueries with LIMIT
UPSERT	Insert-or-update
generate_series	Filling date/time gaps

More database tools: Docker Compose Templates (PostgreSQL, MongoDB, Redis configs) | FastAPI Starter

---

What SQL pattern do you use the most? Any favorites I missed? 👇

SQL & backend articles at dev.to/0012303