SolveSQL :: 난이도 2 문제풀이

1. 두 테이블 결합하기

역대 올림픽 정보 데이터셋은 역대 올림픽 경기와 관련된 데이터가 들어있는 테이블로 이루어져 있습니다. athletes 테이블에는 역대 올림픽 참가 선수의 이름이 들어 있습니다. events 테이블에는 종목과 경기 이름이 들어 있습니다. games 테이블에는 올림픽 개최 연도, 개최 도시와 시즌 정보가 기록되어 있습니다. records 테이블에는 역대 올림픽 참가 선수들의 신체 정보와 획득한 메달 정보가 기록되어 있습니다. 이 테이블은 다른 테이블과 매핑할 수 있는 ID 정보도 가지고 있습니다. teams 테이블에는 국가 정보가 기록되어 있습니다.

 

위 테이블 중 events 테이블과 records 테이블을 활용해 올림픽 골프 종목에 참가한 선수의 ID를 모두 조회하는 쿼리를 작성해주세요.

SELECT DISTINCT R.athlete_id
FROM EVENTS E, RECORDS R
WHERE E.sport = 'Golf' AND E.id = R.event_id;

 

2. 레스토랑 웨이터의 팁 분석

tips 테이블에는 식사 주문 금액, 팁, 결제자 성별, 요일, 시간대 등 음식점의 방문 고객들과 관련된 데이터가 들어있습니다.

 

음식점의 요일, 시간대 별 패턴을 분석해보고자 합니다. 구체적으로는 각 요일의 시간대별 평균 팁은 얼마인지, 평균 일행 수는 몇 명인지 확인하고자 합니다. 평균 팁과 평균 일행 수는 소수점 아래 셋째 자리에서 반올림 해 둘째 자리까지 출력하고, 결과 데이터가 요일, 시간대의 알파벳 순으로 정렬되도록 쿼리를 작성해주세요. 결과 데이터에는 아래 4개의 컬럼이 들어가야 합니다.

• day - 요일
• time - 시간대
• avg_tip - 평균 팁
• avg_size - 평균 일행 수

SELECT DAY, TIME, ROUND(AVG(TIP), 2) AS avg_tip, ROUND(AVG(SIZE), 2) AS avg_size
FROM tips
GROUP BY DAY, TIME
ORDER BY DAY ASC, TIME ASC;

 

3. 일별 블로그 방문자 수 집계

ga 테이블에는 데이터리안 기술 블로그에 설치된 Google Analytics를 활용해 수집한 방문, 클릭 로그가 들어 있습니다.

 

2021년 8월 2일부터 2021년 8월 9일까지 날짜별로 몇 명의 방문자가 방문했는지 알고 싶습니다. 해당 일자 내 로그 테이블에 이벤트가 하나라도 기록 된 경우 방문자로 집계합니다. 기간 별 방문자 수를 일별로 추출하는 쿼리를 작성해주세요. 결과 데이터에는 아래 2개 컬럼이 들어가야하며, 방문일을 기준으로 오름차순 정렬되어있어야 합니다.

• dt - 방문일
• users - 방문자 수

SELECT event_date_kst AS dt, COUNT(DISTINCT user_pseudo_id) AS users
FROM GA
WHERE DATE(event_date_kst) BETWEEN '2021-08-02' AND '2021-08-09'
GROUP BY event_date_kst;

 

4. 우리 플랫폼에 정착한 판매자 2

olist_order_items_dataset 테이블에는 주문 안에 어떤 상품이 포함되어 있는지, 상품의 판매자는 누구인지 등 상품 단위의 데이터가 들어있습니다.

 

상품 가격이 50달러 이상인 주문이 100건 이상 들어온 판매자 리스트를 주문 건수가 많은 순서대로 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 아래 컬럼이 있어야 합니다.

• seller_id - 판매자 ID
• orders - 판매자가 판매한 주문 건수

SELECT seller_id, COUNT(DISTINCT order_id) AS orders
FROM olist_order_items_dataset
WHERE price >= 50
GROUP BY seller_id
HAVING orders >= 100
ORDER BY orders DESC;

 

5. 레스토랑의 일일 매출

