Day 09. 게임 평점 예측하기 1https://solvesql.com/problems/predict-game-scores-1/WITH T1 AS ( SELECT DISTINCT genre_id , ROUND(AVG(critic_score) OVER(PARTITION BY genre_id),3) AS critic_score , CEIL(AVG(critic_count) OVER(PARTITION BY genre_id)) AS critic_count , ROUND(AVG(user_score) OVER(PARTITION BY genre_id),3) AS user_score , CEIL(AVG(user_count) OVER(PARTITION BY genre_id)) AS user_cou..

Day 08. 온라인 쇼핑몰의 월 별 매출액 집계https://solvesql.com/problems/shoppingmall-monthly-summary/WITH T1 AS ( SELECT oi.order_id , DATE_FORMAT(o.order_date, '%Y-%m') AS order_month , oi.price * oi.quantity AS amount FROM orders AS o INNER JOIN order_items AS oi ON o.order_id = oi.order_id), n_cancel AS ( SELECT order_month , SUM(amount) AS 'ordered_amount' FROM T1 WHERE order_id NOT LIKE ..

Day 07. 기증품 비율 계산하기https://solvesql.com/problems/ratio-of-gifts/WITH T1 AS ( SELECT artwork_id , title , LOWER(credit) , CASE WHEN LOWER(credit) LIKE '%gift%' THEN artwork_id END AS gift FROM artworks)SELECT ROUND(COUNT(gift) / COUNT(*)*100,3) AS ratioFROM T1❗ROUND() 사용해서 출력 포맷 맞추기   +) WITH 절❗LOWER() 사용해서 소문자로 바꾸기 ❗CASE WHEN, LIKE 사용해서 소문자 credit 중에 gift 찾아 새로운 열 만들기

Day 06. 게임을 10개 이상 발매한 퍼블리셔 찾기https://solvesql.com/problems/publisher-with-many-games/SELECT nameFROM companiesWHERE company_id IN (SELECT publisher_id FROM games GROUP BY publisher_id HAVING COUNT(DISTINCT game_id) >= 10)❗WHERE 절에 company_id와 publisher_id가 같은 조건으로 이름 추출   +) WHERE 절 서브쿼리 ❗GROUP BY, HAVING 사용해서 그룹화 후 조건에 맞춰서 아이디 뽑기

Day 05. 언더스코어(_)가 포함되지 않은 데이터 찾기https://solvesql.com/problems/data-without-underscore/WITH T1 AS ( SELECT DISTINCT page_location , INSTR(page_location, '_') AS U_BAR FROM ga LIMIT 1000)SELECT page_locationFROM T1WHERE U_BAR = 0ORDER BY page_location ASC ❗WHERE 절에 언더스코어가 없는 것 조건 필터링 +) WITH 절❗INSTR() 사용해서 page_location에서 _ 위치 찾기

Day 04. 지자체별 따릉이 정류소 개수 세기https://solvesql.com/problems/count-stations/SELECT local, COUNT(station_id) AS num_stationsFROM stationGROUP BY localORDER BY num_stations ASC❗SELECT 절에 집계함수 COUNT() 사용❗GROUP BY 절에 조건 넣기

Day 03. 제목이 모음으로 끝나지 않는 영화https://solvesql.com/problems/film-ending-with-consonant/SELECT titleFROM filmWHERE SUBSTR(TITLE,-1) NOT IN ('A','E','I','O','U') AND (rating = 'R' OR rating = 'NC-17')❗where 절에 substr() 사용해서 뒤에서부터 모음이 있는지 없는지 확인❗where 절에 rating 조건 넣기

Day 02. 펭귄 조사하기https://solvesql.com/problems/inspect-penguins/SELECT island, speciesFROM penguinsGROUP BY island, speciesORDER BY island ASC, species ASC❗group by 절에 조건 넣기