SolveSQL :: 난이도 1 문제풀이

Coding Test/SolveSQL

SolveSQL :: 난이도 1 문제풀이

hyeeein 2025. 3. 25. 16:00

1. 모든 데이터 조회하기

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

points 테이블에 있는 모든 데이터를 조회하는 쿼리를 작성해주세요.

SELECT *
FROM POINTS;

2. 일부 데이터 조회하기

points 테이블에서 quartet 컬럼의 값이 I인 데이터만 조회하는 쿼리를 작성해주세요.

SELECT *
FROM POINTS
WHERE QUARTET = 'I';

3. 데이터 정렬하기

points 테이블에서 quartet 컬럼의 값이 I인 데이터만 조회하되, y 컬럼의 값을 기준으로 오름차순 정렬이 되도록 쿼리를 작성해주세요.

SELECT *
FROM POINTS
WHERE QUARTET = 'I'
ORDER BY Y ASC;

4. 데이터 그룹으로 묶기

points 테이블에 쿼리를 수행해 quartet으로 구분되는 각 서브셋 데이터에 대해서 아래 통계량을 계산하는 쿼리를 작성해주세요. 계산된 값은 소수점 아래 셋째 자리에서 반올림 해야 합니다. 결과 데이터에는 아래 5개의 컬럼이 존재 해야 합니다.

quartet - 콰르텟
x_mean - x 평균
x_var - x 표본 분산
y_mean - y 평균
y_var - y 표본 분산

▷ 힌트: 이 문제에서 구해야 하는 분산은 모분산이 아니라 표본 분산입니다. DBMS에 따라 표본 분산을 구하는 함수가 다를 수 있으니 유의해주세요.

SELECT quartet,
       ROUND(AVG(x), 2) AS x_mean,
       ROUND(VARIANCE(x), 2) AS x_var,
       ROUND(AVG(y), 2) AS y_mean,
       ROUND(VARIANCE(y), 2) AS y_var
FROM points
GROUP BY quartet;

5. 몇 분이서 오셨어요?

Waiter's Tips 데이터에는 레스토랑에서 근무한 웨이터가 서빙했던 테이블과 팁에 대한 정보가 들어있습니다. 서빙했던 테이블 중 고객이 홀수 명이었던 경우만 보여주는 쿼리를 작성해주세요.

쿼리 결과에는 모든 컬럼이 출력되어야 합니다. 테이블 당 일행의 수는 size 컬럼에 들어있습니다. 예를 들어, 5명이서 방문한 테이블의 경우 size 컬럼의 값이 5입니다.

SELECT *
FROM tips
WHERE size % 2 == 1;

6. 최근 올림픽이 개최된 도시

역대 올림픽 정보 데이터셋은 역대 올림픽 경기와 관련된 데이터가 들어있는 테이블로 이루어져 있습니다. 그 중 games 테이블은 역대 올림픽 개최년도와 시즌, 도시 기록이 저장되어 있습니다.

2000년 이후 올림픽이 개최된 도시의 이름을 앞에서부터 3글자만 추출하는 쿼리를 작성해주세요. 쿼리 결과에는 올림픽 개최년도와 개최 도시만 출력되도록 하되, 도시 이름은 대문자로 출력되어야합니다. 쿼리 결과에는 아래 두 개의 컬럼이 있어야하고, 결과는 최근에 개최된 도시부터 내림차순으로 정렬되어있어야 합니다.

year - 올림픽 개최년도
city - 올림픽 개최도시 (3글자)

SELECT YEAR, UPPER(SUBSTR(CITY, 0, 4)) AS city
FROM GAMES
WHERE YEAR >= 2000
ORDER BY YEAR DESC;

7. 우리 플랫폼에 정착한 판매자 1

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

우리 플랫폼에서 상품을 많이 판매하고 있는 판매자가 누구인지 알고 싶습니다. 총 주문이 100건 이상 들어온 판매자 리스트를 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 아래 컬럼이 있어야 합니다.

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

SELECT seller_id, COUNT(DISTINCT order_id) AS orders
FROM olist_order_items_dataset
GROUP BY seller_id
HAVING ORDERS >= 100;

8. 최고의 근무일을 찾아라

Waiter's Tips 데이터셋에는 식사 금액, 팁, 결제자 성별, 결제 요일 등 레스토랑 손님들의 계산 정보가 들어있습니다. 이 레스토랑에 근무하는 서버는 팁을 많이 받을 수 있는 날 하루를 골라 근무하고 싶습니다.

요일별로 팁 총액을 집계하고 팁이 가장 많았던 요일과 그날의 팁 총액을 출력하는 쿼리를 작성해주세요. 그날의 팁 총액은 소수점 셋째 자리에서 반올림하여 출력되도록 해주세요. 결과 데이터는 아래 컬럼을 포함해야 합니다.

day - 요일
tip_daily - 요일 별 팁 총액

SELECT DAY, ROUND(SUM(TIP), 3) AS tip_daily
FROM TIPS
GROUP BY DAY
ORDER BY tip_daily DESC
LIMIT 1;

9. 첫 주문과 마지막 주문

