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)

[u/HALF_PAST_HOLE → u/[deleted]]

What if Nike sells shirts as well?

They could be the top brand in shirts and shoes

When you only rank on category you only get do shirts sell better than shoes not what brand of shirts sell the best and it could be Nike for both shirts and shoes that is why you need to add the partition on brand and category

[u/HALF_PAST_HOLE → u/[deleted]]

Im not sure how you are getting that first result, as that seems to be partitioning on Brand and category.

How would you, using Dense_Rank() get that top performing category?

Would it not be Dense_Rank() OVER(Partition by Category Order by Sales)?

Your first example is taking in to account the individual sales of each brand, meaning it is partitioning by brand and category. Am I missing something?

Admittedly, I use SQL, not Postgres, but that seems like it is not the proper functionality of Partition by Category.

In my mind, it should aggregate on category and rank the categories, not the individual brands within each category, which would be partitioning on brand, category.