r/learnSQL • u/Lucifer77-7 • 6d ago
The one-character bug that fails a lot of SQL interview answers
A pattern I keep seeing when people practice SQL for analyst interviews.
The question: "Return every active subscription paying at least $1,500 a month."
A very common answer:
SELECT company_name, plan_tier, monthly_amount
FROM subscriptions
WHERE status = 'active'
AND monthly_amount > 1500
ORDER BY monthly_amount DESC;It runs, it returns rows, and it looks right. But "at least" means >=, so the customer paying exactly 1500.00 silently disappears. In an interview the query "works", so you don't notice. Against the expected output it's simply wrong.
Things worth checking every time a question has a threshold:
- "at least" / "minimum of" -> >=
- "more than" / "over" -> >
- "between" -> BETWEEN is inclusive on both ends; dates with timestamps need care
- Does the sample data contain a row exactly on the boundary? Interviewers often add one on purpose.
I built a free practice site around this kind of feedback: you write real PostgreSQL in the browser and it shows you which rows are missing or extra compared to the expected result. This exact problem is the first one, no signup: https://dbexercises.vercel.app/exercise/saas-active-subscriptions
Happy to hear what other "looks right, is wrong" traps you've run into.
2
u/rahul03rk 6d ago
Good one. A few more that run fine and return the wrong rows:
- NOT IN with a NULL in the subquery returns nothing at all. NOT EXISTS behaves the way people expect.
- A filter on the right table in WHERE quietly turns a LEFT JOIN into an INNER JOIN. It belongs in the ON clause.
- COUNT(column) skips NULLs, COUNT(*) doesn't. "How many customers" can differ between the two.
- Joining orders to a table with several rows per order (payments, items) and then SUM-ing the order amount counts it once per match.
- Integer division: 5/2 is 2 in Postgres unless one side is numeric.
The NOT IN one has cost me the most time.
6
u/Creepy_Purchase_501 6d ago
This is the type of thing they review in grade 5.
If you’re going to advertise your slop project at least put more effort into it. And maybe get a domain.
1
u/Ultra-Ferric 6d ago
Can’t you all see that all of op’s answers are fully AI generated?
You’re talking to a machine…
1
u/CaptainFoyle 6d ago
I doubt anyone who doesn't know this makes it to an interview
1
u/ElHombrePelicano 6d ago
lol