case문5 [leetcode / MySQL] 1158. Market Analysis I 문제 💡 Write a solution to find for each user, the join date and the number of orders they made as a buyer in 2019. Return the result table in any order. 테이블 형태 풀이 SELECT user_id AS buyer_id, join_date, SUM(CASE WHEN YEAR(order_date)=2019 THEN 1 ELSE 0 END) AS orders_in_2019 FROM Users AS u LEFT JOIN Orders AS o ON u.user_id=o.buyer_id GROUP BY user_id 해설 각 유저별 가입 일자와 2019년에 buyer로서 주문한 order 데이터의.. 2024. 2. 27. [leetcode / MySQL] 626. Exchange Seats 문제 💡 Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped. Return the result table ordered by id in ascending order. 테이블 형태 풀이 SELECT id , CASE WHEN id=(SELECT COUNT(*) FROM Seat) AND id%2=1 THEN student WHEN id%2=1 THEN LEAD(student, 1) OVER (ORDER BY id) WHEN id%2=0 THEN LAG(student, 1) OVER(ORDER BY.. 2024. 2. 22. [프로그래머스 / MySQL] 자동차 대여 기록에서 장기/단기 대여 구분하기 문제 💡 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요. 테이블 형태 풀이 SELECT HISTORY_ID , CAR_ID , DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE , DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE , CASE WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 30 TH.. 2024. 2. 1. [leetcode / MySQL] 262. Trips and Users 문제 💡 The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day. Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decima.. 2024. 1. 31. [leetcode / MySQL] 1179. Reformat Department Table 문제 💡 Reformat the table such that there is a department id column and a revenue column for each month. Return the result table in any order. 테이블 형태 풀이 SELECT id , SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue , SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue , SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue , SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_R.. 2024. 1. 29. 이전 1 다음