Waiter's Tips 데이터셋에는 식사 금액, 팁, 결제자 성별, 결제 요일 등 레스토랑 손님들의 계산 정보가 들어있습니다. 이 데이터를 이용하여 레스토랑의 요일별 매출 총액을 계산하려고 합니다.

 

요일별로 매출 총액을 집계하는 쿼리를 작성해주세요. 1000달러 미만으로 판매한 날은 결과에서 제외하고, 매출이 많았던 요일부터 순서대로 출력해주세요. 결과 데이터는 아래 컬럼을 포함해야 합니다.

• day - 요일
• revenue_daily - 요일 별 매출 총액

SELECT day, SUM(total_bill) AS revenue_daily
FROM tips
GROUP BY day
HAVING revenue_daily >= 1000
order by revenue_daily DESC;

 

6. 버뮤다 삼각지대에 들어가버린 택배

Brazilian E-Commerce Public Dataset by Olist 데이터셋은 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터 입니다. 그 중 olist_orders_dataset 테이블에는 주문 ID, 고객 ID, 주문 상태, 구매 시각 등 주문 내역 데이터가 들어있습니다. Olist의 주문부터 배송까지 프로세스는 다음 단계를 통해 이루어지고, 각 단계마다 시각을 기록하고 있습니다.

1. 고객의 구매
   • order_purchase_timestamp 컬럼에 구매 시점이 저장됨
2. 판매자가 주문을 승인
   • order_approved_at 컬럼에 승인 시점이 저장됨
3. 택배사에 도착하여 배송 시작
   • order_delivered_carrier_date 컬럼에 배송 시작 시점이 저장됨
4. 배송 완료
   • order_delivered_customer_date 컬럼에 배송 완료 시점이 저장됨

종종 택배사에 물건을 보내 배송 시작이 되었는데, 고객에게 택배가 도착하지 않는 일이 있습니다.

이런 경우 order_delivered_carrier_date 컬럼에 택배사 도착 시각은 기록되지만, order_delivered_customer_date 컬럼의 값은 null으로 저장됩니다. 일단 원인을 파악하기 전에 이런 일들이 얼마나 발생하고 있는지 현황 파악을 해보려고 합니다.

 

2017년 1월 한 달 동안 택배사에 전달되었지만 배송 완료는 되지 않은 주문 건수를 택배사 도착일을 기준으로 집계하는 쿼리를 작성해주세요. 쿼리 결과는 택배사 도착일을 기준으로 오름차순 정렬되어야 하고, 아래 컬럼을 포함해야 합니다.

• delivered_carrier_date - 택배사 도착 날짜 (예: 2017-01-16)
• orders - 택배사에 도착했지만, 고객에게 배송되지 않은 주문 건 수

또한 위 서술에 해당하는 주문이 없었던 날은 출력에서 제외해주세요. 예를 들어, 2017년 1월 1일에는 택배사에 전달되었지만, 배송 완료가 되지 않은 주문은 없습니다. 이 날은 SQL 실행 결과에 포함되지 않아야 합니다.

SELECT DATE(order_delivered_carrier_date) AS delivered_carrier_date,
       COUNT(order_id) AS orders
FROM olist_orders_dataset
WHERE DATE(order_delivered_carrier_date) BETWEEN '2017-01-01' AND '2017-01-31'
      AND order_delivered_customer_date IS NULL
GROUP BY delivered_carrier_date;

 

7. 쇼핑몰의 일일 매출액

Brazilian E-Commerce Public Dataset by Olist 데이터셋은 브라질의 이커머스 웹사이트인 Olist Store의 판매 데이터 입니다. 그 중 olist_orders_dataset 테이블에는 주문 ID, 고객 ID, 주문 상태, 구매 시각 등 주문 내역 데이터가 들어있습니다. olist_order_payments_dataset 테이블에는 주문 ID, 결제 방법, 결제 금액 등 각 주문의 결제와 관련된 정보가 저장되어 있습니다. 두 테이블을 이용해 2018년 1월 1일 이후 쇼핑몰의 일일 매출액을 계산하는 쿼리를 작성해주세요.

 

주문 각각에 대해 매출이 일어나는 시점은 olist_orders_dataset 테이블의 order_purchase_timestamp 컬럼에 기록되고, 주문 금액은 olist_order_payments_dataset 테이블의 payment_value 컬럼에 기록됩니다.

 

