코드카타 / SQL

설득이 2024. 4. 1. 20:20

코드카타

자동차 대여 기록에서 장기/단기 대여 구분하기
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

대여 시작일 : DATE_FORMAT
대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시 : CASE WHEN
대여 시작일이 2022년 9월 : WHERE
대여 기록 ID를 기준으로 내림차순 정렬 : ORDER BY

SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, 
CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여'
ELSE '단기 대여' END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

자동차 평균 대여 기간 구하기
평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력
평균 대여 기간은 소수점 두번째 자리에서 반올림하고,
결과는 평균 대여 기간을 기준으로 내림차순 정렬, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬

소수점 두번째 자리에서 반올림 : ROUND
평균 대여 기간 : AVG
평균 대여 기간 : DATEDIFF
평균 대여 기간이 7일 이상인 자동차들의 자동차 ID : GROUP BY
평균 대여 기간이 7일 이상인 자동차들의 자동차 ID : HAVING
내림차순 정렬 : ORDER BY

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;

헤비 유저가 소유한 장소
공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회

공간을 둘 이상 등록한 사람 : GROUP BY
공간을 둘 이상 등록한 사람 : HAVING COUNT
공간의 정보를 아이디 조회 : 서브쿼리 사

SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID in (SELECT HOST_ID 
            FROM PLACES
            GROUP BY 1
            HAVING COUNT(HOST_ID)>=2)
ORDER BY 1;

SQL 수준별학습

UNION 함수
- 여러 개의 SELECT문의 결과를 하나의 테이블로 연결하여 보고 싶을 때 사용
- 두 개의 테이블이 수직 결합된 형태
- UNION VS UNION ALL
  - 두 쿼리문을 하나로 수직결합
  - 열의 갯수와 순서가 모든 쿼리에서 동일
  - 데이터의 형식이 일치
  - UNION은 중복된 행을 하나로 표기
  - UNION ALL은 모두 표현

JOIN
- 데이터를 추출하기 위해 두 개의 테이블을 결헙하는 역할
- 수평 결합
- 공통컬럼을 반드시 1개이상 가져야함
- 공통컬럼 찾기 -> 관계 찾기 -> 적절한 조인 방식 찾기
  - PK : 기본키, NULL일 수가 없고 유일한 값을 가짐
  - FK : 외래키, 다른 테이블의 PK와 연결되어 테이블 간 관계를 나타내주는 컬럼
  - INNER JOIN : 두 테이블에서 일치하는 값을 가진 행 출력
  - LEFT JOIN : 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환
  - RIGHT : 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행을 반환
  - FULL OUTER JOIN : 모든 데이터가 보고 싶을 때 사용
숙제

오랜 기간 보호한 동물(1)
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문
이때 결과는 보호 시작일 순으로 조회해야 합니다.

아직 입양을 못 간 동물 중, : INS 테이블에 있음
동물 3마리 : LIMIT
보호 시작일 순으로 조회 : ORDER BY

SELECT I.NAME, I.DATETIME FROM ANIMAL_INS AS I
LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3;

조건에 맞는 사용자와 총 거래금액 조회하기
완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문
결과는 총거래금액을 기준으로 오름차순 정렬해주세요

완료된 중고 거래 : 조인
완료된 중고 거래 :WHERE
총금액이 70만 원 이상인 사람의 회원 ID : SUM
총금액이 70만 원 이상인 사람의 회원 ID : GROUP BY
총금액이 70만 원 이상인 사람의 회원 ID : HAVING
오름차순 정렬 : ORDER BY

SELECT U.USER_ID, U.NICKNAME, 
SUM(B.PRICE) AS `TOTAL_SALES`
FROM USED_GOODS_USER AS U
INNER JOIN USED_GOODS_BOARD AS B ON U.USER_ID = B.WRITER_ID
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC;

보호소에서 중성화한 동물
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만,
보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문

보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디 : JOIN
중성화 수술을 거친 동물 : WHERE
아이디 순으로 조회 : ORDER BY

SELECT i.ANIMAL_ID,i.ANIMAL_TYPE,i.NAME
FROM ANIMAL_INS i
JOIN ANIMAL_OUTS o
ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE i.SEX_UPON_INTAKE LIKE "Intact%" and o.SEX_UPON_OUTCOME NOT LIKE "Intact%"
ORDER BY i.ANIMAL_ID

case when 구문과 join 함수를 사용하여, users 테이블을 기준으로, 결제를 한 유저와 결제를 하지 않은 유저를 추출

users 테이블을 기준 : join
case when 구문 : null 값이 없으면 결제함, 아니라면 결제 안함
결제를 한 유저와 결제를 하지 않은 유저 : count(distinct)

SELECT
    CASE WHEN p.game_account_id  IS NOT NULL THEN '결제함'
         ELSE '결제안함'
    END AS gb,
    count(distinct u.game_account_id) as usercnt
FROM
    users u
LEFT JOIN
    payment p ON u.game_account_id  = p.game_account_id 
group by gb;

서버번호가 2 이상인 데이터와 결제방식이 CARD 인 경우를 join해 주시고, game_account_id 를 기준으로 actorcnt, sumamount를 추출

서버번호가 2 이상인 데이터 : 서브쿼리
결제방식이 CARD 인 경우 : 서브쿼리
join해 주시고 : game_account_id로
game_account_id 를 기준 : group by
actorcnt 라는 컬럼에 game_actor_id 갯수를 중복값없이 세어 : count(distinct)
sumamount라는 컬럼에 pay_amount 값을 더해 : sum(distinct)
having 을 사용하지 않고, subquery 사용으로 game_actor_id 갯수가 2 이상인 경우만 추출 : count(distinct) >=2

SELECT 
    t1.game_account_id,
    COUNT(DISTINCT t1.game_actor_id) AS actorcnt,
    sum(distinct t2.pay_amount) AS sumamount
FROM 
    (SELECT * FROM basic.users u  WHERE u.serverno >= 2) AS t1
JOIN 
    (SELECT * FROM basic.payment p WHERE p.pay_type = 'CARD') AS t2
ON 
    t1.game_account_id = t2.game_account_id
GROUP BY 
    t1.game_account_id
HAVING 
    COUNT(DISTINCT t1.game_actor_id) >= 2;

user 테이블에서 game_account_id, date, serverno 를 추출한 데이터와 매출 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 join 을 진행

그 다음, datediff 함수를 사용해 결제일자-접속일자를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요.
두 날짜의 형식은 같아야 합니다.

마지막으로, 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주세요.
다만, 평균 datediff 컬럼은 정수 형태로 출력되어야 합니다.
또한, 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬

SELECT 
    serverno,
    round(AVG(diffdate)) AS avg_datediff
FROM
    (
        SELECT 
            u.game_account_id,
            u.date AS login_date,
            u.serverno,
            DATEDIFF(MAX(p.date), u.date) AS diffdate
        FROM 
            basic.users u
        LEFT JOIN 
            (
                SELECT 
                    game_account_id,
                    MAX(approved_at) AS date
                FROM 
                    basic.payment
                GROUP BY 
                    game_account_id
            ) AS p
        ON 
            u.game_account_id = p.game_account_id
        GROUP BY 
            u.game_account_id, u.date, u.serverno
    ) AS subquery
WHERE 
    diffdate >= 10
GROUP BY 
    serverno
ORDER BY 
    serverno DESC;