[TIL]_2025.04.03 본캠프 46일차 (2) 코드카타 SQL

92번. [Average Selling Price]
(https://leetcode.com/problems/average-selling-price/description/)

SELECT 
    p.product_id, 
    COALESCE(ROUND(SUM(p.price * COALESCE(u.units, 0)) / NULLIF(SUM(COALESCE(u.units, 0)), 0), 2), 0) AS average_price
FROM prices p
LEFT JOIN unitssold u
    ON p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

93번. [Project Employees I]
(https://leetcode.com/problems/project-employees-i/description/)

select p.project_id, round(sum(e.experience_years) / count(p.project_id), 2) as average_years 
from project p
join employee e
on p.employee_id = e.employee_id
group by 1

94번. [Percentage of Users Attended a Contest]
(https://leetcode.com/problems/percentage-of-users-attended-a-contest/description/)

select r.contest_id, round(count(u.user_id) / cnt * 100, 2) as percentage
from register r
join users u
on r.user_id = u.user_id
join (select count(*) as cnt from users) u2
group by r.contest_id
order by 2 desc, 1 asc