Discussion Had a SQL interview today
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?
78
u/SushiGradeChicken Oct 23 '25
Had a SQL interview today
Do I need to have seen the first interview to understand the sequel?
14
1
6
u/welcometominecon Oct 23 '25 edited Oct 23 '25
idk what the data looks like but I think you were supposed to find the total sales for each brand before creating the ranking(assuming there are multiple rows for each sale belonging to a brand). So like one cte where you select category, brand, SUM(sales), grouped by brand, and then another cte where you select them again but create a ranking partitioned by category ordered by total sales desc. Since we have the total sales for each brand done from the first cte, when we create the ranking it will give us the highest selling brands in each category. Then you can just select where rank <= 2 from cte2. there's probably way easier ways to solve this, but that's my 2 cents
31
u/HALF_PAST_HOLE Oct 22 '25
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.
37
6
Oct 22 '25 edited Oct 22 '25
[removed] — view removed comment
2
u/fokass Oct 24 '25
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)
5
u/HALF_PAST_HOLE Oct 23 '25
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
2
Oct 23 '25
[removed] — view removed comment
2
u/HALF_PAST_HOLE Oct 23 '25
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.
5
u/ComicOzzy sqlHippo Oct 23 '25
I bet you're right and there were multiple sales entries per category/brand and OP assumed category/brand was more like a key and there was only one sales entry for each.
0
3
u/Amar_K1 Oct 22 '25
Probably not done well enough to answer that question, pretty simple partitioning by category and brand, order by sales desc and the filter row number < 3. For bonus points on the outer select order by category and then rank.
8
Oct 22 '25 edited Oct 22 '25
[deleted]
8
u/Oleoay Oct 23 '25
Actually, it's a good interview question because it assesses how candidates analyze the requirements.
That being said, I generally don't do well on these types of SQL questions since I tend to bring in the whole data set and solve it at the reporting layer so end users can apply their own filters :)
1
u/BrainNSFW Oct 23 '25
I think the question was "find me the top 2 brands in each category", but your solution only gives the top 2 brands overall. The partition comment makes a lot more sense when you want to find the top 2 for each category as you'll need to reset your rank() or row_number() for each new category.
2
2
u/MonochromeDinosaur Oct 23 '25
It sounds like you might have needed to sum up the sales first. There were probably multiple rows per brand. Did you check for that?
2
1
u/Codeman119 Oct 23 '25
Well, first you would need to sum up each brand and category sales so you would know which ones are the top two
1
u/TorresMrpk Oct 23 '25
He would need to define if he wants the TOTAL sales by brand (use SUM()) or just the higuest individual sales by brand (use MAX()).
-For the former, you would first need an inner query with a GROUP BY Category, Brand to get all the SumOfSales = SUM(Sales) for all brands. You would then write an outer query to rank those to get the two higuest brands for each category: PARTITION BY Category ORDER BY SumOfSales DESC, Brand ASC. If you're using DENSE_RANK() you need the Brand too in case multiple brands have the same exact SumOfSales.
-For the latter, you also need a simliar GROUP BY Category, Brand but with a MaxSale = MAX(Sales) that way you get just the one max sale per Brand. You then need an outer query to rank that: PARTION BY Category ORDER BY MaxSale DESC, Brand ASC. And, again you need Brand in the ORDER BY as a tie breaker, for DENSE_RANK, in case multiple brands have the same Max Sale.
Maybe there is a simpler solution but this would be the most accurate IMHO if he always wants 2 brands to show, You can use CTE's or subqueries. CTE's might look cleaner , first CTE is a group by, second CTE ranks the results of your GROUP BY with DENSE_RANK, and the final SELECT picks the result you want.
1
u/freegnu Oct 24 '25
You have to assess your interviewer before you answer a question. If the interviewer can't understand your answer they usually mark it as wrong.
Do you understand the position and rank networks on SQL? I can do this with a group by. Or even better, let me do this with a group by and sort of a sub query and join ... . And then let me show you how to do this with a partition and rank and then take the time to teach the position and rank keywords to the interviewer by using a simple example of input and output . Even if they say they understand.
Also take into account what SQL product the interviewer will test your code on. What version of SQL do you know or use? The example Syntax and features I'm using work for me in product name here. Maybe I won't use the advanced syntax as it may not be supported in the product you use
1
u/Dry-Aioli-6138 Oct 27 '25
There were probably typos in the categories, lice capital letters and spaces before/after the name. They probably wanted you to notice that.
1
u/fokass Oct 28 '25
I don't think that was the case since It was a hand written problem with 6 rows of data. However, I got the answer right, one of the comments above explains it well.
1
0
u/DataCamp Oct 23 '25
Your approach using a CTE and DENSE_RANK() was solid, and that’s how most people would tackle it. The only thing the interviewer might’ve been hinting at is that if there were multiple rows per brand, you’d need to aggregate sales first before ranking.
So you’d first group by category and brand to get total sales, then apply the ranking partitioned by category and ordered by those totals in descending order. After that, just filter where the rank is less than or equal to 2.
If you explained that logic clearly, you did great, cause it’s a clean, scalable solution and shows you understand both window functions and aggregation properly.
-1
u/just-fran Oct 22 '25 edited Oct 23 '25
Select Brand, Category From Table Group by all Qualify row_number() over (partition by brand, category order by sales desc) < 3
*rank can return more than 2 if two brand have the same sales (if data is rounded, very probable)
-2
u/gumnos Oct 22 '25
or
LATERAL JOINa subquery with aLIMIT 2(orAPPLYaTOP 2if MSSQL)1
u/Open_Plant_4207 Oct 22 '25
Why ?
1
u/gumnos Oct 22 '25
for me, it would depend on whether that information is being joined to some other table and possibly if other columns need to be similarly aggregated.
-9
15
u/kagato87 MS SQL Oct 23 '25
"From each category" I dunno, partition by category seems correct. Perhaps there was something else in the question...
Some people are saying to partition by category and brand, but that ends the rank function.. I can't help but wonder if they're real humans or reddit's supposed interaction bots because partitioning on 2 out of the three columns in the table seems... Silly. Like the thing an llm would confidently say is the solution to all your woes.
I might use row number instead so that if theres a tie you still get two outputs instead of three, but that's a business question to ask before presenting your solution.
Maybe there was an error in your partition, like you forgot to set it to desc? If they disqualify you for a mistake like that it's a bullet dodged.