What "Production-Ready SQL" Means in Interviews

A correct query can still fail an interview. Senior loops at Meta, Stripe, and Airbnb downgrade candidates whose SQL returns the right answer but wouldn't survive a code review. The interviewer's exact note in the rubric: "correct, but not production-ready."

Most candidates have never been told what that phrase means. They assume "production-ready" is a vague style preference. It isn't. It's a specific, learnable bar that separates senior offers from mid-level. This guide is what production-ready SQL actually looks like, and how to get there before your next loop.

What "Production-Ready" Means

A production-ready query is one a senior teammate would approve in a code review without asking for changes. It returns the right answer and:

1. Is readable by someone who didn't write it
2. Handles the edge cases that exist in real data
3. Is structured so that pieces can be reused or modified
4. Performs acceptably on the actual data volume
5. Makes its assumptions explicit, not implicit

A junior query satisfies #1 only on the day it's written. A production query satisfies all five — six months later, on someone else's screen, against data that's evolved.

The Eight Markers of Production-Ready SQL

Senior interviewers aren't looking for poetry. They're looking for a specific set of habits. Each one is small. Together they signal "I've shipped SQL that other people had to maintain."

Marker 1 — Named CTEs over Nested Subqueries

A correct but not-production query stuffs everything into a single 40-line select with nested subqueries. A production query decomposes into named CTEs.

Not production:

SELECT user_id, total_revenue, total_orders, total_revenue::numeric / NULLIF(total_orders, 0) AS aov
FROM (
  SELECT user_id, SUM(amount) AS total_revenue, COUNT(*) AS total_orders
  FROM (
    SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '90 days' AND status = 'completed'
  ) recent
  GROUP BY user_id
) summarized
WHERE total_orders >= 3;

Production:

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    AND status = 'completed'
),
user_totals AS (
  SELECT
    user_id,
    SUM(amount) AS total_revenue,
    COUNT(*)    AS total_orders
  FROM recent_orders
  GROUP BY user_id
)
SELECT
  user_id,
  total_revenue,
  total_orders,
  ROUND(total_revenue::numeric / NULLIF(total_orders, 0), 2) AS avg_order_value
FROM user_totals
WHERE total_orders >= 3;

Same answer. Six months from now, only the second one is editable without rewriting.

Marker 2 — Explicit Null Handling

Real data has nulls. Production queries assume they exist and decide what to do about them.

• COUNT(column) ignores nulls — sometimes that's what you want, sometimes not
• SUM and AVG skip nulls silently — can mask data quality issues
• Joining on a nullable column will drop or duplicate rows depending on join type
• Division by a column that might be zero or null silently breaks without NULLIF

The production move is to think through null cases for every column you touch. The senior signal is naming them out loud: "channel is nullable for organic acquisitions — I'm treating those as 'organic' explicitly rather than letting them drop."

Marker 3 — Sensible Time Window Semantics

>= CURRENT_DATE - INTERVAL '30 days' is the right form most of the time. But junior candidates regularly write > CURRENT_DATE - INTERVAL '30 days' (off by one day) or BETWEEN without thinking about boundary inclusion.

Production-ready means:

• State which boundary is inclusive: "Last 30 days, including today, excluding today's incomplete day"
• Use closed-open intervals when joining time ranges: start <= event_time AND event_time < end
• Be explicit about timezone: "This assumes the events are in UTC — if they're in user-local time, I'd need to convert first"

Marker 4 — Defensive Aggregation Choices

COUNT(*) counts rows. COUNT(user_id) counts non-null user IDs. COUNT(DISTINCT user_id) counts unique users. These return different numbers on the same data.

Production-ready means you pick the right one and you say why. Defaulting to COUNT(*) when the question is about users is the most common junior mistake. The interviewer notices.

Same with SUM over a joined table — if you joined a 1-to-many, you're double-counting. The fix is to aggregate before joining, or use SUM(DISTINCT ...), or restructure the query. The production query makes the join multiplicity explicit.

Marker 5 — Window Functions Used Deliberately, Not Defensively

Window functions are powerful and often necessary. They're also often overused. A junior candidate reaches for ROW_NUMBER() for problems that just need MIN. A senior candidate uses windows when they're the right tool — for deduplication, ranking, or running calculations.