쿼리 결과는 아래 두 컬럼을 포함해야 하고, 매출 날짜 기준으로 오름차순 정렬되어 있어야 합니다. 매출액은 반올림 해 소수점 둘째자리까지 출력해주세요.

• dt - 매출 날짜 (예: 2018-01-01)
• revenue_daily - 해당 날짜의 매출액

SELECT DATE(o.order_purchase_timestamp) AS dt,
       ROUND(SUM(p.payment_value), 2) AS revenue_daily
FROM olist_orders_dataset o, olist_order_payments_dataset p
WHERE o.order_id = p.order_id
      AND DATE(o.order_purchase_timestamp) >= '2018-01-01'
GROUP BY dt
ORDER BY dt;

 

8. 점검이 필요한 자전거 찾기

따릉이를 운영하는 서울시에서는 매달 따릉이 자전거의 정기점검을 진행하고 있습니다. 1달에 주행 거리가 50km 이상인 자전거가 정기점검 대상에 포함됩니다.

 

2021년 2월 정기점검 대상 자전거를 추출하려고 합니다. rental_history 테이블을 사용해 2021년 1월 한 달간 총 주행 거리가 50km 이상인 자전거의 ID를 출력하는 쿼리를 작성해주세요.

SELECT bike_id
FROM rental_history
WHERE DATE(rent_at) BETWEEN '2021-01-01' AND '2021-01-31'
      AND DATE(return_at) BETWEEN '2021-01-01' AND '2021-01-31'
GROUP BY bike_id
HAVING SUM(distance) >= 50000;

 

9. 레스토랑의 대목

tips 테이블에는 식사 금액, 팁, 결제자 성별, 요일, 시간대 등 어느 레스토랑의 테이블 당 결제에 관련된 데이터가 들어있습니다. 요일별 매출액 합계를 구하고, 매출이 1500 달러 이상인 요일의 결제 내역을 모두 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 tips 테이블에 있는 모든 컬럼이 포함되어야 합니다.

SELECT TIPS.*
FROM (
    SELECT day
    FROM tips
    GROUP BY day
    HAVING SUM(total_bill) >= 1500
    ) AS DAY, tips
WHERE TIPS.DAY = DAY.DAY;

 

10. 레스토랑의 요일별 VIP

tips 테이블에는 식사 금액, 팁, 결제자 성별, 요일, 시간대 등 어느 레스토랑의 테이블 당 결제에 관련된 데이터가 들어있습니다. 요일별로 가장 높은 금액의 결제 내역을 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 tips 테이블에 있는 모든 컬럼이 포함되어야 합니다.

SELECT *
FROM tips
GROUP BY day
HAVING MAX(total_bill);

 

11. 다음날도 서울숲의 미세먼지 농도는 나쁨

서울숲 일별 평균 대기오염도 데이터셋은 2022년 서울숲 대기오염도 측정소에서 매일 기록한 대기오염 정보를 담고 있습니다.

 

measurements 테이블의 pm10 컬럼에는 다양한 대기오염도 측정 기준 중에서도 미세먼지(PM10) 농도가 기록되어 있습니다. 이 데이터를 이용하여 당일의 미세먼지 농도보다 바로 다음날의 미세먼지 농도가 더 안좋은 날을 찾아주세요. 결과는 아래 컬럼들을 포함해야 합니다.

• today: 당일 (YYYY-MM-DD)
• next_day: 다음날 (YYYY-MM-DD)
• pm10: 당일의 미세먼지 농도
• next_pm10: 다음날의 미세먼지 농도

SELECT *
FROM (
      SELECT measured_at AS today,
            LEAD(measured_at, 1) OVER (ORDER BY measured_at) AS next_day,
            pm10,
            LEAD(pm10, 1) OVER (ORDER BY measured_at) AS next_pm10
      FROM measurements) A
WHERE A.pm10 < A.next_pm10

 

<시간 더하기 빼기 함수>

• DATE_ADD는 기준 날짜에 입력된 기간만큼을 더하는 함수: DATE_ADD(기준 날짜, INTERNAL)
• DATE_SUB는 기준 날짜에 입력된 기간만큼을 빼는 함수: DATE_SUB(기준 날짜, INTERNAL)

