[SQL] LeetCode Easy Database Problem (21~32)

21. Recyclable and Low Fat Products

SELECT product_id
FROM Products
WHERE SUM(low_fats, recyclable) = 2
low_fats = TRUE AND recyclable = TRUE

22. Warehouse Manger

SELECT W.name,  SUM(Width*Lenght*Height*unit) as volume
FROM Warehouse W
JOIN Products P
ON W.product_id = P.product_id
GROUP BY W.name

23. Average Time of Process per Machine

//기계별 프로세스 처리량 구하고 그걸 다 더하고
SELECT machine_id, avg(process_time)
FROM (SELECT machine_id, 
			IF(activity_type = 'end', timestamp)
			- IF(activity_type = 'start', timestamp) as process_time
			FROM Activity
			GROUP BY machine_id, process_id
)
GROUP BY machine_id

SELECT a.machine_id, AVG(b.timestamp - a.timestamp) as processing_time
FROM Activity a
JOIN Activity b
ON a.machine_id = b.machine_id AND a.process_id = b.process_id
WHERE a.activity_type = 'start' and b.activity_type = 'end'
GROUP BY machine_id

//self join과 subquery 중 성능이 더 나은게 뭐

24. Top Travellers (COALESCE)

SELECT name, COALESCE(sum(distance), 0) as travelled_distance
FROM Users
LEFT JOIN Rides ON Users.id = Rides.user_id
GROUP BY Users.id

25. Find Total Time Spent by Each Employee

SELECT event_day AS day, emp_id, SUM(out_time-in_time)as total_time
FROM Employee
GROUP BY event_day, emp_id
//ORDER BY event_day asc
ORDER BY 안해도 됨. ANY ORDER

26. Employee Bonus (NULL계산할때 COALESCE)

SELECT name, bonus
FROM Employee E
LEFT JOIN Bonus B
ON E.empId = B.empId
WHERE COALESCE(bonus,0) < 1000

27. Find Customers With Positive Revenue this Year

SELECT customer_id
FROM Customers
WHERE year = 2021 and revenue > 0
//primary key = customer_id, year니

28. Triangle Judgement

//가장 긴변의 길이가 나머지 두변의 길이 합보다 작아야함
SELECT x, y, z, 
IF(x+y > z and y+z > x and z+x > y, "yes", "no")
as triangle
FROM triangle

29. Immediate Food Delivery

: 날짜 비교 그냥 = 해도 되고

: if(t/f,1,0) 보다 sum(t/f)

SELECT round(sum(order_date=customer_pref_delivery_date)/count(*), 2)
FROM Delivery

30. Students With Invalid Departments

SELECT id, name
FROM Students
WHERE department_id NOT IN (SELECT id FROM Department)

SELECT id, name
FROM Students
LEFT JOIN Department
ON Students.department_id = Department.id
WHERE Department.id IS NULL

31. Percentage of Users Attended a Contest

SELECT contest_id, round(COUNT(user_id)*100 / (SELECT COUNT(*) FROM Users),2) as percentage
FROM Reigster
GROUP BY Register.contest_id
ORDER BY percentage desc, contest_id asc

32. Customer Placing the Largest Number of Orders

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(order_number) desc
LIMIT 1