leetcode SQL50

1070. Product Sales Analysis III: Double column filtering with IN keywords

# Write your MySQL query statement below
select product_id ,year as first_year,quantity,price
from Sales
where (product_id,year) in (select product_id,min(year) from Sales group by 1);

1661. Average Time of Process per Machine: special join condition

select A1.machine_id,round(avg(A2.timestamp -A1.timestamp),3) as processing_time from Activity A1
JOIN Activity A2 ON A1.machine_id = A2.machine_id
AND A1.process_id = A2.process_id
AND A1.activity_type = 'start'
AND A2.activity_type = 'end'
group by A1.machine_id

difficulty: Product Price at a Given Date

get 2nd largest

select max(salary) as SecondHighestSalary 
from employee
where salary < (select max(salary) from employee);

1484. Group Sold Products By The Date: row rolling up + string concatentation