Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Problem sales analysis-Leet Code Question 1070

Here’s the problem statement
Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Input:
Sales table:

+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

My solution

    select product_id, min(year) as first_year, quantity, price from Sales 
    GROUP BY product_id;

How is this solution incomplete and why does this query pass 1 test case and fails the rest?

Tags:

2

Answers


  1. 0

    Here is the solution:

    select product_id, year as first_year, quantity, price from 
(select * from Sales order by year asc limit 100000) group by product_id
    1. sort the data by year(limit is important to sort, the sort is invalid if without the sort)
    2. group by
    Login or Signup to reply.
  2. 0

    Well first of all, the query you just posted is not correct. Because here in this query:

    select product_id, min(year) as first_year, quantity, price from sale
GROUP BY product_id;

    You are trying to print out quantity and price after applying group by which is not possible. Because this is not how group by works, It is gonna split your whole table into smaller iterable groups of rows based on some common factor (which here in your case is product_id) It will make small groups of rows having same product_id. And it will apply aggregation over those only but we know aggregation returns a single value and the other columns you are trying to print can not be printed because the are supposed to have multiple values which again can not be printed because one cell contains a single value.(1st Nomal Form).

    And comming back to main topic how the hell was it gonna be solved so here is the actual query that may solve the problem:

    SELECT s.product_id, s.year AS first_year, s.quantity, s.price
FROM sale s
JOIN (
    SELECT product_id, MIN(year) AS first_year
    FROM sale
    GROUP BY product_id
) t
ON s.product_id = t.product_id AND s.year = t.first_year;

    I hope it helps you out, otherwise you can ask me to explain it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search