코드카타
66. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
-- 내가 작성한 쿼리
SELECT
CONCAT('/home/grep/src/',board_id,'/',file_id,file_name,file_ext) AS 'FILE_PATH'
FROM (
SELECT MAX(views),b.board_id,f.file_id,f.file_name,f.file_ext
FROM used_goods_board b
JOIN used_goods_file f ON b.board_id = f.board_id
ORDER BY f.file_id DESC
) sub※ 오답 이유: 조회수가 가장 높은 게시글의 첨부파일이 2개 이상일 수 있음
→ 최댓값을 MAX가 아닌 RANK로 찾기
-- 1. 조회수 결과를 기준으로 rank 찾기
SELECT board_id,
RANK() OVER (ORDER BY views DESC) AS ranking
FROM used_goods_board b
→ view수가 같은 board_id가 같은 순위로 표시 됨.
Rank가 있는 테이블을 WITH를 이용해 r이라고 정의하고 세 테이블을 모두 조인.
-- 중간과정
WITH r AS(
SELECT board_id,
RANK() OVER (ORDER BY views DESC) AS ranking
FROM used_goods_board
)
SELECT CONCAT('/home/grep/src/',b.board_id,'/',f.file_id,f.file_name,f.file_ext) AS 'FILE_PATH' , b.board_id, r.ranking
FROM used_goods_board b
JOIN used_goods_file f ON b.board_id = f.board_id
JOIN r ON r.board_id = f.board_id
ORDER BY r.ranking;
→ ranking이 1인 첨부파일을 첨부파일 이름으로 내림차순해서 조회.
-- 정답
WITH r AS(
SELECT board_id,
RANK() OVER (ORDER BY views DESC) AS ranking
FROM used_goods_board
)
SELECT CONCAT('/home/grep/src/',b.board_id,'/',f.file_id,f.file_name,f.file_ext) AS 'FILE_PATH'
FROM used_goods_board b
JOIN used_goods_file f ON b.board_id = f.board_id
JOIN r ON r.board_id = f.board_id
WHERE r.ranking = 1
ORDER BY f.file_id DESC;
-- 오답 코드
SELECT f.flavor
FROM first_half f JOIN july j ON f.shipment_id = j.shipment_id
GROUP BY f.flavor
ORDER BY SUM(f.total_order + j.total_order) DESC
LIMIT 3;오답 이유 : JOIN키를 shipment_id로 해버리면 shipment_id가 같은 값끼리 묶여버리기 때문에 한shipment_id를 가진 값들이 나타나지 않음 (밑에 예시로 보면 shipment_id가 109인 두개의 strawberry 주문이 두가지 다 나오지 않음)
>> 따라서 JOIN의 공용키를 favor로 하면 모든 주문의 값이 출력이 가능함
-- 정답 쿼리
SELECT f.flavor
FROM first_half f JOIN july j ON f.flavor = j.flavor
GROUP BY f.flavor
ORDER BY f.total_order + SUM(j.total_order) DESC
LIMIT 3;
-- 오답 쿼리
SELECT a.author_id,
a.author_name,
b.category,
SUM(s.sales) * SUM(b.price) AS 'TOTAL_SALES'
-- 계산 순서 틀림!!!
-- sales가 2, 3이고 Price가 1000, 2000일때 이 계산식이면 (2+3)*(1000+2000)=15000
-- 하지만 구해야하는 값은 (2*1000) + (3*2000) = 8000
FROM book b
JOIN author a ON b.author_id = a.author_id
JOIN book_sales s ON b.book_id = s.book_id
WHERE sales_date LIKE '2022-01-%'
GROUP BY a.author_name, b.category
ORDER BY 1,3 DESC>> total_sales의 계산 순서 수정
-- 정답
SELECT a.author_id,
a.author_name,
b.category,
SUM(s.sales * b.price) AS 'TOTAL_SALES'
FROM book b
JOIN author a ON b.author_id = a.author_id
JOIN book_sales s ON b.book_id = s.book_id
WHERE sales_date LIKE '2022-01-%'
GROUP BY a.author_name, b.category
ORDER BY 1,3 DESC;
69. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
-- 중간과정 1: 8월-10월사이 데이터 중, 대여기록이 5번 이상인 car_id
SELECT car_id , count(*)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY car_id
HAVING COUNT(*) >= 5;
-- 중간과정 2 : 1의 데이터 안에 car_id가 있고, 8월-10월 사이에 MONTH, ca_id, RECORDS 데이터를 구한다.
SELECT MONTH(start_date) MONTH,
car_id,
COUNT(car_id) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE car_id IN (
SELECT car_id
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY car_id
HAVING COUNT(*) >= 5)
AND DATE_FORMAT(start_date,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
-- WHERE문에서 이미 해줬다고 생각하지만 어디까지나 조건문에서 지정해준 것이기 때문에 한번 더 기간 설정 해야함.
GROUP BY car_id;>> MONTH와 car_id를 기준으로 그룹화, 정렬
-- 정답
SELECT MONTH(start_date) MONTH,
car_id,
COUNT(car_id) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE car_id IN (
SELECT car_id
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY car_id
HAVING COUNT(*) >= 5)
AND DATE_FORMAT(start_date,'%Y-%m-%d') BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH , car_id
ORDER BY MONTH , car_id DESC;
-- 중간과정1 : 리뷰를 가장 많이 작성한 사람 조회
SELECT REST_REVIEW.*
FROM REST_REVIEW
INNER JOIN (
SELECT MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY CNT DESC
LIMIT 1 -- 1. 리뷰가 가장 많은 사람의 member_id 조회
)T1 ON REST_REVIEW.MEMBER_ID = T1.MEMBER_ID --2. 리뷰가 가장 많은 사람의 member_id와 같다면 모든 컬럼을 출력
-- 중간과정2 : 이름을 출력하기 위해 member_profile과 Join
SELECT MEMBER_NAME
, REVIEW_TEXT
, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE
INNER JOIN (
SELECT REST_REVIEW.*
FROM REST_REVIEW
INNER JOIN (
SELECT MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY CNT DESC
LIMIT 1
)T1 ON REST_REVIEW.MEMBER_ID = T1.MEMBER_ID
)T2
ON MEMBER_PROFILE.MEMBER_ID = T2.MEMBER_ID
+ 정렬
-- 정답
SELECT MEMBER_NAME
, REVIEW_TEXT
, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE
INNER JOIN (
SELECT REST_REVIEW.*
FROM REST_REVIEW
INNER JOIN (
SELECT MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY CNT DESC
LIMIT 1
)T1 ON REST_REVIEW.MEMBER_ID = T1.MEMBER_ID
)T2
ON MEMBER_PROFILE.MEMBER_ID = T2.MEMBER_ID
ORDER BY REVIEW_DATE,REVIEW_TEXT;
[FACTS]
- 코드카타 Lv4 5문제
[FEELINGS]
- 코드카타 문제를 풀 때 문제를 정확하게 읽지 않아서 오답을 내는 경우가 있음
- 문제 안에서 어떤 조건을 먼저 짜야할지 고민하는 경우가 많다.
- Lv4 이상이 되니 복합적으로 쿼리를 짜야하는 문제가 많아서 한문제당 30분 이상씩 걸려서 답답했다.
[FINDINGS]
- 쿼리가 중간까지 밖에 안될때는 아예 다 지우고 새로운 시각으로 접근해 보는것도 나쁘지 않다. (대신 중간과정은 꼭 저장해놓기)
[FUTURE]
- 문제를 반복해서 풀기, 안풀리는 문제는 답 보지말고 끝까지 해보기.
- 문제가 풀렸어도 내가 푼 방법 말고 더 효과적인 방법이 있는지 참고하기.
파이썬 종합반 3주차 연습문제
1) 조건문 퀴즈
임의의 수를 변수에 넣고 그 변수에 입력된 수가 양수인지 음수인지 판별하여 결과를 출력하는 파이썬 코드를 작성해보세요. (0은 양수로 간주합니다)
x = 256
if x >= 0:
print('x는 양수입니다.')
else:
print('x는 음수입니다.')
2) for문 퀴즈
1부터 10까지의 정수 중에서 홀수만을 포함하는 리스트를 생성하는 파이썬 코드를 작성해보세요.
num = []
for n in range(1,11):
if n % 2 != 0:
num.append(n)
print(num)
3) while문 퀴즈
다음 중 무한 루프를 탈출하는 방법이 아닌 것은?
a) break문 사용
b) continue문 사용
c) 조건문에서 False가 되도록 설정
d) return문 사용
4) 종합 퀴즈
1부터 100까지의 정수 중에서 3의 배수일 때 "Fizz", 5의 배수일 때 "Buzz"를 출력하세요.
for n in range(1,101):
if n % 3 == 0:
print("Fizz")
elif n % 5 == 0:
print("Buzz")
else:
print(n)
'내일배움캠프 > TIL' 카테고리의 다른 글
| [TIL]파이썬 코드카타, 개인과제4번 (0) | 2024.12.16 |
|---|---|
| [TIL]241212_SQL코드카타, Python 개인과제 문제1~3 (2) | 2024.12.12 |
| [TIL]241210_코드카타, 데이터 리터러시 강의 1 (1) | 2024.12.10 |
| [TIL]2412209_코드카타, 파이선 종합반 강의 (2) | 2024.12.09 |
| [WIL]내일배움캠프_2주차 (1) | 2024.12.08 |