Author: Sainath Reddy

I once inherited a revenue report that had been green for fourteen months. Every morning it ran, populated a dashboard, and nobody questioned it. Then a new CFO asked why the data warehouse said Q2 regional revenue was $4.1M while the finance system said $1.3M. Same source data. The query had no error, no failed test, no warning. It had one join. Somewhere upstream, an orders table was joined to order_items, and a downstream SUM was quietly counting each order’s total once for every line item on it. A three-line-item order got counted three times. For fourteen months.

The engineer who wrote it was competent. The SQL was readable. It followed every rule in the “write clean SQL” playbook — named columns, tidy formatting, sensible aliases. And it was still catastrophically wrong, because clean and correct are different things. That gap is what actually separates senior SQL from junior SQL, and it’s a deeper gap than the usual advice about CTEs and naming suggests. The readability stuff is real and I’ll get to it, but if that’s all you fix, you’ll write beautifully formatted queries that overstate revenue by 3x. Senior SQL is different along three axes at once — correctness, cost, and change-safety — and only the third is about how the code looks. This isn’t abstract, by the way: I’ve argued that SQL is the highest-leverage skill on a data team right now, and this is exactly why — the language is easy, the judgment is not.

TL;DR

• → A query that runs clean can still be wrong; the most expensive SQL bugs produce plausible numbers, not errors.
• → Track the grain of every CTE — joining before you aggregate is the single most common cause of silently inflated totals.
• → NOT IN (subquery) returns zero rows if the subquery contains a single NULL; seniors use NOT EXISTS for anti-joins.
• → Deduping with QUALIFY ROW_NUMBER() and a tie-broken ORDER BY is reproducible; SELECT DISTINCT and untied ROW_NUMBER are not.
• → Wrapping a filter column in a function (DATE(ts) = ...) and using SELECT * both defeat the engine’s ability to skip data, and that shows up on the bill.
• → CTEs aren’t just prettier — each one is a debugging checkpoint you can query in isolation when a number looks wrong.
• → Junior engineers optimize to make the query run; senior engineers optimize so the next change, and the next reader, don’t create a new bug.

Senior SQL isn’t clever SQL

There’s a myth that senior engineers write dense, impressive queries full of tricks. The opposite is true. The most senior SQL I’ve reviewed is almost boring to read — because the author spent their cleverness on being right and cheap, not on being concise. The comparison above captures the pattern: at every decision point there’s a lazy default that works on your laptop against 100 rows, and a deliberate choice that survives contact with production data. The rest of this article is the “why” behind the right-hand column, with real SQL for each.

Correctness #1: the grain trap that triples your revenue

This is the bug from the intro, and it’s worth seeing in code because it looks completely innocent. The business question: total spend per customer. There’s an orders table and an order_items table. Here’s the query that ran for fourteen months:

-- WRONG: order_total is counted once per line item
SELECT
    o.customer_id,
    SUM(o.order_total) AS total_spend
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'refunded'
GROUP BY o.customer_id;

The join changes the grain. Before the join, orders has one row per order. After joining order_items, an order with three line items becomes three rows — each carrying the full order_total. The SUM then adds that total three times. Nothing errors; the number is just inflated by however many items the average order has.

The join multiplied the rows; the SUM multiplied the money. The fix is to aggregate at the right grain before joining.

The senior version aggregates order_items down to one row per order first, so every later step operates on a known grain:

WITH order_revenue AS (              -- grain: one row per order
    SELECT
        oi.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM order_items oi
    GROUP BY oi.order_id
)
SELECT
    o.customer_id,                   -- grain: one row per customer
    SUM(orv.order_total) AS total_spend
FROM orders o
JOIN order_revenue orv ON orv.order_id = o.order_id
WHERE o.status <> 'refunded'
GROUP BY o.customer_id;

The habit that prevents this bug isn’t a syntax rule — it’s asking “what is the grain of this result?” after every join and every CTE. Seniors annotate it, as in the comments above. This is the same class of silent failure I wrote about in why passing dbt tests still ship bad data: uniqueness and not-null tests would both pass on the wrong query above, because the rows really are unique and non-null. They’re just counted too many times.

