Ian Klosowicz
The SQL habits that make you look junior are the small ones that work fine on tiny practice tables and fall apart on real data: SELECT * everywhere, subqueries stacked 4 deep instead of CTEs, DISTINCT slapped on to hide a broken join, functions wrapped around the column you're filtering, and query text nobody can read. Each one runs. That is why they survive. They signal you have not had to maintain your own SQL yet, or hand it to someone else who did.
Here are the specific habits that give it away, why each one reads as junior to the person reviewing your code, and the fix for every one. None of these take long to change, and fixing them is the fastest way to make your SQL read like someone who has done this at work.
The fix is to name the columns you actually need. SELECT * is the clearest tell, because it says you grabbed everything rather than deciding what the query is for.
On a small table it looks harmless. On a real one it pulls columns the query does not use, drags extra data across the network, breaks when someone reorders or renames a column, and quietly inflates a view or a downstream model that selects from your query. A reviewer sees SELECT * in a production query and assumes the rest of the logic got the same amount of thought.
Name the columns. It's more typing once and less debugging later. The only fair place for SELECT * is a quick ad hoc look at a table you are exploring, and even then I usually add a LIMIT.
The fix is to find why the rows are duplicated, which is almost always the join. DISTINCT at the top of a query that suddenly has duplicates is a junior move, because it treats the symptom and leaves the cause running.
Here is what usually happened: you joined to a table that has more than one matching row per key, so every row fanned out into several, and DISTINCT collapsed them back down. The query now returns the right count by luck. Change the data and it breaks, or a SUM downstream double counts before the DISTINCT ever runs.
When you see unexpected duplicates, check the join first:
I review a fair amount of SQL from people trying to break in, and DISTINCT covering a fan-out join is one of the most common things I flag. The query looks correct and is wrong the moment the data changes.
The fix is to pull nested subqueries into CTEs with names. A query with 3 or 4 levels of subquery nested inside the FROM clause is readable only to the person who wrote it, that day.
A common table expression, the WITH clause, lets you name each step and stack them top to bottom. Instead of reading a query inside out, someone reads it in order: here is the base set, here is the filtered version, here is the aggregate, here is the final join. Same logic, but now it has a shape a person can follow.
CTEs also make debugging faster, because you can select from any named step to see what it returns. Nested subqueries force you to untangle them first. Reaching for a CTE is one of the clearest signals that someone has had to maintain their own SQL, and it is a habit worth building early. It shows up constantly in the daily drills inside Analyst Hive for exactly that reason.
The fix is to keep the column bare on one side of the comparison and move the function to the other side. Writing WHERE YEAR(order_date) = 2025 looks reasonable and quietly stops the database from using an index on order_date.
When you wrap a column in a function inside the WHERE clause, the database has to compute that function for every row before it can compare, so any index on the raw column goes unused. On a large table that turns a fast lookup into a full scan.
Rewrite the filter so the column stays untouched. Instead of filtering on YEAR(order_date), filter on a range: order_date greater than or equal to the start of the year and less than the start of the next year. Same result, and the index still works. The same idea applies to wrapping columns in UPPER, CAST, or date functions inside a WHERE or JOIN. Keep the column bare and transform the constant instead.
The fix is to format it like you expect another person to open it, because someone will. A 40 line query on 3 lines with no indentation and lowercase keywords mixed with caps reads as someone who writes SQL for themselves and stops there.
Formatting is how a reader finds the FROM, the joins, and the filters at a glance, and how you catch a missing join condition or a stray AND. A few habits cover most of it:
Most editors will auto-format for you, so this costs almost nothing. It is one of the cheapest upgrades to how senior your code looks.
The fix is to treat NULL as its own case, because NULL is not equal to anything, including another NULL. This trips up almost everyone early, and the bugs it causes are silent.
Two patterns cause most of the trouble:
There is also the quiet one: a COUNT of a column skips NULLs, while COUNT(*) counts every row. Mixing those up gives you two different numbers and a long afternoon figuring out why. Knowing how NULL moves through a query is one of the lines between someone who passes a SQL screen and someone who gets surprised by it.
The fix is to use explicit JOIN syntax and alias tables for what they are. Joining with commas in the FROM clause and putting the join condition in the WHERE is old style, and naming your tables a, b, and c makes a multi-join query impossible to follow.
Explicit joins read better and fail louder:
Implicit comma joins also make an accidental cross join easy: forget the condition in the WHERE and you get every row times every row, with no error. Explicit JOIN makes the missing ON stand out.
The fix is to remember the order: WHERE filters rows before they are grouped, HAVING filters groups after the aggregate. Trying to filter an aggregate in the WHERE clause, like WHERE COUNT(*) greater than 5, throws an error, and juniors often work around it by nesting the whole query in a subquery instead of using HAVING.
The simple rule:
Putting a plain column filter in HAVING also works in some databases but reads as confused and can be slower, because you grouped rows you could have dropped earlier. Filter early with WHERE, aggregate, then filter the groups with HAVING. Getting that order right is a small thing that tells a reviewer you understand how the query actually executes.
Is SELECT * always bad?
No. For a quick look at an unfamiliar table it is fine, usually with a LIMIT. The habit to avoid is SELECT * in saved queries, views, and anything downstream code depends on, because it pulls columns you don't need and breaks when the table changes. In production SQL, name the columns.
Why is DISTINCT considered a junior habit?
DISTINCT itself is fine. It reads as junior when it is used to remove duplicates that a join created, because that hides the cause and leaves it running. The duplicates usually mean the join fanned out on a non-unique key. Fix the join or aggregate first, and reach for DISTINCT only when the duplicates are genuinely expected.
Are CTEs better than subqueries?
For readability, usually yes. CTEs let you name each step and read a query top to bottom instead of inside out, and you can select from any step to debug it. Performance is similar in most modern databases. The main win is that the next person, including future you, can follow the logic without untangling nested subqueries.
How do I write a WHERE clause that still uses an index?
Keep the indexed column bare on one side of the comparison and move any function to the constant. Instead of WHERE YEAR(order_date) = 2025, filter on a date range so order_date itself is compared directly. The same rule applies to UPPER, CAST, and other functions. A column wrapped in a function usually cannot use its index.
What SQL mistakes do interviewers notice most?
The ones that show up fast on a shared screen: SELECT *, no formatting, DISTINCT hiding a join problem, and shaky NULL handling. None of them are about knowing an obscure function. They are about whether your SQL looks like something you would be comfortable handing to a teammate, which is what the interviewer is really checking.
How long does it take to fix these habits?
Most are a single session of awareness plus a few weeks of catching yourself. Naming columns, formatting, and explicit joins are pure habit and change immediately. NULL behavior and join fan-out take a bit of practice because you have to see the bugs a few times. The payoff is fast, since reviewers notice clean SQL right away.
These habits come from learning on small data, before you have had to maintain queries other people rely on. That experience is what closes the gap, and you can build it on purpose instead of waiting for a job to hand it to you. Daily reps on realistic tables, with someone pointing out the fan-out join or the unindexed filter, is what Analyst Hive is built for: a 90-day daily-task program for aspiring data analysts who want SQL that reads like they have done this before.
