본문 바로가기

전체 글210

[SQL] 프로그래머스 [Level-4] 오프라인/온라인 판매 데이터 통합하기 SELECT A.SALES_DATE , A.PRODUCT_ID , A.USER_ID , A.SALES_AMOUNT FROM ( SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03' UNION ALL SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , NULL AS USER_ID , SALES_AMOUNT FROM OFFLINE_SALE WHERE DATE_FORMAT(SALES_DATE, '%Y.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 취소되지 않은 진료 예약 조회하기 SELECT A.APNT_NO , B.PT_NAME , B.PT_NO , A.MCDP_CD , C.DR_NAME , A.APNT_YMD FROM APPOINTMENT A , PATIENT B , DOCTOR C WHERE A.PT_NO = B.PT_NO AND A.MDDR_ID = C.DR_ID AND DATE_FORMAT(A.APNT_YMD, '%Y-%m-%d') = '2022-04-13' AND A.APNT_CNCL_YN = 'N' AND A.MCDP_CD = 'CS' ORDER BY A.APNT_YMD SELECT A.APNT_NO , B.PT_NAME , B.PT_NO , A.MCDP_CD , C.DR_NAME , A.APNT_YMD FROM APPOINTMENT A , PATIENT B , DO.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 주문량이 많은 아이스크림들 조회하기 SELECT A.FLAVOR FROM FIRST_HALF A INNER JOIN ( SELECT FLAVOR , SUM(TOTAL_ORDER) AS JULY_ORDER FROM JULY GROUP BY FLAVOR ) B ON A.FLAVOR = B.FLAVOR ORDER BY (A.TOTAL_ORDER + B.JULY_ORDER) DESC LIMIT 3 SELECT FLAVOR FROM ( SELECT A.FLAVOR FROM FIRST_HALF A INNER JOIN ( SELECT FLAVOR , SUM(TOTAL_ORDER) AS JULY_ORDER FROM JULY GROUP BY FLAVOR ) B ON A.FLAVOR = B.FLAVOR ORDER BY (A.TOTAL_ORDER + B.JU.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-5] 상품을 구매한 회원 비율 구하기 SELECT DATE_FORMAT(A.SALES_DATE, '%Y') AS YEAR , DATE_FORMAT(A.SALES_DATE, '%c') AS MONTH , COUNT(DISTINCT A.USER_ID) AS PUCHASED_USERS , ROUND(COUNT(DISTINCT A.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE DATE_FORMAT(JOINED, '%Y') = 2021), 1) AS PUCHASED_RATIO FROM ONLINE_SALE A INNER JOIN USER_INFO B ON A.USER_ID = B.USER_ID AND DATE_FORMAT(B.JOINED, '%Y') = 2021 GROUP BY YEAR, MONTH ORDER.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-3] 즐겨찾기가 가장 많은 식당 정보 출력하기 SELECT A.FOOD_TYPE , A.REST_ID , A.REST_NAME , A.FAVORITES FROM REST_INFO A INNER JOIN ( SELECT FOOD_TYPE , MAX(FAVORITES) AS FAVORITES FROM REST_INFO GROUP BY FOOD_TYPE ) B ON A.FOOD_TYPE = B.FOOD_TYPE AND A.FAVORITES = B.FAVORITES ORDER BY A.FOOD_TYPE DESC 프로그래머스 즐겨찾기가 가장 많은 식당 정보 출력하기 SQL 2022. 12. 2.
[SQL] 프로그래머스 [Level-3] 헤비 유저가 소유한 저장소 SELECT ID , NAME , HOST_ID FROM PLACES WHERE HOST_ID IN ( SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(HOST_ID) > 1 ) ORDER BY ID 집계함수 COUNT를 조건으로 사용하므로 서브쿼리에서 WHERE절이 아닌 HAVING절에 COUNT(HOST_ID) > 1 프로그래머스 헤비 유저가 소유한 저장소 SQL 2022. 12. 2.

티스토리툴바