SQL | SOLVESQL 코딩테스트 : Lv2. (AGGREGATE)

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

https://solvesql.com/problems/tip-analysis/

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

❗ROUND(), AVG() 사용해서 출력 포맷 맞추기

 

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

https://solvesql.com/problems/blog-counter/

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 ASC

❗방문한 유저 수를 집계해야하므로 DISTINCT 사용
❗WHERE 절에 BETWEEN 사용해서 날짜 필터링

 

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

https://solvesql.com/problems/settled-sellers-2/

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

❗seller_id를 기준으로 DISTINCT
❗GROUP BY, HAVING 사용해서 조건 필터링

 

Lv2. 식사 금액과 팁의 관계

https://solvesql.com/problems/tip-and-revenue/

SELECT day
  , SUM(tip) AS tip_daily
  , SUM(total_bill) AS revenue_daily
  , ROUND(SUM(tip) / SUM(total_bill)*100,2) AS tip_revenue_pct
FROM tips
GROUP BY day
ORDER BY tip_revenue_pct DESC

 

Lv2. 레스토랑의 일일 매출

https://solvesql.com/problems/daily-revenue/

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

 

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

https://solvesql.com/problems/shipment-in-bermuda/

SELECT DATE(order_delivered_carrier_date) AS delivered_carrier_date
  , COUNT(*) AS orders
FROM olist_orders_dataset
WHERE order_purchase_timestamp BETWEEN '2017-01-01' AND '2017-01-31'
  AND order_delivered_carrier_date IS NOT NULL
  AND order_delivered_customer_date IS NULL
GROUP BY DATE(order_delivered_carrier_date)
HAVING delivered_carrier_date BETWEEN '2017-01-01' AND '2017-01-31'
ORDER BY delivered_carrier_date ASC

❗WHERE 절에 AND 사용해서 구매시각, 택배사 도착, 배송 실패 조건 결합하기
❗HAVING 절에 날짜 필터링 추가로 해서 1월에 도착한 택배만 필터링하기

 

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

https://solvesql.com/problems/inspection-needed-bike/

SELECT bike_id
FROM rental_history
WHERE rent_at LIKE '2021-01%'
  AND return_at LIKE '2021-01%'
GROUP BY bike_id
HAVING SUM(distance) >= 50000

❗WHERE 절에 대여 날짜, 반납 날짜 필터링
❗HAVING 으로 조건 2차 필터링

 

Lv2. 레스토랑의 대목

https://solvesql.com/problems/high-season-of-restaurant/

SELECT *
FROM tips
WHERE day IN (SELECT day
              FROM tips AS t
              GROUP BY day
              HAVING SUM(t.total_bill) >= 1500)

❗WHERE 절에 서브쿼리 작성해서 수입이 1500 넘는 요일만 저장

 

Lv2. 레스토랑의 요일별 VIP

https://solvesql.com/problems/restaurant-vip/

WITH T1 AS (
  SELECT * 
    , RANK() OVER(PARTITION BY day ORDER BY total_bill DESC) AS RNK
  FROM tips
)

SELECT total_bill, tip, sex, smoker, day, time, size
FROM T1
WHERE RNK = 1

+) WITH 절

❗RANK() 함수와 윈도우 함수 사용해서 DAY 기준으로 TOTAL_BILL 높은 순서로 순위

 

Lv2. 고액 영수증 찾기

https://solvesql.com/problems/highest-bill-per-size/

SELECT *
FROM tips
WHERE (size, total_bill) IN (SELECT size
                              , MAX(total_bill)
                            FROM tips
                            GROUP BY size)
ORDER BY size ASC

❗WHERE 절에 서브쿼리 작성해서 인원과 최대값 저장 후, 비교

 

Lv2. 일요일 저녁 손님들의 식사 금액

https://solvesql.com/problems/bill-of-sunday-customers/

SELECT FLOOR(SUM(total_bill)) AS sum_total_bill
    , FLOOR(AVG(total_bill)) AS avg_total_bill
FROM tips
WHERE day = 'Sun'
    AND time = 'Dinner'

❗FLOOR() 사용해서 소수점 버림