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"?