22,000원
다른 수강생들이 자주 물어보는 질문이 궁금하신가요?
- 해결됨SQL 코딩테스트를 위한 마지막 걸음
Date 강의 / leetcode 1321 질문
# [Setting] USE PRACTICE; DROP TABLE Customer; CREATE TABLE Customer (customer_id int, name varchar(20), visited_on date, amount int); INSERT INTO Customer (customer_id, name, visited_on, amount) VALUES (44, 'Ashley', '2019-01-04', 160), (23, 'Sabo', '2019-01-04', 70), (38, 'Moustafa', '2019-01-05', 90), (30, 'Halley', '2019-01-06', 140), (5, 'Elvis', '2019-01-07', 160), (12, 'Leslie', '2019-01-08', 100), (23, 'Sabo', '2019-01-08', 90), (13, 'Will', '2019-01-09', 170), (20, 'Brock', '2019-01-10', 160), (29, 'Leo', '2019-01-10', 90), (33, 'Isaac', '2019-01-11', 60), (46, 'Selena', '2019-01-12', 100), (4, 'Winston', '2019-01-13', 150), (15, 'Marti', '2019-01-13', 160); SELECT * FROM Customer;# [my practice] select b.visited_on, sum(amount) as amount, round(sum(a.amount) /7, 2) as average_amount from customer a inner join (select distinct date_sub(visited_on, interval 6 day) as week_before, visited_on from customer) b on a.visited_on between b.week_before and b.visited_on group by b.visited_on having count(amount) >= 7 order by b.visited_on;올려주신 방식 외에 inner join시 date 구간으로 조건 넣는 방식으로 시도해 보았습니다.위 데이터로 삽입하는 경우, MySQL Workbench에서는 예상 결과대로 출력 되는데 leetcode에서 오류가 나서 혹시 제가 코드 상에서 간과하고 있는 부분이 있는지 궁금해서 질문 드립니다.leetcode 내에 PostgreSQL로 유사한 방식의 해결 법은 확인했었는데 혹시나 해서 링크 올려봅니다. https://leetcode.com/problems/restaurant-growth/solutions/5040002/well-explained-postgresql-solution-beats-99
- 해결됨SQL 코딩테스트를 위한 마지막 걸음
lag, leag 강의에서 game-play-analysis-iv 문제
https://leetcode.com/problems/game-play-analysis-iv/ 이 문제를 풀고 있는데,제가 작성한 코드는 SELECT ROUND(COUNT(player_id) /(SELECT COUNT(DISTINCT player_id) FROM Activity),2) as fractionFROM( SELECT player_id, LAG(player_id) OVER (ORDER BY player_id) AS prev_id, LAG(event_date) OVER (ORDER BY player_id) AS prev_date, event_date, RANK() OVER (PARTITION BY player_id ORDER bY event_date) AS rnk FROM Activity) AWHERE rnk = 2AND DATE_ADD(prev_date, INTERVAL 1 day) = event_date AND player_id = prev_id입니다. 이 코드로 정답을 체크해 보았을 때 wrong answer라 뜨지만, A FROM절 안에서 event_date, rnk의 순서를 LAG 앞으로 바꾸어 주었을 땐 정답 처리가 되었습니다. 왜 그런걸까요??아래는 순서를 바꿨을 때 정답처리 되었던 코드 입니다.SELECT ROUND(COUNT(player_id) /(SELECT COUNT(DISTINCT player_id) FROM Activity),2) as fractionFROM(SELECT player_id, event_date, RANK() OVER (PARTITION BY player_id ORDER bY event_date) AS rnk, LAG(player_id) OVER (ORDER BY player_id) AS prev_id, LAG(event_date) OVER (ORDER BY player_id) AS prev_dateFROM Activity) AWHERE rnk = 2AND DATE_ADD(prev_date, INTERVAL 1 day) = event_date AND player_id = prev_id