Using Window Function: Why row_number() doesn't work
Intuition\nFrom the problem description, since we need the first of each product, it shoud be easy to solve using the window functions. Hence, we need to rank t
# Intuition\nFrom the problem description, since we need the first of each product, it shoud be easy to solve using the window functions. Hence, we need to rank the years for each product, and then select the row with lowest value in year.\n\n# Approach\nI started by using the row_number() function to rank years for each product. However, only one test case passed with this approach.\n\nWhy?\n\nBecause row_number() always give a unique value to each row, even duplicates. In case of duplicates, it randomly assigns one year as higher rank and the other lower.\n\nSo now we could use either rank() or dense_rank().\n\nI preferred using dense_rank() since the rank numbers are continuous in this case.\n\n# Code\n```\nselect product_id, year as first_year, quantity, price from\n(select product_id, year, dense_rank() over (partition by product_id order by year) as firsty, quantity, price\nfrom Sales) as d\nwhere d.firsty= 1\n```
['MySQL']