Correctness #2: NOT IN will betray you

You need a win-back list: customers with no orders in the last 90 days. The obvious query:

-- WRONG if orders.customer_id contains any NULL
SELECT customer_id, email
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - 90
);

If a single row in that subquery has a NULL customer_id — a guest checkout, a bad load, a soft-deleted account — this query returns zero rows. Not an error. An empty result that looks like “nobody qualifies.” The reason is SQL’s three-valued logic: NOT IN is defined as “not equal to any,” and comparing anything to NULL yields UNKNOWN, which poisons the whole condition so no row is ever selected. This is spec-mandated behavior, documented in PostgreSQL’s logical operators reference, and it behaves the same across every compliant engine.

The senior default is an anti-join with NOT EXISTS, which is immune to the NULL problem because it tests row existence, not value equality:

SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= CURRENT_DATE - 90
);

Rule of thumb worth internalizing: reach for NOT EXISTS over NOT IN every time a subquery is involved, unless you have proven the column is non-nullable. It’s not a style preference; it’s a correctness guarantee.

Correctness #3: dedup that returns the same rows twice

Deduplication is where juniors reach for SELECT DISTINCT and seniors reach for a window function — and the difference is about determinism, not taste. Say raw_orders has duplicate order_ids from an at-least-once ingestion, and you want the latest version of each. DISTINCT can’t express “latest”; it just collapses fully identical rows. And ROW_NUMBER without a tie-break silently picks an arbitrary row when the sort column ties — so the same pipeline can emit different rows on different runs, which is a nightmare to debug.

The senior pattern uses QUALIFY to filter on a window function inline, with a deterministic ordering:

SELECT order_id, customer_id, status, order_total, updated_at
FROM raw_orders
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY updated_at DESC, ingested_at DESC   -- tie-break = determinism
) = 1;
+----------+-------------+----------+-------------+---------------------+
| order_id | customer_id | status   | order_total | updated_at          |
+----------+-------------+----------+-------------+---------------------+
|      101 |        4471 | shipped  |      150.00 | 2026-07-20 14:02:11 |
|      102 |        2210 | placed   |      200.00 | 2026-07-21 09:15:40 |
|      103 |        8890 | returned |      250.00 | 2026-07-21 18:44:02 |
+----------+-------------+----------+-------------+---------------------+

The second column in the ORDER BY is the tell. A junior stops at updated_at DESC; a senior adds a unique tie-breaker so that when two rows share an updated_at, the query still picks the same one every single run. Reproducibility is a correctness property, and non-deterministic SQL is a close cousin of the “same input, different output” problem — except here you can actually eliminate it.

Cost: write SQL the engine is allowed to skip

Correctness keeps you employed; cost keeps you promoted. On columnar cloud warehouses, two junior habits quietly multiply what a query scans. Consider:

-- Scans every column, every micro-partition
SELECT *
FROM events
WHERE DATE(event_ts) = '2026-07-01';

Two problems. SELECT * forces the engine to read every column, including fat JSON and audit fields nobody asked for. And wrapping event_ts in DATE() means the optimizer can’t use the raw timestamp to prune partitions — it has to compute a function over every row first. The senior rewrite selects only what it needs and expresses the filter as a half-open range on the bare column, which lets the engine skip whole partitions it knows can’t match:

SELECT event_id, user_id, event_type
FROM events
WHERE event_ts >= '2026-07-01'
  AND event_ts <  '2026-07-02';

If you want the mechanics of why this works — how pruning and partition elimination actually happen — I walked through it in what really happens when you run a query in Snowflake, and why re-running the same query can be nearly free in how the warehouse cache actually works.

The cost math

Put rough numbers on it. Say events is 2 TB across 40 columns, roughly evenly sized, holding one year of data. The junior query scans close to the full 2 TB. Selecting only the three columns you need cuts that to about 150 GB (three of forty columns). Adding the prunable date range drops it to one day out of ~365 — on the order of 400 MB actually read. That’s a ~5,000x reduction in bytes scanned, on a warehouse you’re billed for by the second, from two changes that took ten seconds to make. This is the same discipline behind not rebuilding models that didn’t change, which is the whole premise of dbt state-based selection — and it’s why teams serious about spend even push analytics to cheaper engines like DuckDB for dev workloads.