Brazilian E-Commerce Public Dataset By Olist 데이터셋은 브라질의 Olist Store 라는 쇼핑몰에서 수집한 데이터를 담고 있습니다. 그 중 olist_orders_dataset 테이블에는 쇼핑몰에서 상품을 구매한 고객, 주문 일자, 상품 도착 일자와 같은 주문 정보가 들어있습니다.

주문 일자를 나타내는 order_purchase_timestamp 컬럼을 통해 첫 주문 일자와 마지막 주문 일자를 알아보려고 합니다. 아래 두 컬럼을 포함하는 쿼리를 작성해주세요.

first_order_date - 첫 주문 일자 (예: 2018-01-01)
last_order_date - 마지막 주문 일자 (예: 2018-08-31)

SELECT MIN(DATE(order_purchase_timestamp)) AS first_order_date,
       MAX(DATE(order_purchase_timestamp)) AS last_order_date
FROM olist_orders_dataset

< 참고 >

Date 함수 : YYYY-MM-DD
Time 함수: HH:MM:SS
Datetime 함수: YYYY-MM-DD HH:MM:SS

10. 많이 주문한 테이블 찾기

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

SELECT *
FROM TIPS
WHERE TOTAL_BILL > (SELECT AVG(TOTAL_BILL) FROM TIPS);

11. 레스토랑의 일일 평균 매출액 계산하기

tips 테이블에는 식사 금액, 팁, 결제자, 성별, 요일, 시간대 등 어느 레스토랑의 테이블 당 결제에 관련된 데이터가 들어있습니다.

이 레스토랑의 일일 평균 매출을 계산해주세요. 예를 들어, 일일 매출이 다음과 같다면 일일 평균 매출은 287.5 입니다. 컬럼의 이름은 avg_sales로 출력해주세요. 결과는 소수점 아래 셋째 자리에서 반올림하여 둘째 자리까지 표시되어야 합니다.

SELECT ROUND(AVG(A.bills), 2) AS avg_sales
FROM (SELECT SUM(total_bill) AS bills
      FROM tips
      GROUP BY day) AS A

12. 레스토랑의 영업일

tips 테이블에는 식사 금액, 팁, 결제자 성별, 결제 요일 등 레스토랑 손님들의 결제 내역 정보가 들어있습니다.

레스토랑이 무슨 요일에 영업을 하는지 궁금합니다. tips 테이블에 들어있는 요일 데이터를 중복없이 보여주는 쿼리를 작성해주세요. 요일은 tips 테이블의 day 컬럼에 들어있습니다. 결과 컬럼의 이름은 day_of_week로 표시해주세요.

SELECT DISTINCT DAY AS day_of_week
FROM TIPS;

13. 크리스마스 게임 찾기

Video Game Sales with Ratings 데이터베이스에는 1980년부터 2016년까지 출시된 게임 정보가 들어있습니다.

크리스마스에 놀러올 조카를 위해 함께 할 수 있는 크리스마스 게임을 찾아보려고 합니다. games 테이블에서 게임 이름에 "Christmas" 또는 "Santa"가 포함된 게임을 찾는 쿼리를 작성해주세요. 쿼리 결과에는 아래 컬럼이 포함되어 있어야 합니다.

SELECT game_id, name, year
FROM games
WHERE name LIKE '%Christmas%' or name LIKE '%Santa%';

14. 펭귄 조사하기

Palmer Penguins 데이터베이스는 펭귄의 종, 서식지, 부리 길이 등 펭귄들의 특성 정보를 담고 있습니다. 서식지 별로 어떤 펭귄이 사는지 조사하기 위해 펭귄의 종과 서식지를 출력하는 쿼리를 작성해주세요. 단, 각 서식지에 대해서 펭귄의 종은 중복 없이 1번씩만 나와야 합니다.

쿼리 결과는 아래 컬럼을 포함해야하고, 서식지 순으로 오름차순 정렬되어 있어야 합니다. 서식지가 같은 서로 다른 펭귄 종 사이에서는 펭귄 종 이름 순으로 오름차순 정렬되어 있어야 합니다.

SELECT DISTINCT species, island
FROM penguins
ORDER BY island ASC, species ASC;

15. 지자체별 따릉이 정류소 개수 세기

공유 자전거 서비스인 따릉이를 운영하는 팀에서는 지자체별 따릉이 서비스의 퀄리티를 유지하기 위해 주기적으로 지자체별 통계를 집계하고 있습니다. 최근 지자체별 따릉이 정류소 개수의 큰 차이가 있다는 의견이 있어 사실인지 확인하고자 합니다. station 테이블을 활용해 소속 지자체별 따릉이 정류소의 개수를 집계하는 쿼리를 작성해주세요. 쿼리 결과에는 아래 2개의 컬럼이 있어야 합니다.

SELECT local, COUNT(station_id) as num_stations
FROM station
GROUP BY local
ORDER BY num_stations ASC;

16. 메리 크리스마스 2024

결과에 'Merry Christmas!'가 출력되는 쿼리를 작성해주세요.

SELECT 'Merry Christmas!' AS table_2024;

▷ 파란색은 내가 다시 풀어보아야 할 것들