헤맨 만큼 내 땅이다 🏃

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/301651-- 각 세대별 대장균의 세대를 구하기 위한 CTE 정의WITH RECURSIVE ECOLI_GENERATIONS AS ( -- 1. 최초 세대(1세대)에 해당하는 대장균을 추출하여 세대 1로 설정 SELECT ID, 1 AS GENERATION FROM ECOLI_DATA WHERE PARENT_ID IS NULL -- 부모가 없는 개체가 1세대 대장균 UNION ALL -- 2. 재귀적으로 각 대장균의 자손을 찾아 다음 세대로 설정 SELECT E.ID, -- 자식..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131534-- 2021년에 가입한 회원을 필터링하여 JOINED2021 CTE에 저장WITH JOINED2021 AS ( SELECT * FROM USER_INFO WHERE YEAR(JOINED) = "2021")-- JOINED2021의 회원 중 상품을 구매한 회원 수와 비율을 연월별로 계산SELECT YEAR(B.SALES_DATE) YEAR, -- 구매 날짜의 연도 추출 MONTH(B.SALES_DATE) MONTH, -- 구매 날짜의 월 추출 COUNT(DISTINCT B.USER_ID) PU..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/276036WITH FrontEnd AS ( SELECT SUM(CODE) CODE FROM SKILLCODES WHERE CATEGORY = 'Front End'),C AS ( SELECT CODE FROM SKILLCODES WHERE NAME = 'C#'),Python AS ( SELECT CODE FROM SKILLCODES WHERE NAME = 'Python')SELECT CASE WHEN D.SKILL_CODE & F.CODE >= 1 AND D.SKILL_CODE & P.CODE >= 1 THEN 'A' ..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/157339SELECT C.CAR_ID, C.CAR_TYPE, CAST(C.DAILY_FEE * 30 * (1 - 0.01 * D.DISCOUNT_RATE) AS UNSIGNED) AS FEE FROM CAR_RENTAL_COMPANY_CAR C LEFT JOIN ( SELECT CAR_ID, MIN(START_DATE) AS START_DATE, MAX(END_DATE) AS END_DATE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID) H ON C.CAR_ID = H.CAR_ID INNER JOI..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/276035SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAMEFROM DEVELOPERS DJOIN SKILLCODES S -- 개발자, 스킬코드 테이블 조인 -> SKILL_CODE 컬럼과 CODE 컬럼 간의 비트 연산 수행ON (D.SKILL_CODE & S.CODE) != 0 -- DEVELOPERS의 SKILL_CODE에 해당하는 비트 위치에 Front End 스킬의 CODE가 포함되어 있는지 확인-- 비트 연산 결과가 0이 아니면 해당 스킬이 포함된 것임 !WHERE S.CATEGORY = 'Front End' -- CATEGORY가 "Front E..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/151141SELECT HISTORY_ID, ROUND(DAILY_FEE * CASE WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 90 THEN (SELECT (1 - DISCOUNT_RATE * 0.01) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상') WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 30 TH..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/301650WITH RECURSIVE ECOLI_GENERATIONS AS ( -- 1. 1세대(기본 세대) 대장균을 초기 설정 SELECT ID, PARENT_ID, 1 AS GENERATION FROM ECOLI_DATA WHERE PARENT_ID IS NULL UNION ALL -- 2. 재귀적으로 각 세대 자손을 추가 SELECT E.ID, E.PARENT_ID, EG.GENERATION + 1 AS GENERATION FROM ECOLI_DATA E JOIN ECOLI_GENERATIONS EG ON E.PARENT_ID..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/59413-- 0~23시의 모든 시간을 포함하는 임시 테이블 생성WITH HOURS AS ( SELECT 0 AS HOUR UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131537SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNTFROM ( SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT FROM ONLINE_SALE WHERE SALES_DATE LIKE '2022-03%' UNION ALL SELECT SALES_DATE, PRODUCT_ID,..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131124SELECT A.MEMBER_NAME, B.REVIEW_TEXT, DATE_FORMAT(B.REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATEFROM MEMBER_PROFILE AJOIN REST_REVIEW BON A.MEMBER_ID = B.MEMBER_IDWHERE A.MEMBER_ID = ( SELECT B.MEMBER_ID FROM REST_REVIEW B GROUP BY B.MEMBER_ID ORDER BY COUNT(B.REVIEW_ID) DESC LIMIT 1)ORDER BY B.REVIEW_DAT..