Change-safety: CTEs as checkpoints, not decoration

This is the axis the “clean SQL” advice usually covers, and it’s genuinely important — just not sufficient on its own. Structuring a query as a sequence of named CTEs (recent_orders → order_revenue → customer_summary) does more than read nicely. Each CTE is a checkpoint you can inspect in isolation: when a customer is missing from the output, you don’t mentally execute a nested monster, you run SELECT * FROM order_revenue WHERE order_id = 8821 and walk the stages until the row disappears. That’s the difference between a five-minute debug and a fifty-minute one.

The other half of change-safety is treating your SELECT list as a contract. SELECT * in a model means that the day someone adds a gdpr_deletion_requested column upstream, it silently flows into every downstream dashboard — the exact class of break I covered in how one renamed column kills a pipeline. An explicit column list is a promise about what this query returns, and promises are what let the next engineer change things without fear. If you want this enforced at the project level rather than per query, the layering conventions in structuring dbt projects in Snowflake are the natural home for it.

The gotchas nobody warns you about

DISTINCT is often a fan-out cover-up. If you added SELECT DISTINCT to “fix” duplicate rows, you probably have a grain bug upstream and are papering over it. DISTINCT hides the symptom and keeps the doubled aggregates.

BETWEEN on timestamps is a silent off-by-one. event_ts BETWEEN '2026-07-01' AND '2026-07-02' includes midnight of the second day, double-counting boundary rows. Half-open ranges (>= and <) are the senior default for exactly this reason.

COUNT(column) and COUNT(*) are not the same. COUNT(col) skips NULLs; COUNT(*) counts rows. Using the wrong one turns a data-quality problem into a wrong metric that looks fine.

RANK and ROW_NUMBER dedup differently. RANK() assigns ties the same number, so QUALIFY RANK() = 1 can keep multiple rows per key. For “exactly one row per key,” it must be ROW_NUMBER().

ORDER BY inside a CTE or view is not guaranteed to hold. The engine is free to reorder unless the outermost query sorts. If order matters downstream, sort where the data is consumed, not where it’s defined.

The one principle

Junior engineers write SQL that returns the right answer today; senior engineers write SQL that can’t quietly start returning the wrong one. The clean formatting, the CTEs, the explicit columns — those are the visible surface. Underneath is a habit of assuming your data is messier than the demo, your query will be re-run on data you haven’t seen, and the next person to touch it won’t know what you knew. Write for that world, and “senior SQL” stops being a style and becomes a form of insurance.

Related reading: Why passing dbt tests still ship bad data · What really happens when you run a query · How one renamed column kills a pipeline · Why SQL is the most valuable skill in AI · Snowflake QUALIFY reference · PostgreSQL three-valued logic

A hiring manager I know keeps a folder of rejected portfolio links. Not to be cruel — to remember the pattern. Out of roughly forty SQL “projects” she reviewed for one analytics-engineer opening, thirty-one were the same customer-churn notebook off the same Kaggle CSV, with the same three queries, ending on the same bar chart. She stopped opening them after about the fifteenth. The one candidate she did call had used the identical dataset as everyone else — but she’d written a paragraph explaining that 4,200 rows had a tenure of zero and a churn flag of one, which is impossible, and how she decided to handle them. That paragraph got her the interview. The SQL didn’t.

That is the thing nobody tells you when you start building a data portfolio: the query is table stakes. Everyone can write a GROUP BY. What almost nobody does is show that they can think about data the way it actually behaves in production — dirty, contradictory, and full of rows that break your assumptions. If you’re weighing whether SQL is even worth the investment in an AI-saturated market, it still is; I’ve argued at length about why SQL quietly became the most valuable skill in the modern stack. This article is the follow-on: five projects genuinely worth building, the public repositories and datasets to build them from, and — the part that matters — what to add to each so it doesn’t land in the rejected folder.

