■ 출처
- 코딩테스트 : SQLite / 연습문제 / 난이도2
https://solvesql.com/problems/?page=1
https://solvesql.com/problems/?page=1
solvesql.com
■ 연습문제
1. 두 테이블 결합하기
SELECT
DISTINCT records.athlete_id
FROM
events RIGHT JOIN records
ON events.id = records.event_id
WHERE
events.sport = 'Golf'
2. 레스토랑 웨이터의 팁 분석
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. 일별 블로그 방문자 수 집계
BETWEEN 'A' AND 'B' 함수 : A컬럼과 B컬럼의 사이 결과를 조회할 때 사용하는 함수
SELECT
event_date_kst AS dt , COUNT(DISTINCT user_pseudo_id) AS users
FROM
ga
WHERE
event_date_kst BETWEEN '2021-08-02' AND '2021-08-09'
GROUP BY
event_date_kst
ORDER BY
dt
4. 우리 플랫폼에 정착한 판매자 2
DISTINCT 함수 : 중복된 행을 제외하여 조회
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※ 쿼리를 짤 때 중복된 결과가 없다는 고정관념을 갖지말고 오류가 있다 생각하며 DISTINCT 함수로 중복을 제거하는 습관 必
5. 레스토랑의 일일 매출
SELECT
day, SUM(total_bill) AS revenue_daily
FROM
tips
GROUP BY
day
HAVING
revenue_daily >= 1000
ORDER BY
revenue_daily DESC
6. 버뮤다 삼각지대에 들어가버린 택배
IS NULL 함수 : 특정 컬럼의 'NULL'값을 조회하라는 함수
- 예시) '컬럼01' IS NULL
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) LIKE '2017-01%'
AND order_delivered_carrier_date IS NOT NULL
AND order_delivered_customer_date IS NULL
GROUP BY
delivered_carrier_date
ORDER BY
delivered_carrier_date ASC
7. 쇼핑몰의 일일 매출액
SELECT
DATE(order_purchase_timestamp) AS dt,
ROUND(SUM(payment_value), 2) AS revenue_daily
FROM
olist_orders_dataset AS A_Table
INNER JOIN olist_order_payments_dataset AS B_Table
ON A_Table.order_id = B_Table.order_id
WHERE
DATE(order_purchase_timestamp) >= '2018-01-01'
GROUP BY
dt
ORDER BY
dt ASC
8. 점검이 필요한 자전거 찾기
DISTINCT 함수 : 중복된 행을 제외하여 조회
SELECT
DISTINCT bike_id
FROM
rental_history
WHERE
DATE(rent_at) LIKE '2021-01%'
GROUP BY
bike_id
HAVING
SUM(distance) >= 50000
9. 레스토랑의 대목
SELECT
ori_tips.*
FROM
tips AS ori_tips
INNER JOIN (SELECT * FROM tips GROUP BY day HAVING SUM(total_bill) >= 1500) AS cha_tips
ON ori_tips.day = cha_tips.day
10. 레스토랑의 요일별 VIP
SELECT
ori_tips.*
FROM
tips AS ori_tips
INNER JOIN (SELECT max(total_bill) AS total_bill FROM tips GROUP BY day) AS cha_tips
ON ori_tips.total_bill = cha_tips.total_bill※ 특정 테이블의 결과를 출력하고자 2개 테이블을 결합할 때 'ori_tips.*' 방식으로 하나의 테이블을 중심으로 모든 결과값을 조회 가능
11. 다음날도 서울숲의 미세먼지 농도는 나쁨
WITH 문 : 쿼리를 따로 만들어서 임시 테이블로 불러올 수 있도록 지원하는 명령어
LEAD() 함수 : 특정 컬럼에 대한 행(ROW)의 다음 값을 가져오는 명령어
OVER() 함수 : 위도움 함수라 불리며, 집계 함수를 계산하는데 사용하는 명령어
DATE_ADD 함수 : 시간을 더하는 연산을 수행하는 명령어
- INTERVAL 함수 : 날짜 및 시간 값에 대한 연산을 수행할 때 사용되는 문법
WITH t1 AS(
SELECT measured_at AS today,
LEAD(measured_at) OVER(ORDER BY measured_at) AS next_day,
pm10,
LEAD(pm10) OVER(ORDER BY measured_at) as next_pm10
FROM measurements)
SELECT *
FROM t1
WHERE pm10 < next_pm10