예시 1) 현재 시간에 1초 더하기

SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND);

 

예시 2) 현재 시간에 1분 더하기

SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE); 

 

예시 3) 현재 시간에 1시간 더하기

SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);

 

예시 4) 현재 시간에 1일 더하기

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); 

 

예시 5) 현재 시간에 1달 더하기

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

 

예시 6) 현재 시간에 1년 더하기

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

 

<LAG, LEAD 함수>

• LAG(대상 컬럼명) OVER(정렬 컬럼명) : 이전 행의 값을 리턴
• LEAD(대상 컬럼명) OVER(정렬 컬럼명) : 다음 행의 값을 리턴

 

12. 제목이 모음으로 끝나지 않는 영화

DVD Rental Store 데이터베이스는 DVD 대여점의 관리 프로그램이 사용하는 데이터베이스입니다. 그 중 film 테이블은 DVD 대여점에서 취급하는 영화 정보를 담고 있습니다.

 

17세 미만 학생이 DVD 대여점에 혼자와서는 대여할 수 없는 영화 중 제목이 모음(’A’, ‘E’, ‘I’, ‘O’, ‘U’)로 끝나지 않는 영화를 찾고 싶습니다. 조건에 맞는 영화 제목(title)을 출력하는 쿼리를 작성해주세요. 다른 컬럼은 출력되지 말아야 합니다.

SELECT title
FROM film
WHERE rating IN ('R', 'NC-17')
  AND title NOT LIKE '%A'
  AND title NOT LIKE '%E'
  AND title NOT LIKE '%I'
  AND title NOT LIKE '%O'
  AND title NOT LIKE '%U';

 

13. 언더스코어(_)가 포함되지 않은 데이터 찾기

ga 테이블의 page_location 컬럼은 페이지 뷰, 클릭, 스크롤 등의 사용자 행동이 수집된 페이지 경로를 담고 있습니다. page_location 컬럼의 값이 언더스코어('_')를 포함하지 않는 경우만 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 page_location 컬럼만 있어야 하고, 중복되는 값은 1번만 나와야 하며 오름차순으로 정렬되어 있어야 합니다.

SELECT DISTINCT page_location
FROM ga
WHERE page_location NOT LIKE '%\_%' ESCAPE '\';

 

 

✅ LIKE를 통한 문자열 탐색

• '_'은 한 문자를 나타내는 와일드 카드이고, '%'은 문자열을 나타내는 와일드 카드
• 와일드카드의 언더스코어 포함 여부를 탐색하기 위해서는 이스케이프 처리가 필요 !!!
• SQL에서 이스케이프 처리는 백슬래쉬 \을 사용함
  → 찾고자 하는 패턴에 \를 통해 이스케이프 처리 후, 뒤에 ESCAPE '\'을 붙여야 제대로 작동

 

14. 게임을 10개 이상 발매한 게임 배급사 찾기

Video Game Sales with Ratings 데이터베이스에는 2016년까지 발매된 게임의 주요 정보와 판매량, 평점 정보를 담고 있습니다. 처음 개발한 게임의 출시를 앞두고 있는 당신은 경험 많은 게임 배급사와 협업하기 위해 게임 배급사로 참여한 게임이 10개 이상인 회사에게 협업 제안을 보내려고 합니다.

 

데이터베이스를 조회해 게임 배급사로 참여한 게임이 10개 이상인 회사의 이름을 출력하는 쿼리를 작성해주세요. 쿼리 결과는 아래 컬럼을 포함하고 있어야 합니다.

• name: 게임 배급사 이름

SELECT c.name
FROM games g
     LEFT JOIN companies c ON c.company_id = g.publisher_id
GROUP BY g.publisher_id
HAVING COUNT(g.game_id) >= 10;

 

15. 기증품 비율 계산하기

Museum of Modern Art Collection 데이터베이스는 미국 뉴욕의 근현대 미술관인 MoMA의 작품 정보를 담고 있습니다. artworks 테이블에서는 MoMA가 소장한 작품의 정보가 있습니다. 작품 정보 중 credit 컬럼에 'gift'라는 단어가 등장하는 작품은 MoMA가 다른 사람이나 단체로 부터 기증 받은 기증품이라는 뜻입니다.

 

