The 5 SQL Habits That Separate Good Analysts from Great Ones

If you’ve been writing SQL for a while, you’ve probably hit a point where it all starts to look the same. You can pull data, join tables, write a GROUP BY in your sleep. But something still feels off. Your queries work — they just don’t sing.

Here’s the thing: getting good at SQL is easy. Getting great takes a different kind of effort. Not more hours. Better habits.

I’ve worked with a lot of analysts over the years, and the ones who stand out aren’t necessarily the ones who know the most functions. They’re the ones whose code is clean, whose logic is airtight, and whose results you can trust without double-checking.

These are the five habits that make the difference.

## 1. Write for the person who reads your query next (even if that person is future you)

Most analysts write SQL for the machine. The great ones write it for the human.

Here’s what that looks like in practice. Compare these two queries — they return the same result:

The “just make it work” version:

SELECT a.id, a.nm, SUM(b.amt) FROM t1 a
LEFT JOIN t2 b ON a.id = b.cid
WHERE b.dt >= '2024-01-01'
GROUP BY a.id, a.nm;

The readable version:

SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.order_amount) AS total_revenue
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY
    c.customer_id,
    c.customer_name;

Same output. But the second one doesn’t require you to reverse-engineer what the author was thinking. Meaningful aliases, explicit column names, consistent indentation — these aren’t luxuries, they’re professionalism.

And when the logic gets genuinely complex, add a comment:

-- Exclude test accounts created by the QA team (prefix 'TEST_')
WHERE c.customer_name NOT LIKE 'TEST_%'

Future you will thank present you.

## 2. Validate before you trust

It’s tempting to write a query, see numbers come back, and call it done. But numbers are always “correct” — they just might be answering the wrong question.

The most common silent killer: unexpected row duplication after a JOIN.

-- Step 1: Check row counts before and after your join
SELECT COUNT(*) FROM orders;            -- e.g. returns 10,000
SELECT COUNT(*) FROM orders o
LEFT JOIN order_items oi
    ON o.order_id = oi.order_id;        -- returns 43,000 — uh oh
-- Step 2: Understand why — one order has many items
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
ORDER BY item_count DESC
LIMIT 5;

If you weren’t expecting a fan-out there, your SUM on revenue is now wildly inflated and your stakeholder is about to present the wrong number in a board meeting.

Build a quick validation check into every query that involves a join. It takes 60 seconds and can save hours of backtracking.

## 3. Understand what the data actually means

This one sounds obvious, but it’s where a lot of analysts fall short.

Before writing a single line of SQL, ask: What is the grain of this table? A “transactions” table might look simple until you realise refunds are stored as negative rows, not separate records. Or that one customer can appear under multiple user_id values because of a legacy migration.

Get in the habit of profiling unfamiliar tables before querying them:

-- Understand the grain: is order_id truly unique?
SELECT order_id, COUNT(*) AS row_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Check for NULLs in key columns
SELECT
    COUNT(*)                                        AS total_rows,
    COUNT(customer_id)                              AS non_null_customer_id,
    COUNT(*) - COUNT(customer_id)                   AS null_customer_id,
    COUNT(DISTINCT customer_id)                     AS unique_customers
FROM orders;
-- Spot unexpected date ranges
SELECT MIN(order_date), MAX(order_date) FROM orders;

Two minutes of profiling like this will tell you more about a table than twenty minutes of reading documentation that may or may not be up to date.

## 4. Learn window functions — then actually use them

If you’re still reaching for a self-join every time you need a running total or a rank, it’s time.

The mental model: window functions operate across rows without collapsing them. Your result set stays intact — you just get a new column computed across a defined window of rows.

Running total:

SELECT
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
--Rank customers by revenue within each region:
SELECT
    customer_name,
    region,
    total_revenue,
    RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS revenue_rank
FROM customer_summary;
--Compare each month's revenue to the previous month:
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month)           AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_revenue;

That last one used to require a self-join and a WHERE condition. Now it’s three lines. Once window functions click, you’ll wonder how you ever managed without them.

## 5. Optimise with intention, not habit

“Make it faster” is not an optimisation strategy. Understanding why a query is slow — and whether it even matters — is.

Start with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) before touching anything:

-- Analyse query performance before optimising
EXPLAIN ANALYZE
SELECT
    c.customer_name,
    SUM(o.order_amount) AS total_revenue
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_name;

The output will tell you if you’re doing a sequential scan on a large table (a red flag), whether indexes are being used, and where the majority of execution time is being spent.

A common performance trap is filtering on a calculated expression — which prevents index use:

-- Slow: function on the column disables the index
WHERE YEAR(order_date) = 2024
-- Fast: range filter lets the index do its job
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

The habit to build: profile first, change one thing at a time, measure the impact. Don’t optimise by instinct — optimise by evidence.

## The Real Pattern

Look back at those five habits and you’ll notice something: none of them are really about SQL syntax. They’re about thinking clearly, communicating precisely, and trusting your own work.

The syntax you can Google. The habits have to be built.

If you’re working on your SQL skills right now, pick one habit from this list and focus on it for the next two weeks. Don’t try to overhaul everything at once. Just get more intentional, one query at a time.

And if you want more practical tips like these — no fluff, no jargon — subscribe to the newsletter. I send out real-world data insights, project walkthroughs, and honest career takes every week.

—

What’s the SQL habit you wish you’d built sooner? Drop it in the comments — I read every one.