[SQL] 코드카타 6일차

어제 몇개라도 하려고 했는데 데일리루틴 페이지가 접속이 안 되어서 포기...

---

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다. (링크)

아래는 초안이었는데, 사실 작성하면서도 "칼럼명 = '값'이라는 식이 있던가..긴가민가 했었다
당연히 에러가 발생했고, 쉽게 가려던 편법(?)은 실패

SELECT
    cart_id
FROM
    cart_products
WHERE
    (name = 'milk')
    and (name = 'yogurt')
ORDER BY
    cart_id
;

그래서 보다 근본적으로 생각했고
우유를 구매한 장바구니, 요거트를 구매한 장바구니, 둘 다 구매? = 교집합
아, inner join이구나!로 시작해서 코드를 짰다.

SELECT
    a.cart_id
FROM
    (SELECT
        *
    FROM
        cart_products
    WHERE
        (name = 'milk')
    ) a
    INNER JOIN
    (
    SELECT
        *
    FROM
        cart_products
    WHERE
        (name = 'yogurt')
    ) b
        ON a.cart_id = b.cart_id
GROUP BY
    a.cart_id
ORDER BY
    a.cart_id
;

GROUP BY를 빼도 정답으로 인정해주긴 하는데,
그렇게하면 동일한 cart_id가 중복으로 나오니까 취지에 덜 맞는다고 생각해서 넣기로 했다

---

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다. (링크)

파일 경로를 서술하는 내용이 조금 복잡하긴 했지만 문제 자체는 간단했던 문제

SELECT
    concat('/home/grep/src/',b.board_id,'/',f.file_id,f.file_name,f.file_ext) as 'file_path'
FROM
    USED_GOODS_BOARD b
    INNER JOIN USED_GOODS_FILE f
        ON b.board_id = f.board_id
WHERE
    b.views = (SELECT
                max(views)
             FROM
                USED_GOODS_BOARD)
ORDER BY
    f.file_id desc
;

---

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요. (링크)

처음에는 대충 합산 구하고 rownum으로 3개만 뽑아내면 되겠다 싶었는데 풀리지 않았다
아마 동일한 출하번호(shipment_id)끼리는 합산이 되었으나, 같은 맛 다른 출하번호의 합산이 되지 않았던 것으로 보인다 (해당 문제에서는 strawberry가 그렇다)

SELECT
    flavor
FROM
    (
    SELECT
        j.flavor
        , row_number() over (order by ifnull(f.total_order,0) + ifnull(j.total_order,0) desc) as 'rownum'
    FROM
        first_half f
        RIGHT OUTER JOIN july j
            ON f.shipment_id = j.shipment_id
    ) aa
WHERE
    rownum <= 3
;

그래서 일단은 어쩔 수 없이 서브쿼리를 많이 남발해보는 것으로 진행했다

SELECT
    flavor
FROM
    (
    SELECT
        flavor
        , sum(full_tot) as 'full_tot'
    FROM
        (
        SELECT
            j.flavor
            , ifnull(f.total_order,0) + ifnull(j.total_order,0) as 'full_tot'
        FROM
            first_half f
            RIGHT OUTER JOIN july j
                ON f.shipment_id = j.shipment_id
        ) aa
    GROUP BY
        flavor
    ORDER BY
        2 desc
    ) bb
LIMIT 3
;