본문 바로가기

SQL56

[SQL] 프로그래머스 [Level-4] 년, 월, 성별 별 상품 구매 회원 수 구하기 SELECT MST.YEAR , MST.MONTH , MST.GENDER , COUNT(MST.GENDER) AS USERS FROM ( SELECT YEAR(A.SALES_DATE) AS YEAR , MONTH(A.SALES_DATE) AS MONTH , B.GENDER FROM ONLINE_SALE A , USER_INFO B WHERE A.USER_ID = B.USER_ID AND B.GENDER IN (0, 1) GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE), B.USER_ID ) MST GROUP BY MST.YEAR, MST.MONTH, MST.GENDER ORDER BY MST.YEAR, MST.MONTH, MST.GENDER SELECT MST.YE.. 2022. 12. 12.
[SQL] 프로그래머스 [Level-4] 5월 식품들의 총매출 조회하기 SELECT A.PRODUCT_ID , B.PRODUCT_NAME , SUM(A.AMOUNT) * B.PRICE AS TOTAL_SALES FROM FOOD_ORDER A , FOOD_PRODUCT B WHERE A.PRODUCT_ID = B.PRODUCT_ID AND DATE_FORMAT(A.PRODUCE_DATE, '%Y-%m') = '2022-05' GROUP BY A.PRODUCT_ID ORDER BY SUM(A.AMOUNT) * B.PRICE DESC, A.PRODUCT_ID SELECT A.PRODUCT_ID , A.PRODUCT_NAME , A.PRICE * B.AMOUNT_SUM AS TOTAL_SALES FROM FOOD_PRODUCT A , ( SELECT PRODUCT_ID, SUM(.. 2022. 12. 12.
[SQL] 해커랭크 Binary Tree Nodes SELECT G.NODE FROM ( SELECT CONCAT(N, ' Root') AS NODE , N FROM BST WHERE P IS NULL UNION ALL SELECT CONCAT(N, ' Inner') AS NODE , N FROM BST WHERE N IN ( SELECT P FROM BST ) AND P IS NOT NULL UNION ALL SELECT CONCAT(N, ' Leaf') AS NODE , N FROM BST WHERE N NOT IN ( SELECT DISTINCT P FROM BST WHERE P IS NOT NULL ) ) G ORDER BY G.N 해커랭크 Binary Tree Nodes SQL 2022. 12. 3.
[SQL] 해커랭크 Ollivander's Inventory SELECT C.ID, B.AGE, A.MIN_COINS_NEEDED, A.POWER FROM (SELECT CODE, MIN(COINS_NEEDED) AS MIN_COINS_NEEDED, POWER FROM WANDS GROUP BY CODE, POWER) A , WANDS_PROPERTY B , WANDS C WHERE A.CODE = B.CODE AND A.CODE = C.CODE AND A.MIN_COINS_NEEDED = C.COINS_NEEDED AND A.POWER = C.POWER AND B.IS_EVIL = 0 ORDER BY A.POWER DESC, B.AGE DESC 해커랭크 Ollivander's Inventory SQL 2022. 12. 3.
[SQL] 해커랭크 15 Days of Learning SQL STEP 1. SUBMISSION_DATE와 ID, NM의 출력은 간단하다. SELECT MST.SUBMISSION_DATE , (SELECT HACKER_ID FROM SUBMISSIONS WHERE SUBMISSION_DATE = MST.SUBMISSION_DATE GROUP BY HACKER_ID ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS ID , (SELECT NAME FROM HACKERS WHERE HACKER_ID = ID) AS NM FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) MST GROUP BY MST.SUBMISSION_DATE STEP 2. 아래 쿼리를 이해하자 SE.. 2022. 12. 3.
[SQL] 해커랭크 SQL Project Planning SET SQL_MODE = ''; SELECT A.START_DATE, B.END_DATE FROM (SELECT START_DATE FROM PROJECTS WHERE START_DATE NOT IN (SELECT END_DATE FROM PROJECTS)) A, (SELECT END_DATE FROM PROJECTS WHERE END_DATE NOT IN (SELECT START_DATE FROM PROJECTS)) B WHERE A.START_DATE < B.END_DATE GROUP BY A.START_DATE ORDER BY DATEDIFF(B.END_DATE, A.START_DATE), A.START_DATE SQL_MODE 초기화 & 조회 SQL_MODE를 확인해보자 SHOW VARIABLE.. 2022. 12. 3.

티스토리툴바