TL;DR

• → Hiring managers reject SQL portfolios for sameness, not syntax errors; the differentiator is documented judgment about messy data, not a cleaner JOIN.
• → Five projects cover the skills employers actually screen for: churn analysis, a Bronze/Silver/Gold data warehouse, sales analysis, customer segmentation, and healthcare KPIs.
• → A Bronze/Silver/Gold data-warehouse build is the single highest-signal SQL portfolio project because it proves modeling and ETL thinking, not just querying.
• → Window functions (NTILE, RANK, ROW_NUMBER) separate a beginner portfolio from a mid-level one faster than any other SQL feature.
• → Use real, messy public data — NYC TLC trip records, open GitHub project datasets — instead of the pre-cleaned Kaggle CSV everyone else submitted.
• → Every project needs a written “what I found and what I’d do about it” section; a small, well-explained project beats a large one with no story.
• → You can run all five locally for free with DuckDB or SQLite — no cloud warehouse and no credit-card required.

Why most SQL portfolios get skipped in 30 seconds

Reviewers don’t read portfolios top to bottom. They scan for a reason to say no, because they have forty of them and one afternoon. A wall of SELECT statements with no narrative gives them that reason instantly. So does a project that’s obviously a tutorial you followed step-for-step, because a tutorial proves you can type, not that you can decide.

The uncomfortable truth is that clean, green output is not evidence of good work. I’ve written before about how tests can pass while you’re still shipping bad data, and the same trap applies to portfolios: a query that runs without error can still be answering the wrong question on data you never inspected. A reviewer who has been burned by exactly that in production is looking for the opposite signal — someone who checked.

This is also why I’m lukewarm on stacking certifications as a substitute for building things; I’ve made the full case for why a certificate rarely moves a hiring decision. A project where you can point at a specific decision you made — “I bucketed tenure this way because the raw values were bimodal” — does the thing a certificate can’t. It shows judgment.

So as you read the five projects below, treat the query as the easy 20%. The 80% that gets you hired is the layer on top: the data-quality checks, the edge cases you caught, and the plain-English conclusion.

The five projects worth building

These five map to the roles most people are actually applying for — analyst, analytics engineer, BI developer — and between them they exercise every SQL skill a screener looks for. For each one I’ve noted a real public repository or dataset to start from, and the one addition that lifts it above the crowd.

1. E-commerce customer churn analysis

Churn is the most common portfolio project for a reason: it’s a real business problem with a clear money consequence, and it maps cleanly to SQL. You take a table of customers with attributes like tenure, complaint counts, satisfaction scores, order frequency, coupon usage, and days since last order, and you find the patterns that predict who leaves. A good public starting point is the open-source Ecommerce Customer Churn Analysis repository, which ships the raw CSV and a full query file you can read, critique, and improve rather than copy.

The skills on display are aggregation, conditional logic, and segmentation. Here’s the kind of query that belongs in this project — churn rate bucketed by tenure, which immediately tells a retention story:

SELECT
    tenure_bucket,
    COUNT(*)                                  AS customers,
    SUM(churned)                              AS churned_customers,
    ROUND(100.0 * SUM(churned) / COUNT(*), 1) AS churn_rate_pct
FROM (
    SELECT
        customer_id,
        CASE WHEN churn = 1 THEN 1 ELSE 0 END AS churned,
        CASE
            WHEN tenure < 6  THEN '0-5 months'
            WHEN tenure < 12 THEN '6-11 months'
            WHEN tenure < 24 THEN '12-23 months'
            ELSE '24+ months'
        END AS tenure_bucket
    FROM ecommerce_churn
) t
GROUP BY tenure_bucket
ORDER BY churn_rate_pct DESC;
Run it and you get something like this:

+---------------+-----------+-------------------+----------------+
| tenure_bucket | customers | churned_customers | churn_rate_pct |
+---------------+-----------+-------------------+----------------+
| 0-5 months    |      1846 |               912 |           49.4 |
| 6-11 months   |      1204 |               331 |           27.5 |
| 12-23 months  |      1098 |               142 |           12.9 |
| 24+ months    |       503 |                28 |            5.6 |
+---------------+-----------+-------------------+----------------+

