Q. https://solvesql.com/problems/estimated-delivery-date/

SELECT DATE(order_purchase_timestamp) AS purchase_date,
       COUNT(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN order_id ELSE NULL END) AS success,
       COUNT(CASE WHEN order_delivered_customer_date >= order_estimated_delivery_date THEN order_id ELSE NULL END) AS fail
FROM olist_orders_dataset 
WHERE order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
GROUP BY DATE(order_purchase_timestamp)
ORDER BY purchase_date ASC

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

Q. https://solvesql.com/problems/bargain-sale/

SELECT order_date,
       SUM(CASE WHEN discount >= 0.8 THEN quantity ELSE 0 END) AS 'big_discount_items',
       SUM(quantity) AS 'all_items'
FROM records 
GROUP BY order_date
HAVING all_items >= 10
AND big_discount_items >= 1
ORDER BY big_discount_items DESC

Q. https://solvesql.com/problems/sales-per-country/

SELECT customers.country,
       (SUM(order_items.price * order_items.quantity)) - (SUM(CASE WHEN order_items.order_id LIKE 'C%' THEN order_items.price * order_items.quantity ELSE '0' END)) AS 'sales'
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
               LEFT JOIN order_items ON orders.order_id = order_items.order_id
WHERE order_date BETWEEN '2019-01-01' AND '2019-01-31'
GROUP BY customers.country
HAVING sales != '0'
ORDER BY sales DESC

Q. https://solvesql.com/problems/refund-austria/

SELECT ABS(SUM(price * quantity)) AS refund_austria
FROM order_items LEFT JOIN orders ON order_items.order_id = orders.order_id
                 LEFT JOIN customers ON orders.customer_id = customers.customer_id
WHERE order_items.order_id LIKE 'C%'
AND country = 'Austria'

☑️ point

• ABS( ) -> 절대값 반환

Q. https://solvesql.com/problems/characteristics-of-orders/

WITH sub AS (
  SELECT region, category, COUNT(DISTINCT order_id) AS cnt
  FROM records
  GROUP BY region, category)

SELECT Region,
       SUM(CASE WHEN category = 'Furniture' THEN cnt END) AS 'Furniture',
       SUM(CASE WHEN category = 'Office Supplies' THEN cnt END) AS 'Office Supplies',
       SUM(CASE WHEN category = 'Technology' THEN cnt END) AS 'Technology'
FROM sub
GROUP BY region
ORDER BY region ASC

☑️ point

• CASE문 사용 후 컬럼이 1개만 나와야하는데 여러 컬럼으로 나뉘어 null 값이 나올 경우 -> 집계함수를 사용해서 대표값 지정!! (GROUP BY에서 집계함수를 사용하는 이유와 같은 개념)
• 참고 : https://velog.io/@sehyunee_/SQL-GROUP-BY에-집계함수를-써야-하는-이유

+) 아래와 같은 풀이도 가능

SELECT region AS Region,
       COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id ELSE NULL END) AS 'Furniture',
       COUNT(DISTINCT CASE WHEN category = 'Office Supplies' THEN order_id ELSE NULL END) AS 'Office Supplies',
       COUNT(DISTINCT CASE WHEN category = 'Technology' THEN order_id ELSE NULL END) AS 'Technology'
FROM records
GROUP BY region
ORDER BY region ASC

우선은 solvesql 사이트에서 입문반 때 풀었던 문제들을 전부 복습하고, 그 외 자습용으로 공개 된 문제들을 전부 풀어보았다!

그 결과 약 일주일 동안 41문제 모두 풀기 성공!!~

생각보다 근본없이 막 다 까먹진 않은 것 같아 다행이었다.. 실전반 문제들도 얼른 차근차근 복습해봐야겠다! 서브쿼리,, 무섭지만...ㅠㅎㅎ

Q. https://solvesql.com/problems/ott-used-by-amy/

SELECT title,
       year,
       genres,
       directors,
       CASE WHEN netflix = '1' THEN 'netflix'
            WHEN netflix = '0' AND prime_video = '1' THEN 'prime_video'
            WHEN netflix = '0' AND prime_video = '0' AND disney_plus = '1' THEN 'disney_plus' ELSE 'hulu' END
            AS 'platform'
FROM movies
WHERE year = 2021
ORDER BY title ASC

☑️ point