이 테이블의 데이터를 활용해 소장품 중 부분 기증품을 포함한 기증품의 비율이 얼마나 되는지 계산하는 쿼리를 작성해주세요. 기증품의 비율은 백분율로 계산되어야하고, 소수점 아래 넷째 자리에서 반올림 해 셋째 자리까지 표시 되어야 합니다. 예를 들어, 전체 소장품 중 1/3의 작품이 기증품이라면 제출된 답안은 33.333 이어야 합니다.

SELECT ROUND(100.0 * (SELECT COUNT(DISTINCT artwork_id)
                      FROM artworks
                      WHERE credit LIKE '%gift%') / COUNT(DISTINCT artwork_id), 3) AS ratio
FROM artworks

 

✅ 정수형 나누기 정수형은 정수형이므로, 실수형으로 바꿔주어야 함.

• 실수형으로 변환하는 과정에서 1.0 대신 100.0으로 바꾸고 round 함수를 이용해 셋째 자리까지만 나타낸다.

✅ 100.0은 먼저 곱하고 나누기 연산을 해야 함. (아래 코드로 작성하면 0이 출력)

• 연산 순서에서 100.0을 먼저 곱하고 나누기 연산을 수행하면, 나누기 연산에서 부동소수점 계산이 이루어짐
• 나누기를 먼저 하고 100.0을 곱하면, 나누기에서 정수 나누기가 일어나 결과가 0이 된다

SELECT ROUND((SELECT COUNT(DISTINCT artwork_id)
              FROM artworks
              WHERE credit LIKE '%gift%') / COUNT(DISTINCT artwork_id) * 100.0, 3) AS ratio
FROM artworks

 

16. 최대값을 가진 행 찾기

points 테이블은 프란시스 앤스컴이 만든 Anscombe's quartet 데이터를 담고 있습니다. 이 데이터는 quartet 컬럼에 의해 4개의 서브셋으로 나뉘어지고, 각 서브셋은 평균, 표본 분산, 상관계수 등이 거의 동일하나 데이터의 분포를 시각화하면 전혀 다른 분포를 가지는 특징이 있습니다.

 

points 테이블에서 가장 큰 x 값을 가지는 데이터와 가장 큰 y 값을 가지는 데이터의 id를 출력하는 쿼리를 작성해주세요. 결과는 오름차순으로 정렬해주세요.

SELECT id
FROM points
WHERE x = (SELECT MAX(x) FROM POINTS) OR y = (SELECT MAX(y) FROM POINTS)
ORDER BY id ASC;

 

17. 3년간 들어온 소장품 집계하기

Museum of Modern Art Collection 데이터베이스에는 뉴욕 현대 미술관에 소장된 소장품과 그 작가 정보가 들어있습니다. 소장품 정보를 담고 있는 artworks 테이블은 소장품의 소장 일시(acquisition_date)와 소장품의 분류(classification) 정보가 들어있습니다. 이 정보를 바탕으로 2014년부터 2016년까지 3년간 어떤 분류의 소장품이 많이 추가되었는지 알고자 합니다.

 

아래와 예시와 같은 형태로 각 분류에 대해 연도별 추가된 소장품 수를 집계하는 쿼리를 작성해주세요. 쿼리 결과는 아래 컬럼을 포함해야하고, 컬럼 순서 역시 아래 예시 순서와 동일해야하며, 각 행은 분류(classification) 컬럼 기준으로 오름차순 정렬되어 있어야 합니다. 또한, 집계하는 3년간 추가된 소장품이 없더라도 이전 년도에 소장품이 있는 분류라면 해당 분류를 결과 테이블에 포함해주세요.

• classification: 소장품 분류
• 2014: 2014년
• 2015: 2015년
• 2016: 2016년

SELECT classification,
       COUNT(CASE WHEN acquisition_date LIKE '2014%' THEN 1 END) AS '2014',
       COUNT(CASE WHEN acquisition_date LIKE '2015%' THEN 1 END) AS '2015',
       COUNT(CASE WHEN acquisition_date LIKE '2016%' THEN 1 END) AS '2016'
FROM artworks
GROUP BY classification

 

✅ SolveSQL은 SQLlite를 써서 IF문과 YEAR 함수가 사용되지 않는다. 그래서 CASE문을 활용했다.