What to add that nobody else does: before you compute a single rate, profile the data and write down what’s wrong with it. Customers with a tenure of zero but a churn flag set. Satisfaction scores outside the documented range. Duplicate customer IDs. Then state your handling decision and why. That paragraph is the reason a reviewer keeps reading.

2. A Bronze/Silver/Gold data warehouse

If you build only one project from this list, build this one. It’s the highest-signal thing on the page because it proves you understand how real data systems are assembled — ingestion, cleansing, and modeling into fact and dimension tables — not just how to query a table someone else prepared. The SQL Data Warehouse Project by Data With Baraa is the gold-standard reference here: it’s MIT-licensed, has hundreds of forks, and walks the full Medallion (Bronze → Silver → Gold) architecture using ERP and CRM CSV sources loaded into a SQL database, then modeled into a star schema.

You’ll practice ETL sequencing, data cleaning, and dimensional modeling. If you want to understand the modeling layer more deeply — where the real design decisions live — my guide on structuring a warehouse project into staging, intermediate, and mart layers maps almost one-to-one onto Bronze/Silver/Gold, and it’ll help you explain why your layers are split the way they are.

What to add that nobody else does: a data catalog and naming conventions. Two short Markdown files — one documenting every column in your Gold tables, one stating your table/column naming rules — signal “I’ve worked on a team” louder than any query. It’s also the thing that separates a warehouse project from a pile of scripts.

3. Sales data analysis

Sales analysis connects SQL directly to business performance, which makes it the easiest project to narrate to a non-technical interviewer. You answer questions a real stakeholder asks: which products drive revenue, how revenue trends month over month, which customer cohorts spend the most, whether there’s seasonality. The skills are joins, date functions, sorting, filtering, and time-based grouping.

This is a great one to build on genuinely large, genuinely messy public data instead of a tidy sample. The NYC Taxi & Limousine Commission trip records are a government-published dataset running to millions of rows per month, with real quirks — negative fares, zero-distance trips, timestamps out of order. Treating trips as “sales” and analyzing revenue by hour, zone, and payment type gives you a portfolio piece at a scale most candidates never touch. And you don’t need a cluster to do it: I’ve made the case for why you should stop spinning up Spark for datasets this size — DuckDB will chew through a month of trip data on your laptop.

What to add that nobody else does: a “so what” for every chart. Don’t just show that December revenue is up. Say what a business would do about it. The analysis is the input; the recommendation is the deliverable.

4. Bank customer segmentation

Segmentation is where you graduate from beginner SQL to mid-level SQL, because it’s the natural home for window functions. You take a banking-style dataset of customers, transactions, and regions, and you rank customers by value, flag dormant versus active accounts, and score regional performance. Employers in fintech and financial analytics screen hard for CTEs, NTILE, RANK, and PARTITION BY — and this project shows all of them at once.

WITH customer_value AS (
    SELECT
        customer_id,
        region,
        SUM(transaction_amount) AS total_spend,
        COUNT(*)                AS txn_count,
        MAX(transaction_date)   AS last_txn
    FROM bank_transactions
    GROUP BY customer_id, region
)
SELECT
    customer_id,
    region,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC)  AS value_quartile,
    RANK()   OVER (PARTITION BY region
                   ORDER BY total_spend DESC)  AS rank_in_region
FROM customer_value
ORDER BY total_spend DESC
LIMIT 5;
+-------------+---------+-------------+----------------+----------------+
| customer_id | region  | total_spend | value_quartile | rank_in_region |
+-------------+---------+-------------+----------------+----------------+
|       10427 | South   |    284119.50 |              1 |              1 |
|       10088 | West    |    271004.20 |              1 |              1 |
|       10391 | South   |    259847.75 |              1 |              2 |
|       10142 | North   |    244310.00 |              1 |              1 |
|       10265 | West    |    238900.10 |              1 |              2 |
+-------------+---------+-------------+----------------+----------------+

