[solvesql] Lv2 문제 풀이 및 함수 정리

1. 두 테이블 결합하기

SELECT
  DISTINCT(records.athlete_id) AS athlete_id 
FROM
  records
  INNER JOIN events 
  ON records.event_id = events.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. 일별 블로그 방문자 수 집계

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
  event_date_kst ASC;

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

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

POINT!

olist_order_items_dataset 같은 주문 상세 테이블에서는

• 하나의 order_id가 여러 product_id 행으로 나뉘어서 들어갑니다.
• 각 상품의 가격(price)이 다를 수 있고, 같은 주문 안에서도 50달러 이상인 상품이 여러 개면 그 주문이 중복 카운트됩니다.

그래서 "주문 건수"를 세는 문제라면 반드시 COUNT(DISTINCT order_id)를 써야 합니다.

5. 레스토랑의 일일 매출

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

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

SELECT
  strftime ("%Y-%m-%d", order_delivered_carrier_date) AS delivered_carrier_date,
  COUNT(DISTINCT (order_id)) AS orders
FROM
  olist_orders_dataset
WHERE
  strftime ("%Y-%m-%d", order_delivered_carrier_date) LIKE '2017-01%'
  AND order_delivered_customer_date IS NULL
GROUP BY
  delivered_carrier_date
ORDER BY
  delivered_carrier_date ASC;

POINT!

① STRFTILME()

: 날짜 /시간을 문자열로 포멧할 때 쓰는 함수 MySQL에서 DATE_FORMAT()과 같은 역할

다만 MySQL의 DATE_FORMAT()이랑 SQLite의 STRFTIME()의 인자 순서가 반대라는 점은 주의해야합니다!

 

기본 문법

strftime(format, date/time 값)

 

• format : 출력 형식 (포맷 코드 사용)
• date/time 값 : 날짜 컬럼이나 'now', 'YYYY-MM-DD' 같은 날짜 문자열

 

 

② GROUP BY 에 SELECT절에서 정의한 별칭을 쓸 수 있다

• 실행 순서상 SELECT절이 GROUP BY보다 나중에 평가되지만
  대부분의 DBMS는 내부적으로 GROUP BY에서 별칭을 참조할 수 있게 해 줍니다.

• 표준SQL은 GROUP BY에 컬럼 번호나 원래식을 쓰는 걸 더 안전하게 봅니다.