Subqueries, CTEs & Readable SQL: Interview-Ready Query Thinking
A food-delivery analyst does not start with “write SQL.” She starts with: “Which restaurants had repeat customers last month, but lower average order value than the city average?” The answer may be one query - but if the logic is unreadable, the business decision becomes risky.
- A subquery is best when you need a small supporting result inside a larger query - for example, “above average sales.”
- A CTE, written using
WITH, is best when the logic has steps - filter, aggregate, rank, compare, then present. - Readable SQL is business communication: it makes assumptions, filters, grain, and transformations obvious.
- Use CTEs to name thinking:
eligible_orders,customer_spend,city_average,final_output. - Do not assume CTEs always improve performance; many databases inline them, and optimizers differ.
- Interview gold line: “I would first define the grain, then build the query in verifiable layers using CTEs.”
The Big Picture
Subqueries and CTEs solve the same core problem: breaking a hard business question into smaller SQL questions. The difference is shape. A subquery hides a step inside another query; a CTE names the step before using it.
Core Explanation: Subquery vs CTE vs Readable SQL
The simplest way to remember the difference: subqueries answer supporting questions inline; CTEs answer supporting questions in named steps.
1. Subqueries: useful when the supporting answer is small
A subquery is a query inside another query. It usually appears in WHERE, FROM, or SELECT.
Example question: “Show customers whose total spend is above the average customer spend.”
SELECT customer_id, total_spend
FROM customer_spend
WHERE total_spend > (
SELECT AVG(total_spend)
FROM customer_spend
);The inner query calculates the average. The outer query compares each customer against it. This is clean because the supporting logic is short.
2. CTEs: useful when the logic has multiple layers
A Common Table Expression uses WITH to create a named temporary result for one SQL statement. It makes the query read like a storyboard.
WITH customer_spend AS (
SELECT customer_id, SUM(order_value) AS total_spend
FROM orders
GROUP BY customer_id
),
average_spend AS (
SELECT AVG(total_spend) AS avg_spend
FROM customer_spend
)
SELECT cs.customer_id, cs.total_spend
FROM customer_spend cs
CROSS JOIN average_spend a
WHERE cs.total_spend > a.avg_spend;This is longer, but more readable. The interviewer can see each assumption: first aggregate by customer, then compute average, then compare.
3. Readable SQL: the interviewer should understand your logic before the result
Readable SQL is SQL written for verification. It answers three silent questions: What is the grain? What rows are included? What transformation happens at each step?
Definitions You Can Say in One Breath
- Subquery: A query nested inside another SQL statement, returning values the outer query uses.
- Common Table Expression: A named temporary result set defined with
WITHand used within one SQL statement. - Readable SQL: SQL structured so another analyst can verify logic, assumptions, and output without guessing.
Where Each SQL Pattern Fits
Do not treat subqueries, CTEs, views, temp tables, and window functions as interchangeable. Each has a best-use zone.
A Worked Example: Above-Average Customers
Suppose an e-commerce analyst has this monthly customer spend table.
Business question: Which customers spent more than the average customer?
Average spend = (₹1,000 + ₹2,000 + ₹3,000 + ₹4,000) / 4 = ₹2,500. So customers C and D qualify.
Readable CTE version:
WITH customer_spend AS (
SELECT customer_id, monthly_spend
FROM monthly_customer_spend
),
average_spend AS (
SELECT AVG(monthly_spend) AS avg_spend
FROM customer_spend
)
SELECT c.customer_id, c.monthly_spend
FROM customer_spend c
CROSS JOIN average_spend a
WHERE c.monthly_spend > a.avg_spend;The important interview point is not the syntax alone. It is the reasoning: define the customer-level grain, calculate the benchmark once, then compare every customer to the same benchmark.
The SQL Execution Mental Model
SQL is written in one order but logically processed in another. This is why beginners get confused when aliases from SELECT do not work in WHERE.
Decision Rule: Which One Should You Use?
Case Study: Razorpay and the Logic of Settlement Reconciliation
Razorpay operates in Indian digital payments, where clean transaction, refund, fee, and settlement logic is essential for merchant trust.
Razorpay is a strong Indian example because payment data is naturally multi-step. A merchant does not only ask, “How much did I sell?” The real question is closer to: “Which successful payments are eligible for settlement, after refunds, charges, taxes, and settlement timing rules?”
Situation: In payment businesses, a single customer payment can touch multiple tables - transaction status, refund status, merchant account, fees, taxes, payout batch, and bank settlement. If analysts write one deeply nested query, small mistakes can misstate merchant payouts.
The move: The clean SQL approach is to layer the business logic using CTEs: first define successful payments, then subtract refunds, then apply fee and tax logic, then map to settlement batches, then produce the merchant-level output.
Outcome or lesson: The win does not come from CTEs alone. The primary driver is explicit business logic at the correct grain, supported by clear naming, auditable intermediate outputs, and separation of eligibility, adjustment, and final payout rules. That is exactly how SQL becomes decision-safe.
How AI Changes Subqueries, CTEs & Readable SQL
AI does not remove the need to understand SQL. It raises the bar: anyone can generate a query; strong candidates can verify whether the query is logically correct.
- Text-to-SQL is now common: Tools can convert “find high-value repeat customers” into SQL, but they often miss grain, join keys, date filters, and duplicate handling.
- AI can refactor messy SQL: You can paste a nested query into ChatGPT or Claude and ask it to rewrite the logic as named CTEs with comments for assumptions.
- AI helps debug query intent: Tools can explain what each CTE does, identify unused CTEs, flag suspicious joins, and suggest where a window function is cleaner than a subquery.
Use ChatGPT or Claude like a SQL reviewer: paste your query and ask, “Identify the grain, list every filter, detect duplicate-risk joins, and rewrite this using readable CTEs.” Then verify the answer manually with 3-5 sample rows.
Interview Relevance
“You have an orders table with customer_id, order_date, city, and order_value. Write or explain a query to find customers whose monthly spend is above their city’s average monthly spend. Would you use a subquery or a CTE?”
Before writing SQL, say: “I will avoid a deeply nested query because the interviewer should be able to inspect each business rule.” That signals analyst maturity.
Common Mistake
The single biggest mistake is writing syntactically correct SQL without stating the grain. It costs candidates because the query may accidentally compare customer-level spend with order-level averages or city-level totals. One-line fix: before coding, say, “My output grain is one row per customer per month per city.”
What to Revise Next
Once subqueries and CTEs are clear, move to the SQL patterns interviewers reuse for analytics problems: first ranking and time-aware comparisons, then date and cohort logic.