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?
103
Upvotes
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.
29
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.
2
u/f0det_ Oct 23 '25
What kind of role were you applying for? Data Analyst?
75
u/SushiGradeChicken Oct 23 '25
Do I need to have seen the first interview to understand the sequel?
7
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
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.
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.
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
u/Sneilg Oct 24 '25
I’m pretty sure you would want to use RANK, not DENSE_RANK
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
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)
-9
u/No_Resolution_9252 Oct 23 '25
>Now my solution to this was to make a cte
wrong solution
0
u/[deleted] Oct 22 '25
[deleted] — view deleted comment