[mySQL] solvesql 실전반 난이도 1 ~ 2 문제 풀이

1. 첫 주문과 마지막 주문

Q. https://solvesql.com/problems/first-and-last-orders/

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

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

Q. https://solvesql.com/problems/find-tables-with-high-bill/

SELECT *
FROM tips
WHERE total_bill > (SELECT AVG(total_bill) AS 'avg' FROM tips)

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

Q. https://solvesql.com/problems/sales-summary/

SELECT ROUND(AVG(sub.total), 2) AS 'avg_sales'
FROM (
  SELECT day, SUM(total_bill) AS 'total'
  FROM tips
  GROUP BY day) AS sub

4. 서울북부지방법원 따릉이 정류소

Q. https://solvesql.com/problems/court-and-station/

SELECT *
FROM station
WHERE lat > (
  SELECT lat
  FROM station
  WHERE name = '서울북부지방법원')

5. 레스토랑의 대목

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

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

6. 레스토랑의 요일별 VIP

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

SELECT *
FROM tips
WHERE (day, total_bill) 
      IN (SELECT day, MAX(total_bill) AS 'revenue' FROM tips GROUP BY day)

7. 고액 영수증 찾기

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

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

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

Q. https://solvesql.com/problems/bad-finedust-measure/

SELECT m1.measured_at AS 'today',
       m2.measured_at AS 'next_day',
       m1.pm10 AS 'pm10',
       m2.pm10 AS 'next_pm10'
FROM measurements AS m1 
     LEFT JOIN measurements AS m2 ON m2.measured_at = DATE_ADD(m1.measured_at, INTERVAL 1 DAY)
WHERE m2.pm10 > m1.pm10