• CASE문에 조건 여러개 다는 것 헷갈리지 말기!!! (자꾸 컬럼을 여러개로 나누고 null로 표시되도록 잘못 작성하는 실수 반복중 ㅠㅠ)

1. HAVING 절에
   • challenges_created = max(challenges_created),
   • challenges_created =/ max(challenges_created) 각각의 조건을 걸어서 작성하는 쿼리

• 쿼리가 길고 복잡하다는 단점 존재

SELECT h.hacker_id,
       h.name,
       COUNT(*) AS challenges_created
FROM Hackers AS h
     INNER JOIN Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING challenges_created = (SELECT MAX(challenges_created)
                             FROM (
                                   SELECT hacker_id, COUNT(*) AS challenges_created
                                   FROM Challenges
                                   GROUP BY hacker_id) AS sub)
OR challenges_created IN 
        (SELECT challenges_created
         FROM (
               SELECT hacker_id, COUNT(*) AS challenges_created
               FROM Challenges
               GROUP BY hacker_id) AS sub
         GROUP BY challenges_created
         HAVING COUNT(*) = 1)                                  
ORDER BY challenges_created DESC, h.hacker_id ASC

2. WITH 문을 활용한 서브쿼리

• 훨씬 간결하고 가독성이 좋음

  WITH counter AS (
    SELECT Hackers.hacker_id, 
           Hackers.name, 
           COUNT(*) AS challenges_created
    FROM Challenges 
         INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
    GROUP BY Hackers.hacker_id, Hackers.name)
  

