[Sql] 문제 풀이

재구매가 일어난 상품과 회원 리스트 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131536

프로그래머스

SELECT USER_ID, PRODUCT_ID 
FROM ONLINE_SALE 
GROUP BY USER_ID, PRODUCT_ID 
HAVING COUNT(*)>=2 
ORDER BY USER_ID, PRODUCT_ID DESC

오프라인/온라인 판매 데이터 통합하기

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

select date_format(sales_date, '%Y-%m-%d') as sales_date, product_id, user_id, sales_amount
from online_sale
where sales_date >= '2022-03-01' and sales_date < '2022-04-01'

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 sales_date >= '2022-03-01' and sales_date < '2022-04-01'

order by sales_date, product_id, user_id

가격이 제일 비싼 식품의 정보 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131115

프로그래머스

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE 
FROM FOOD_PRODUCT
ORDER BY PRICE DESC LIMIT 1

식품분류별 가장 비싼 식품의 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

SELECT CATEGORY, PRICE AS 'MAX_PRICE', PRODUCT_NAME 
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE)
                FROM FOOD_PRODUCT
                GROUP BY CATEGORY)
        AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

https://school.programmers.co.kr/learn/courses/30/lessons/157340

프로그래머스

SELECT CAR_ID,
(CASE WHEN CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') AND DATE_FORMAT(END_DATE, '%Y-%m-%d'))
THEN '대여중'
ELSE '대여 가능'
END) AS 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

동명 동물 수 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59041

프로그래머스

SELECT NAME,COUNT(NAME) 
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL 
GROUP BY NAME HAVING COUNT(NAME)>=2
ORDER BY NAME

입양 시각 구하기(1)

https://school.programmers.co.kr/learn/courses/30/lessons/59412

프로그래머스

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) 
FROM ANIMAL_OUTS 
GROUP BY HOUR HAVING HOUR BETWEEN 9 AND 19 
ORDER BY HOUR(DATETIME)

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

프로그래머스

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE>='2022-08-01' AND
    START_DATE<'2022-11-01' 
    GROUP BY CAR_ID HAVING COUNT(*)>=5
    ) AND START_DATE>='2022-08-01' AND
    START_DATE<'2022-11-01'
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC

그룹별 조건에 맞는 식당 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS A
JOIN REST_REVIEW AS B ON A.MEMBER_ID=B.MEMBER_ID
WHERE B.MEMBER_ID = (SELECT MEMBER_ID
                      FROM REST_REVIEW
                      GROUP BY MEMBER_ID ORDER BY COUNT(*) DESC LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.CAR_ID=H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE=P.CAR_TYPE
WHERE C.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01'
) AND P.DURATION_TYPE='30일 이상'
GROUP BY C.CAR_ID
HAVING C.CAR_TYPE IN ('세단', 'SUV') AND (FEE>=500000 AND FEE<2000000) 
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

5월 식품들의 총매출 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131117

프로그래머스

SELECT A.PRODUCT_ID, PRODUCT_NAME, SUM(PRICE*AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS A 
JOIN FOOD_ORDER AS B ON A.PRODUCT_ID=B.PRODUCT_ID
WHERE DATE_FORMAT(PRODUCE_DATE,'%Y-%m')='2022-05'
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID

없어진 기록 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59042

프로그래머스

SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_OUTS A 
LEFT OUTER JOIN ANIMAL_INS B ON A.ANIMAL_ID=B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID

오랜 기간 보호한 동물(1)

https://school.programmers.co.kr/learn/courses/30/lessons/59044

프로그래머스

SELECT INS.NAME, INS.DATETIME 
FROM ANIMAL_INS AS INS LEFT OUTER JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME LIMIT 3

상품을 구매한 회원 비율 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, 
COUNT(DISTINCT A.USER_ID) AS PUCHASED_USERS,
ROUND(COUNT(DISTINCT A.USER_ID)/ (SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE YEAR(JOINED)=2021),1) AS PUCHASED_RATIO
FROM USER_INFO AS A
JOIN ONLINE_SALE AS B ON A.USER_ID=B.USER_ID
WHERE YEAR(JOINED)=2021
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)

조건별로 분류하여 주문상태 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131113

프로그래머스

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,"%Y-%m-%d") AS OUT_DATE, 
IF(OUT_DATE <='2022-05-01', '출고완료', 
            IF(OUT_DATE IS NULL,'출고미정','출고대기'))
AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID

자동차 대여 기록 별 대여 금액 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

SELECT H.HISTORY_ID, ROUND((DATEDIFF(END_DATE, START_DATE)+1)*C.DAILY_FEE*(100-IFNULL(P.DISCOUNT_RATE, 0))/100) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
JOIN CAR_RENTAL_COMPANY_CAR AS C ON C.CAR_ID=H.CAR_ID
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
ON C.CAR_TYPE=P.CAR_TYPE AND P.DURATION_TYPE=(
    CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 >= 90 THEN '90일 이상'
    WHEN DATEDIFF(END_DATE,START_DATE)+1 >= 30 THEN '30일 이상'
    WHEN DATEDIFF(END_DATE,START_DATE)+1 >= 7 THEN '7일 이상'
    END)
WHERE C.CAR_TYPE = '트럭'
GROUP BY H.HISTORY_ID
ORDER BY FEE DESC, H.HISTORY_ID DESC

자동차 평균 대여 기간 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157342

프로그래머스

SELECT CAR_ID, 
ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID HAVING AVERAGE_DURATION>=7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

중성화 여부 파악하기

https://school.programmers.co.kr/learn/courses/30/lessons/59409

프로그래머스

SELECT ANIMAL_ID,NAME,
    CASE WHEN (SEX_UPON_INTAKE LIKE "%Neutered%" OR SEX_UPON_INTAKE LIKE "%Spayed%")
    THEN "O"
    ELSE "X"
    END AS "중성화"
FROM  ANIMAL_INS
ORDER BY ANIMAL_ID