The production-ready move is asking "do I need a window here, or am I overcomplicating?" before reaching for OVER (PARTITION BY ...). When you do use one, name what it's doing in a comment if the why isn't obvious from context.

Marker 6 — Performance Awareness on Real Data Sizes

You're not expected to optimize for petabytes in an interview. You ARE expected to know when a query won't scale.

• SELECT DISTINCT on a billion-row table is expensive
• A JOIN without indexed keys is a full scan on both sides
• Window functions over the entire table without a PARTITION BY are sequential scans
• A WHERE clause that wraps the indexed column in a function (WHERE DATE(created_at) = ...) prevents index use

Production-ready means flagging these as you go: "This DISTINCT on a billion-row table would be slow — in production I'd probably materialize this as a daily aggregate instead."

Marker 7 — Consistent, Readable Formatting

Tabs vs spaces, uppercase vs lowercase, where the commas go — none of these matter individually. What matters is internal consistency within the query.

Pick one style and stick to it for the whole problem:

• SQL keywords UPPERCASE
• Column names lowercase with underscores
• One column per line in SELECT clauses with three or more columns
• Indentation visually shows the query structure
• Commas at the end of lines (preferred) or leading commas (acceptable) — never mixed

The interviewer doesn't care which style you pick. They care that you have one.

Marker 8 — Comments Only Where Why Isn't Obvious

Junior candidates over-comment ("this CTE gets the users"). Senior candidates under-comment, but the comments they DO write capture non-obvious decisions.

Good comment:

-- Use MIN here because users can have multiple activation events;
-- we want the first one
MIN(activated_at) AS first_activated_at

Bad comment:

-- Get the user_id and email
SELECT user_id, email

The production-ready move is to assume the reader can read SQL but not your mind. Comment the why, never the what.

What an Interviewer Hears When They See Production-Ready SQL

The internal monologue of a senior reviewer when they see clean, decomposed, edge-case-aware SQL:

"This person has shipped before. I can trust their PRs without line-by-line review. I can put them on an ambiguous problem and not babysit. I can promote them within 18 months."

That mental note is what bumps you from a 3 to a 4 on Code Quality and often from a 3 to a 4 on Communication. Two dimension upgrades from the same set of habits. Production-ready SQL is a force multiplier on the rubric.

What an Interviewer Hears When They See "Correct But Not Production"

The opposite monologue:

"They can solve problems but they'll need a senior reviewing their PRs. Not promotion material yet — could maybe get there with 6-12 months of mentorship. I'll vote 'no hire' for senior, 'weak yes' for mid-level."

This is how candidates with technically-correct queries end up downleveled. They didn't fail the technical bar. They failed the production-readiness bar that was always quietly there.

The Production-Ready Checklist

Before declaring "I'm done" on any interview query, walk through this checklist out loud. The walk-through itself is a senior signal:

1. Nulls. Did I handle them in every aggregation and join?
2. Distinct. Am I counting rows when I should be counting entities?
3. Time windows. Are my boundaries explicit and correct?
4. Division. Did I NULLIF and cast to numeric where needed?
5. CTEs. Is each one doing one thing with a clear name?
6. Joins. Have I named the multiplicity (1-to-1, 1-to-many) for each?
7. Performance. Would this run on real data volume? Where's the bottleneck?
8. Readability. Would my teammate understand this in two minutes?

Twenty seconds of checklist walk-through at the end of every problem. Habit-forming, easy, and the difference between "correct" and "production-ready."

How to Build This

Production-ready SQL isn't a topic you study. It's a set of habits you build by writing queries that you re-read a week later.

The drill:

1. Write a query today, fully and to your current standard
2. Don't look at it for a week
3. Re-read it cold, as if a teammate wrote it
4. Note everything that's unclear, fragile, or off
5. Rewrite it with those fixes
6. Compare the two versions side by side

After ten cycles of this, your "first draft" becomes what your "rewrite" looked like. The habit becomes automatic. That's when interviewers start writing "production-ready" in the positive column of your rubric.

The candidates who level up from mid to senior do this drill consciously. The ones who plateau don't. SQL correctness is the floor. Production-readiness is the ceiling — and it's a learnable one.

Want a structured way to practice? Queryflo's Learn track includes interview-style problems with senior-quality reference solutions you can compare your work against. Five problems with cold-read review beats fifty without.