🐘PostgreSQL ❗ Beats 91.48% ❗ Simple solution with FIRST_VALUE window function
Approach\nThis SQL query you have written aims to extract the first year, quantity, and price for each product in the sales table. The subquery assigns the firs
# Approach\nThis SQL query you have written aims to extract the first year, quantity, and price for each product in the sales table. The subquery assigns the first year of sales for each product using the `FIRST_VALUE` window function, and the outer query filters the results to include only the records where the year matches this first year.\n\n1. **Subquery (`stb`)**:\n   - `FIRST_VALUE(year) OVER (PARTITION BY product_id ORDER BY year) AS first_year`: This part assigns the first year of sales for each product.\n   - `PARTITION BY product_id`: This ensures the window function resets for each `product_id`.\n   - `ORDER BY year`: This orders the rows within each partition by the `year`.\n\n2. **Outer Query**:\n   - Filters the results where `year` is equal to `first_year`, effectively selecting only the rows where the year is the first year of sales for each product.\n\nThis query ensures you get the `product_id`, the first year it was sold (`first_year`), and the corresponding `quantity` and `price` for that first year.\n\n# Code\n```\nSELECT product_id, first_year, quantity, price\nFROM (\n    SELECT *,\n           FIRST_VALUE(year) OVER (PARTITION BY product_id \n           ORDER BY year) first_year\n    FROM sales\n) stb\nWHERE year = first_year;\n\n```
['PostgreSQL']