SELECT counter.hacker_id, counter.name, counter.challenges_created FROM counter WHERE counter.challenges_created = (SELECT MAX(challenges_created) FROM counter) OR counter.challenges_created IN (SELECT challenges_created FROM counter GROUP BY challenges_created HAVING COUNT(challenges_created) = 1) ORDER BY counter.challenges_created DESC, counter.hacker_id ASC ```

조회할 데이터 : USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원의 수 조건1. 2021년에 가입 조건2. 나이가 20세 이상 29세 이하

SELECT COUNT(*) AS USERS
FROM user_info
WHERE YEAR(JOINED) = '2021'
AND AGE BETWEEN 20 AND 29

조회할 데이터 : 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호 조건1. 전화번호가 없는 경우, 'NONE'으로 출력 조건2. 결과는 나이를 기준으로 내림차순 정렬, 나이가 같다면 환자이름을 기준으로 오름차순 정렬

SELECT pt_name, pt_no, gend_cd, age, 
       CASE WHEN tlno IS NULL THEN 'NONE' ELSE tlno END AS TLNO
FROM patient
WHERE age <= 12 AND GEND_CD = 'W'
ORDER BY age DESC, pt_name ASC

조회할 데이터 : 평균 일일 대여 요금 조건1. 자동차 종류가 'SUV' 조건2. 평균 일일 대여 요금은 소수 첫번째 자리에서 반올림, 컬럼명은 average_fee로 지정

SELECT ROUND(AVG(daily_fee)) AS average_fee
FROM car_rental_company_car
WHERE car_type = 'SUV'

조회할 데이터 : 이름, 의사ID, 진료과, 고용일자 조건1. 진료과과 흉부외과(CS)이거나 일반외과(GS) 조건2. 고용일자를 기준으로 내림차순 정렬, 고용일자가 같다면 이름을 기준으로 오름차순 정렬

SELECT dr_name,
       dr_id,
       mcdp_cd,
       DATE_FORMAT(hire_ymd, '%Y-%m-%d') AS hire_ymd
FROM doctor
WHERE mcdp_cd IN ('CS', 'GS')
ORDER BY hire_ymd DESC, dr_name ASC

조회할 데이터 : 식당ID, 식당 이름, 음식 종류, 즐겨찾기 수, 주소, 리뷰 평균 점수 조건1. 서울에 위치 조건2. 리뷰 평균점수는 소수점 세번째 자리에서 반올림 조건3. 결과는 평균점수를 기준으로 내림차순 정렬, 평균점수가 같다면 즐겨찾기 수를 기준으로 내림차순 정렬

SELECT rest_info.rest_id,
       rest_info.rest_name,
       rest_info.food_type,
       rest_info.favorites,
       rest_info.address,
       ROUND(AVG(rest_review.review_score), 2) AS score
FROM rest_info 
     INNER JOIN rest_review ON rest_info.rest_id = rest_review.rest_id
WHERE rest_info.address LIKE '서울%'
GROUP BY rest_info.rest_id
ORDER BY score DESC, rest_info.favorites DESC

☑️ 헤맨 부분 '%서울%'이 아닌, '서울%'로 입력해야 정답처리 되었음

조회할 데이터 : 공장ID, 공장 이름, 주소 조건1. 강원도에 위치 조건2. 결과는 공장ID를 기준으로 오름차순 정렬

SELECT factory_id, 
       factory_name,
       address
FROM food_factory
WHERE address LIKE '%강원도%'
ORDER BY factory_id ASC

테이블 내 sex 컬럼에서 값이 m인 것은 f로, f인 것은 m으로 변경

UPDATE Salary
SET sex = CASE WHEN sex = 'f' THEN 'm' ELSE 'f' END

(1) 단일행 서브쿼리 (✨중요) -- 비교연산자와 함께 사용 (=, >, < 등) -- 비교연산자를 사용하기 때문에 서브쿼리로 출력한 결과값은 반드시 단 1개만 나와야 한다.

-- 평균 영수 금액보다 더 많이 지불한 경우를 모두 출력해주세요.
-- 1. 평균 영수 금액 출력 2. 1의 쿼리로 출력한 평균 영수 금액을 WHERE 절의 조건으로 사용하는 쿼리 작성

SELECT *
FROM tips
WHERE total_bill > (SELECT AVG(total_bill) FROM tips)

(2) 다중행 서브쿼리 (✨중요) -- IN, NOT IN과 함께 사용 -- 서브쿼리의 결과값이 컬럼은 1개, 로우가 N개

-- 요일 별 판매 금액이 1500불 이상인 날의 결제 내역을 모두 출력해주세요.
-- 1. 요일 별 판매 금액 출력 => 토요일, 일요일이 '판매 금액 1500불 이상' 조건을 만족함
-- 2. 즉, 요일 별 판매 금액이 1500불 이상인 날 = 토요일, 일요일이므로 문제가 원하는 것은 '토요일/일요일의 결제 내역을 모두 출력해주세요'

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

-- 서브쿼리의 결과값 : SUM(sales)가 1500불 이상인 day를 출력 = 토요일, 일요일 -- 아우터 쿼리 : 서브쿼리에 따라 필터링 된 토요일, 일요일의 전체 결제 내역을 모두 출력

(3) 다중컬럼 서브쿼리 -- 서브쿼리의 결과값이 컬럼 N개, 로우도 N개

-- 요일 별로 가장 높은 금액의 결제 내역들을 출력해주세요.

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

FROM 절 서브쿼리 (✨중요) -- 서브쿼리에 꼭 별칭을 주어야 한다. -- 서브쿼리의 경우 > 임시의 테이블처럼 활용이 가능하다. (조인도 가능)

-- 이 레스토랑의 요일별 매출액의 평균을 구해주세요.

SELECT AVG(sales)
FROM (
    SELECT day, SUM(total_bill) AS sales
    FROM tips
    GROUP BY day) AS daily

-- WITH문 활용 (조인도 가능)

WITH daily AS (
    SELECT day, SUM(total_bill) AS sales
    FROM tips
    GROUP BY day)
SELECT AVG(sales)
FROM daily

-- 예제

-- 각 영수 금액이, 요일 별 매출액에서 차지하는 비율을 계산해주세요. 
-- 비율은 반올림하여 소수점 둘째자리까지만 출력해주세요. 영수 금액이 높은 것부터 출력해주세요.

WITH daily AS (
  SELECT day, SUM(total_bill) AS sales
  FROM tips
  GROUP BY day)

SELECT tips.day, tips.total_bill, ROUND(tips.total_bill / daily.sales * 100, 2) AS pct
FROM tips INNER JOIN daily ON tips.day = daily.day
ORDER BY tips.total_bill DESC

SELECT 절 서브쿼리 -- 단일행 서브쿼리만 사용 가능함 -- 행 바이 행으로 계산되는 개념

-- 각 영수 금액이, 요일 별 매출액에서 차지하는 비율을 계산해주세요. 
-- 비율은 반올림하여 소수점 둘째자리까지만 출력해주세요. 영수 금액이 높은 것부터 출력해주세요.

SELECT t1.day, t1.total_bill,
       (SELECT SUM(total_bill) FROM tips AS t2 WHERE t2.day = t1.day) AS sales,
       ROUND(t1.total_bill / (SELECT SUM(total_bill) FROM tips AS t2 WHERE t2.day = t1.day) * 100, 2) AS pct
FROM tips AS t1
ORDER BY t1.total_bill DESC

-- 각 영수 금액이, 이 레스토랑의 전체 매출액에서 차지하는 비율을 계산해주세요. 
-- 비율은 반올림하여 소수점 둘째자리까지만 출력해주세요. 영수금액이 높은 것부터 출력해주세요.

SELECT t1.day, t1.total_bill,
       (SELECT SUM(total_bill) FROM tips AS t2) AS sales,
ROUND(t1.total_bill / (SELECT SUM(total_bill) FROM tips AS t2) * 100, 2) AS daily
FROM tips AS t1
ORDER BY t1.total_bill DESC

SELECT DATE(order_purchase_timestamp) AS purchase_date,
       COUNT(DISTINCT CASE WHEN order_estimated_delivery_date >= order_delivered_customer_date THEN customer_id ELSE NULL END) AS success,
       COUNT(DISTINCT CASE WHEN order_estimated_delivery_date < order_delivered_customer_date THEN customer_id ELSE NULL END) AS fail
FROM olist_orders_dataset 
WHERE order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
AND order_delivered_customer_date IS NOT NULL
AND order_estimated_delivery_date IS NOT NULL
GROUP BY purchase_date

☑️ point

1. 정해진 기간의 주문수만 조회하기 위한 WHERE 조건절 작성
2. 배송 완료/배송 예정 시각 데이터가 없는 경우 계산에서 제외하기 위한 WHERE 조건절 추가 작성
3. 배송 예정 시각 > 배송 완료 시각일 경우 -> 배송 예정 시각 이전에 배송이 완료되었음을 의미하므로 'success' 배송 예정 시각 < 배송 완료 시각일 경우 -> 배송 예정 시각 이후에 배송이 완료되었음을 의미하므로 'fail'

REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

WITH fav AS (
    SELECT food_type,
           rest_name,
           MAX(favorites) OVER (PARTITION BY food_type) AS favorites
    FROM rest_info)

SELECT DISTINCT rest_info.food_type,
       rest_info.rest_id,
       rest_info.rest_name,
       rest_info.favorites
FROM rest_info
     INNER JOIN fav ON rest_info.food_type = fav.food_type
     AND rest_info.favorites = fav.favorites
ORDER BY rest_info.food_type DESC

-- 01. Weather Observation Station 6 https://www.hackerrank.com/challenges/weather-observation-station-6

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION.

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou].*'

^ 여기서부터 [ ] 안의 문자가 시작됨 .* 뒤에 아무거나 와도 상관없음 (LIKE 함수의 %와 같은 역할)

-- 02. Weather Observation Station 7 https://www.hackerrank.com/challenges/weather-observation-station-7

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION.

SELECT DISTINCT city
FROM station
WHERE city REGEXP '.*[aeiou]$'

$ [ ] 안의 문자로 끝나야 함

-- 03. Weather Observation Station 8 https://www.hackerrank.com/challenges/weather-observation-station-8

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters.

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou].*[aeiou]$'

^[aeiou] 맨 앞글자가 [ ] 안의 문자들이고, .* 중간은 어떤 것이 와도 상관없고 [aeiou]$ 맨 뒷글자가 [ ] 안의 문자들이어야 한다

-- 04. Weather Observation Station 9 https://www.hackerrank.com/challenges/weather-observation-station-9

Query the list of CITY names from STATION that do not start with vowels.

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[a,e,i,o,u].*'

~이 아니다로 부정할때는 REGEXP 함수 앞에 NOT을 써준다 (LIKE 함수의 NOT LIKE와 비슷한 활용)

-- 05. Weather Observation Station 10 https://www.hackerrank.com/challenges/weather-observation-station-10

Query the list of CITY names from STATION that do not end with vowels.

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '.*[aeiou]$'

-- 06. Weather Observation Station 11 https://www.hackerrank.com/challenges/weather-observation-station-11

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels.

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[aeiou].*'
OR city NOT REGEXP '.*[aeiou]$'

either이라는 조건이 있으므로 맨 앞글자가 모음으로 시작하지 않고 모음으로 끝나지 않는 조건을 OR로 연결시켜준다.