Forum

Posted by Oscar, Oct. 5, 2022, 12:29 p.m.

Why is my answer "wrong"?: 110. Most popular product by category

My solution below was marked as "wrong", why?

SELECT product_category_id, product_id, ranking
FROM (
     SELECT p.product_category_id, o.product_id, sum(o.qty), 
     RANK() OVER(PARTITION BY product_category_id ORDER BY SUM(qty) DESC) AS ranking
     FROM orders o
    JOIN product_category p on o.product_id = p.product_id
     GROUP BY 1,2
) x
WHERE ranking = 1

 

Yet the solution is this:

WITH prod_cat_qty AS (    
    SELECT product_category_id, O.product_id, SUM(qty) AS quantities
    FROM orders O
    INNER JOIN product_category C
    ON C.product_id = O.product_id
    GROUP BY product_category_id, O.product_id
)

SELECT product_category_id, product_id, quantities, ranking
FROM (
    SELECT product_category_id, product_id, quantities, RANK() OVER(PARTITION BY product_category_id ORDER BY quantities DESC) as ranking
    FROM prod_cat_qty
) X
WHERE X.ranking=1

 

Both yielded the exact same output. Would my solution in an interview me looked as "wrong"?

Answers

Hi Oscar -

Your query works, but you left the QUANTITIES column out of the outer query so it wasn't matching everything as expected. 

@Bhavesh - I'm not sure if this is the topic you meant to post under but I don't think that query is related to this question.

Mike, Oct. 9, 2022, 9:22 a.m.

[Wrong Window]

Solution without RANK, DENSE_RANK

SELECT category
FROM sales_by_film_category
ORDER BY total_sales DESC
LIMIT 3;

Bhavesh, Oct. 8, 2022, 5:46 p.m.
SQLPad user avatar

Mike (228)

Oct. 5, 2022, 2:28 p.m.