What to add that nobody else does: define your segments before you write the SQL, in business terms, and defend the thresholds. “High-value” meaning top quartile by spend is a choice; so is defining “dormant” as no transaction in 90 days. Writing down why you drew the lines where you did is the difference between analysis and arbitrary bucketing.

5. Healthcare data analysis

Healthcare rounds out the set because it proves you can work with domain-specific, meaningful data: patient records, conditions, hospitals, insurance providers, admission types, and billing. You explore which conditions are most common, how billing varies by condition, which hospitals see the most volume, and how admission types differ across patient groups. The SQL skills — grouping, filtering, joins, aggregates — overlap with sales analysis, but the domain framing shows range.

What to add that nobody else does: a KPI rollup with definitions. Healthcare reviewers care about correctness of metrics. Publishing a small set of KPIs (average length of stay, cost per admission type, readmission proxy) with an explicit definition for each shows you understand that a metric is only as good as its definition — the same discipline that keeps a real reporting layer trustworthy.

Where to get data that isn’t the same tired CSV

The fastest way to blend into the rejected folder is to use the exact pre-cleaned dataset every tutorial uses. Three better sources: open GitHub project repositories that ship their own raw CSVs (like the two linked above), government open-data portals such as the NYC TLC records for genuine scale and mess, and Kaggle for breadth when you want a specific domain. The rule of thumb: the messier and more real the source, the more your data-quality work has something to actually do — which is the whole point.

You do not need cloud infrastructure for any of this. All five projects run locally on DuckDB or SQLite for free, and if you later want to show you can operate against a warehouse without burning money, my walkthrough on querying warehouse data through DuckDB to cut costs covers the hybrid pattern. For the ingestion side of the warehouse project, a small Python loading script turns “I loaded CSVs” into “I built a repeatable pipeline.”

How to actually spend your time

Most people invert the effort. They spend 80% of their time writing more queries and 20% on everything else, then wonder why the portfolio reads like homework. Flip it. A realistic budget for one strong project looks closer to this: roughly 15% profiling and cleaning the data, 25% writing the core SQL, 20% catching and documenting edge cases, and 40% on the write-up — the README, the data-quality notes, and the plain-English conclusions. The queries are the cheapest part to produce and the least differentiating. The narrative is expensive, rare, and exactly what gets remembered.

This is also why one finished, deeply documented project beats five half-built ones. Depth is legible to a reviewer in a way that breadth isn’t. Five shallow churn notebooks read as one shallow churn notebook copied five times.

The gotchas nobody warns you about

A green query is not a correct query. The most dangerous portfolio bug is a query that runs, returns plausible numbers, and is silently wrong because you joined on a column with duplicates and fanned out your row counts. Always sanity-check totals before and after a join.

Averages lie on skewed data, and money is always skewed. “Average transaction value” across a banking dataset is nearly meaningless when a handful of whales dominate. Reach for medians and percentiles, and mention explicitly why you did — it signals statistical maturity.

Date handling is where portfolios quietly break. Time zones, mixed formats, and out-of-order timestamps in real datasets like the TLC records will wreck a naive monthly rollup. The candidate who noticed 200 trips with a drop-off before the pick-up looks far stronger than the one whose numbers were merely clean.

Schema assumptions rot the moment upstream data changes. If a column you depend on gets renamed or retyped, your whole analysis silently produces garbage — the exact failure mode I unpack in the piece on how a single renamed column kills a pipeline. Documenting the schema you built against is cheap insurance and a strong signal.

A dashboard is not a conclusion. Ending on a chart with no written interpretation is the most common way strong SQL gets wasted. The reviewer wants your read of the numbers, not a second job interpreting them.

The one principle

Your SQL proves you can query; your write-up proves you can think — and only one of those gets you hired. Pick one project from this list, use genuinely messy public data, and spend more time explaining your decisions than writing your queries. A small project with a documented point of view will beat a sprawling one with clean output and nothing to say, every single time.

Related reading: Why SQL is the most valuable skill in AI (2026) · The problem with data engineering certifications · Structuring a warehouse project properly · Run it locally with DuckDB, not Spark · SQL Data Warehouse Project (GitHub) · NYC TLC trip record data