[Leetcode]Students & Examinations, Percentage of Users Attended a contest, Product Sales Analysis 3

1. Students and Examinations - https://leetcode.com/problems/students-and-examinations/

Students and Examinations - LeetCode

Q. 

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example. (Input 중략)

일단 포인트는 all students & all subjects를 포함해야 한다는 것이다. 때문에 cross join을 써야 한다는 것이 중요한 것.

크로스 조인은 그림으로 표현했을 때 아래처럼, 가능한 테이블 조인 시 각 원소 내 가능한 모든 조합을 표현한다는 특징이 있다. 

출처 https://www.javatpoint.com/mysql-cross-join

즉, 하나의 입력 테이블의 각 행은 다른 테이블의 각 행과 매칭된다고 할 수 있다. 각 테이블의 사이즈가 m개 행 * n개 행이라면 결과는 m*n 사이즈의 행이 만들어지는 것. 동시에 모든 테이블의 모든 행을 합친 테이블이 만들어진다. 

여기서는 Students와 subjects의 모든 행을 합쳐야 하므로 From students 에다 cross join subjects 으로 코드를 짰다.

SELECT Students.student_id, Students.student_name, Subjects.subject_name, IFNULL(cnt, 0) as attended_exams
FROM Students
CROSS JOIN Subjects
LEFT JOIN (
    SELECT student_id, subject_name,
    COUNT(*) as cnt FROM Examinations GROUP BY student_id, subject_name
) as Temp 
ON Subjects.subject_name = Temp.subject_name AND Students.student_id = Temp.student_id
ORDER BY student_id, subject_name

사실 cross join만 알았다면, left join 같은 경우는 종속(?) 쿼리만 잘 짰으면 되는 부분이라 괜찮았을 듯.

몰라서 고생했다 ,,,

각 run, submit 결과 :)

---

2. Percentage of Users Attended a contest - https://leetcode.com/problems/percentage-of-users-attended-a-contest/

Percentage of Users Attended a Contest - LeetCode

Q.

Write a solution to find the percentage of the users registered in each contest rounded to two decimals.

Return the result table ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order. The result format is in the following example.

 

round 함수 * 100 (100 곱해서)  percentage(백분율) 표현해주고, from을 각 테이블(Users , Register)에 대해 사용했다.

select a.contest_id,
round((total /count(user_id))*100 , 2) as percentage
from Users u join(
select contest_id,
count(distinct user_id) as total

from Register
group by contest_id) as a
group by a.contest_id
order by round((total /count(user_id))*100 , 2) desc , a.contest_id asc

역시나 Run / Submit 결과~

---

3. Product Sales Analysis 3

https://leetcode.com/problems/product-sales-analysis-iii/

LeetCode - The World's Leading Online Programming Learning Platform

Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order. The result format is in the following example.

 

사실 종속쿼리문(쿼리문 속의 쿼리문)의 갈피만 잘 잡으면 되는 애였다. 다만 ON 조건을 잘 붙여 써야 했다는 게 함정 ,,,, ON조건 못 써서 30분 넘게 헤맨 바보가 있다면 믿으시겠습니까

SELECT Sales.product_id, first_year, quantity, price
FROM Sales
JOIN (
    SELECT product_id, MIN(year) as first_year
    FROM Sales
    GROUP BY product_id
) as Temp ON Sales.year = Temp.first_year 
AND Sales.product_id = Temp.product_id

어쨌거나 결과는 성공 :)