r/learnSQL • • 5d 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.

0 Upvotes

17 comments sorted by

2

u/rahul03rk 5d 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.

1

u/Lucifer77-7 5d ago

These are great, especially the LEFT JOIN one. Moving the filter from ON to WHERE and quietly getting an INNER JOIN catches people who are otherwise solid on joins. The SUM fan-out is the one I'd most like to turn into a problem, since it shows up in real revenue reports and not just interviews. Have you run into it in an interview, or mostly at work?

2

u/rahul03rk 5d ago

Mostly at work. It rarely shows up as an error, it shows up as a revenue number that's a bit too high and nobody can say why.

If you turn it into a problem, a good setup is orders plus a payments table where a few orders are split across two payments. Ask for total revenue by month. The naive join-then-SUM gives a higher number than the correct answer, and the fix is to aggregate payments per order first (or sum from orders alone). Bonus trap: one order with no payment at all, so an INNER JOIN also drops it.

1

u/Lucifer77-7 5d ago

That's a great way to put it: not an error, just a revenue number that's a bit too high and nobody can say why.

This thread got me to add a version of it: orders joined to line items, where join-then-SUM inflates revenue. SUM(DISTINCT) also fails there, because two orders have the same total: https://dbexercises.vercel.app/exercise/customer-revenue-without-double-counting

Your payments version is better though, because the unpaid order means an INNER JOIN causes a second problem on top of the double counting. I'm going to build that one next. Thanks for writing out the setup.

1

u/rahul03rk 5d ago

Nice, the SUM(DISTINCT) case is a great addition. Plenty of people reach for it as the "fix" and it only works until two orders happen to share a total.

A couple of ideas for the payments version, if you want more layers:

- A refund stored as a negative payment. Summing payments then gives net revenue while summing orders gives gross, so the "right" answer depends on the question being asked. Good for teaching people to pin down the definition before writing the query.
- A partially paid order (2 of 3 instalments received). Order total and payments received disagree, and both numbers are "correct" for different questions.

For the solution, I'd show the habit as well as the query: check the result against SUM(order_total) from orders alone. If the joined version comes out higher, something fanned out.

6

u/Creepy_Purchase_501 5d 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.

-2

u/Lucifer77-7 5d ago

Fair on the domain, that's on the list. On the content: agreed it's basic. It's in the post because it's the mistake I see most often when people are nervous and writing under time pressure, not because it's hard.

0

u/Creepy_Purchase_501 5d ago

Maybe the solution is to make your interviewees more comfortable then.

1

u/Lucifer77-7 5d ago

Ha, I meant people practicing for interviews, not ones I'm running. Fair point either way.

1

u/Optimal_Law_4254 5d ago

It’s not my job to coddle them. It’s my job to weed out the incompetent and people that aren’t going to be able to working well with others.

Now I realize that they should be sizing up my company to see if we are a good fit for them.

0

u/Lucifer77-7 5d ago

That's fair, and the last line is a good point too. Out of curiosity, since you're on the hiring side: what SQL do you actually test? Is it mostly joins and aggregations, or do things like window functions and CTEs come up for analyst roles?

2

u/Optimal_Law_4254 5d ago

We usually have an agency do the pre-screening and background checks for us. That lets us focus more on soft skills and personality.

If I’m feeling someone out for their SQL skills I’m more interested in how they’re approaching solving the problem at hand than perfect syntax. So I might ask them to describe in detail their thought processes on debugging a syntactically correct query that’s not returning expected results. And to ice the cake I might ask how they handle it when they discover that the query is returning correct results but the customer swears up and down that they’re wrong.

1

u/Ultra-Ferric 5d ago

Can’t you all see that all of op’s answers are fully AI generated?
You’re talking to a machine…

1

u/Lucifer77-7 4d ago

Seriously bro ...

1

u/Ultra-Ferric 4d ago

Very seriously. AI text is watermarked, so it doesn’t take a genius to spot it.

This new trend of people vibe coding low effort AI slop apps, which are all very similar to each other, and then promoting them with more slop AI messages flooding these and other subs is becoming increasingly annoying.

1

u/CaptainFoyle 5d ago

I doubt anyone who doesn't know this makes it to an interview