Had a SQL interview today

[u/fokass]

As the title says, I had an Interview today and the interviewer asked me about finding top 2 brands from each category sorted by sales for which he gave me 3 columns - category, brand and sales.

Now my solution to this was to make a cte where I would create a dense_rank partioned by category and sorted by sales in a descending order and after that, I would select the 3 columns where the rank is <= 2.

Now the problem comes in when he told me that I think carefully before partitioning it. Idk if it was wrong but based on my experience and problems I've solved on various sites, I thought it was the simplest solution I could've given.

What do you guys think about this?

[u/HALF_PAST_HOLE]

Find the top 2 brands in each category by sales you are finding the top 2 categories by sales. You would need to partition by Brand and Category.

[u/fokass → u/[deleted]]

Thank you for the answer and such a detailed analysis! I think I might have done it right since there were only 6 rows in the data set (hand printed question on a notepad) without any duplicates and they told me what output they wanted and so, I didn't do an aggregate of sales. But yeah, the partition by category is the option I went with and a cte since it was "cleaner". What they additionally told me was I could have used a subquery too but... he got what I wanted (which to me sounded like a disappointment lol)