SQL Habits Every Analyst Should Know

Structured Query Language (SQL) has outlived dozens of "revolutionary" database paradigms. From NoSQL document stores to graph engines, SQL remains the universal lingua franca of data. When you are querying a table with 5,000 rows in SQLite or PostgreSQL, virtually any syntactically valid query executes in milliseconds. Bad habits go unnoticed.

However, when your company's data grows into millions of transaction records across Snowflake, BigQuery, or Amazon Redshift, inefficient query habits become catastrophic: multi-minute timeouts, skyrocketing warehouse compute invoices, and spaghetti code that nobody dares refactor. Here are five SQL patterns that separate amateur querying from professional data engineering.

1. Replace Deeply Nested Subqueries with CTEs

Nested subqueries inside FROM clauses are difficult to read, impossible to test in isolation, and obscure query intent. Common Table Expressions (WITH ... AS) structure your query like a logical, top-to-bottom pipeline.

-- Clear, modular, and easy to debug each step independently
WITH active_customers AS (
  SELECT customer_id, signup_date, country
  FROM customers
  WHERE status = 'active'
),
recent_orders AS (
  SELECT 
    customer_id,
    SUM(total_amount) AS total_spend,
    COUNT(order_id) AS order_count
  FROM orders
  WHERE order_date >= '2026-01-01'
  GROUP BY customer_id
)
SELECT 
  c.customer_id,
  c.country,
  COALESCE(o.total_spend, 0) AS ltv_2026,
  COALESCE(o.order_count, 0) AS orders_2026
FROM active_customers c
LEFT JOIN recent_orders o ON c.customer_id = o.customer_id
ORDER BY ltv_2026 DESC;

2. Master Window Functions for Complex Analytics

Before window functions, calculating running totals, month-over-month growth, or finding the "most recent record per user" required convoluted self-joins or multiple passes over the dataset. Window functions execute over partitions without collapsing rows.

• ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC): Perfect for deduplicating or finding each customer's latest order.
• LAG(revenue, 1) OVER (ORDER BY order_month): Instantly computes month-over-month percentage changes without joining the table to itself.
• SUM(amount) OVER (PARTITION BY account_id ORDER BY transaction_date): Builds a continuous running balance in a single execution sweep.

3. Write SARGable Filters (Protect Your Indexes)

A query is "SARGable" (Search Argument Able) when the database query planner can utilize an index on a column. The moment you wrap an indexed column in a function, the optimizer is forced to perform a full table scan, checking every single row individually.

-- Non-SARGable: Scans millions of rows (Index ignored)
SELECT * FROM audit_logs 
WHERE DATE(created_at) = '2026-09-21';

-- SARGable: Uses B-Tree index on created_at (Instant lookup)
SELECT id, user_id, action, created_at FROM audit_logs 
WHERE created_at >= '2026-09-21 00:00:00' 
  AND created_at < '2026-09-22 00:00:00';

4. Ban SELECT * in Production Queries

SELECT * is convenient during interactive exploration in a scratch terminal. But leaving it in production reporting views, dashboards, or ETL scripts has severe consequences:

• Network and I/O Waste: Transfers unused text columns or heavy JSON blobs across the wire.
• Bypasses Covering Indexes: If an index includes columns (id, status, created_at), selecting only those columns requires zero reads from the primary heap table.
• Fragility to Schema Changes: If a team member adds or reorders columns in the upstream table, downstream consumers may fail silently or assign data to wrong positional fields.

5. Explicitly Guard Against NULL Gotchas

In SQL three-valued logic, NULL represents unknown, not zero or false. Expressions like NULL = NULL evaluate to UNKNOWN, not true. When aggregating or filtering:

• Always use COALESCE(column, 0) when doing arithmetic or financial totals to prevent a single NULL from rendering the entire sum NULL.
• Remember that NOT IN (SELECT ...) will return zero rows if the subquery returns even one single NULL. Prefer NOT EXISTS instead.

Conclusion

Writing performant SQL is less about memorizing obscure dialect syntax and more about cultivating discipline: explicit projections, modular CTE structure, window partitioning, and respecting database index mechanics. Your queries will run faster, your cloud bills will shrink, and your teammates will thank you.