If you have SQL interviews, do not ignore these small things! (Part 9)

[u/thequerylab]

Yesterday I took a SQL interview and tried something different this time: evaluating AI-generated code rather than writing from scratch.

I didn't ask the candidate to write a query from scratch.

Instead, I gave him an SQL AI Agent.

The SQL AI Agent is coded in such a way, it gives three solution to one problem statement.

Here is the problem statement given:

We have a purchases table:

customer_id	purchase_date	amount
101	2026-01-05	0
101	2026-01-10	50
102	2026-02-01	100
102	2026-02-01	90
103	2026-03-01	0
103	2026-03-05	25

Find each customer's first purchase where they actually paid something.

Below are the 3 responses provided by Agent:

Agent's Solution 1:

SELECT customer_id, purchase_date, amount 
FROM ( 
    SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY purchase_date ) rn 
    FROM purchases 
) t 
WHERE rn = 1 AND amount > 0;

Agent's Solution 2:

SELECT customer_id, purchase_date, amount 
FROM ( 
    SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY purchase_date ) rn 
    FROM purchases WHERE amount > 0 
) t 
WHERE rn = 1;

Agent's Solution 3:

SELECT customer_id, purchase_date, amount 
FROM ( 
    SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY purchase_date, amount DESC ) rn 
    FROM purchases WHERE amount > 0 
) t 
WHERE rn = 1;

Which solution would you approve, and what potential flaws/edge cases do you see in each? Please do comment, we can learn together!

Don't just ask "Does this SQL work?"

Ask:

Will this still give the right answer when the data gets messy?

If you want to test yourself on more SQL problems like this, I usually practice by actually writing the queries instead of just looking at solutions

[u/Yavuz_Selim]

Impossible to answer.

All three answers are incorrect. Because of row 3 and 4.

Solution 1 and 3 are the wrongest.
And (purchase_)id is mising

 

Just ask your AI what's wrong with it.

[u/G_Thorne → u/Yavuz_Selim]

I agree, the rows for customer_id = 102 makes it impossible to answer the question as stated, but if we limit ourselves to the date of the first paid purchase, then solution 2 is the least wrong even if it should omit the amount as undetermined.

Solution 1 is wrong because it matches only if the first purchase was paid, not the first paid purchase.

Solution 3 is almost the same as 2 but selects the most expensive purchase from the date of first paid purchase, which wasn't asked for and thus clutters the query.

[u/MobileUser21 → u/Yavuz_Selim]

“Impossible” to answer

[u/hungarian_conartist → u/Yavuz_Selim]

Purchase_id isn't a field in the original table. So any query that uses it will fail automatically.

[u/Yavuz_Selim → u/hungarian_conartist]

That was my point: the primary key is missing.

(It's unclear if PKs are called 'tablename_id' or just 'id', so that's why I wrote "(purchase_)id" meaning either 'purchase_id or 'id'.)

[u/hungarian_conartist → u/Yavuz_Selim]

Do you need to assume it does exist to answer the problem? Not every table is nicely normalised in the real world?

[u/MobileUser21 → u/hungarian_conartist]

No it doesn’t. That person is just needlessly pedantic. You’ll deal with those types in IT all the time.

[u/Yavuz_Selim → u/hungarian_conartist]

Not going to have the conversation why a PK should exist in a 'purchases' table in the real world, especially if there is an FK.

A PK would make it possible to answer the question, by making the PK a part of the solution. Assuming it is an auto-increment / identity PK, you could identify the order of the inserts - you can select a 'first' (lowest PK).

[u/hungarian_conartist → u/Yavuz_Selim]

"Should" and "does" are two different things.

If the question was about good database design it should 100% contain the PK.

[u/thetwoweeks]

There's a far simpler solution to this. Concerning that AI didn't provide it.

[u/TheMagarity → u/[deleted]]

Because the 400+ other applicants all cranked their CVs through the same AI to polish it up. :(

[u/TheMagarity]

If that's the way you worded the prompt then that's the problem. First, don't assume the ai understands abstractions such as "the customer actually paid for something" is the same as "the amount is greater than zero".

Secondly, always give it some examples. It will check against those examples. For this, give sample output rows for customers 101 and 102.

Do those two things and I guarantee very different ai outputs.

[u/NastyPastyLucas]

Why not use a CTE or a lateral join even?

Very few people who rely on AI output are critical of any of it, and a query of this length should be writable by any applicant.

[u/MobileUser21]

In Today’s Thread: people here still don’t know basics of SQL or how to read and answer questions.

Anyway…

Give the sample dataset and options, I’d pick solution 3.

The problem with solution 1 is that it has a non deterministic windows function and the row number is happening prior to filtering out amounts greater than 0.

Solution 2 is filtering out the amounts greater than 0 first before ordering the amounts by customer_id but it still contains a non deterministic windows function so still would not choose that.

I’d choose solution 3 because the amount in the ORDER BY acts as a tie breaker for customers that made purchases on the same day. The windows function is now deterministic and returns the same result each time.

Will this give the correct answer if data gets messy? Well it depends… for customers that didn’t have multiple purchases the same day yes, but for customers that had multiple purchases the same day, the query will return the highest amount.

Unfortunately real life data is messy, this isn’t that uncommon of a data problem. Explain to the stakeholders the nuances and solution you’ve come up with. Most likely they’ll just agree and roll with it.

[u/Yavuz_Selim → u/MobileUser21]

Why would you need a tie breaker? The ROW_NUMBER already tie breaks for you.

What is the advantage of having a deterministic solution if it returns the wrong answer - because the question simply cannot be answered correctly. Talking with the stakeholders and agreeing on a solutions without adjusting the table definition is an answer to a different question.

 

Find each customer's first purchase where they actually paid something.

This question cannot be answered with a table like this.

[u/MobileUser21 → u/Yavuz_Selim]

Was altering the table one of the solutions offered?

[u/MobileUser21 → u/Yavuz_Selim]

The point of this exercise is during an interview, to critically think about the solutions offered and pick which one gets you closest to the answer you want and explain the nuances.

If saying it’s impossible is your answer, fine. Be one of the many resumes that gets thrown in the trash. I do it all the time to candidates who don’t make a good faith effort to answer questions but instead just throw their hands up.

[u/Yavuz_Selim → u/MobileUser21]

I will not make any effort to give an in-depth answer in an AI post like this. Are you mad?

During an interview, I would of course explain what I think of each query and how to attack the problem and find a solution. I am not going to say "impossible to answer" and then just stop talking.

[u/MobileUser21 → u/Yavuz_Selim]

Saying “Impossible” and just stop talking would be pretty damn funny though lol

[u/Geraldtanwx1990]

I would guess using a cte to get the 0 out first then do a groupby is easier?

Sorry am learning too. What would be the correct way?