✅ 코드 SELECT product_id , product_name FROM Product WHERE product_id NOT IN ( SELECT product_id FROM Sales WHERE sale_date NOT BETWEEN '2019-01-01 00:00:00' AND '2019-03-31 23:59:59' ) AND product_id IN (SELECT DISTINCT product_id FROM Sales) 첫 번째 풀이입니다. 다중행 서브쿼리를 사용한 풀이입니다. WHERE문에 조건을 2개 설정했는데 다소 복잡해보이는 감이 있습니다. SELECT P.product_id AS product_id , P.product_name as product_name FROM Product AS ..
✅ 코드 -- FROM절 서브쿼리 사용하면 항상 alias 지정해줘야 함 WITH ACC AS ( SELECT requester_id AS id , COUNT(DISTINCT accepter_id) AS num FROM RequestAccepted GROUP BY requester_id ), REQ AS ( SELECT accepter_id AS id , COUNT(DISTINCT requester_id) AS num FROM RequestAccepted GROUP BY accepter_id ), T1 AS ( SELECT * FROM ACC UNION ALL SELECT * FROM REQ ), T2 AS ( SELECT id , SUM(num) AS num FROM T1 GROUP BY id ) SE..
✅ 코드 SELECT E1.name FROM Employee AS E1 LEFT JOIN Employee AS E2 ON E1.id = E2.managerId GROUP BY E1.id HAVING COUNT(DISTINCT E2.id) >= 5 그룹화를 수행한 기준 컬럼을 SELECT문에 입력하지 않아도 된다는 점을 알았습니다. 이외에는 LEFT JOIN을 수행하면 쉽게 풀이가 가능했습니다.
✅ 코드 WITH T AS ( SELECT player_id , MIN(event_date) AS first_log FROM Activity GROUP BY player_id ) SELECT ROUND(COUNT(DISTINCT T.player_id) / COUNT(DISTINCT A.player_id), 2) AS fraction FROM Activity AS A LEFT JOIN T ON A.player_id = T.player_id AND A.event_date = DATE_ADD(T.first_log, INTERVAL 1 DAY) WITH문으로 유저별 첫 로그인 일자를 모아둔 테이블을 생성했습니다. 이후 Activity 테이블과 특정 조건을 만족하는 LEFT JOIN을 수행했습니다.
✅ 코드 SELECT request_at AS Day , ROUND(COUNT(DISTINCT CASE WHEN status LIKE '%cancelled%' THEN id END) / COUNT(DISTINCT id), 2) AS 'Cancellation Rate' FROM Trips WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03' AND client_id IN (SELECT users_id FROM Users WHERE banned = 'No') AND driver_id IN (SELECT users_id FROM Users WHERE banned = 'No') GROUP BY Day 다중행 서브쿼리를 사용하여 WHERE문의 조건을 설정했습니다.
✅ 코드 WITH RENTAL_HISTORY AS ( SELECT * , CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 7 THEN NULL WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 30 THEN '7일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 90 THEN '30일 이상' ELSE '90일 이상' END AS DURATION_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY ), TRUCKS AS ( SELECT * FROM CAR_RENTAL_COMPANY_CAR WHERE CAR_TYPE = '트럭' ) SELECT H.HISTORY_ID AS HISTORY_..
✅ 코드 WITH T AS ( SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE WHERE SALES_DATE BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59' UNION ALL SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , NULL , SALES_AMOUNT FROM OFFLINE_SALE WHERE SALES_DATE BETWEEN '2022-03-01 00:00:00' AND '2022-03-31 23:59:59' ) S..
✅ 코드 WITH T AS ( SELECT * FROM FIRST_HALF UNION ALL SELECT * FROM JULY ) SELECT FLAVOR FROM T GROUP BY FLAVOR ORDER BY SUM(TOTAL_ORDER) DESC LIMIT 3 FIRST_HALF와 JULY 테이블의 컬럼 형식이 동일해서 UNION ALL을 사용했습니다.
