주간 SQL Study 8차시 (윈도우 함수, 서브쿼리)

1️⃣ Contest Leaderboard Q1. https://www.hackerrank.com/challenges/contest-leaderboard/problem Contest Leaderboard | HackerRank Generate the contest leaderboard. www.hackerrank.com 다시한번 느끼지만 문제를 이해하고 정의하는 능력이 중요함!! 콘테스트 리더보드 관련 문제다. 해커id, 이름이 저장된 Hackers 테이블이 있고 Submission_id, hacker_id, challenge_id, score가 저장된 submissions 테이블이 있다. 두개 테이블 조인해서 무언가 해야하는 문제라는걸 알 수 있다. 제시된 조건은 해커, 챌린지별 Max Score 값을..
Data Analystics/SQL 2022. 3. 28. 17:06
주간 SQL Study 6차시 (SolveSQL, Leetcode 문제풀이)

1️⃣ 멘토링 짝꿍 리스트 Q1. https://solvesql.com/problems/mentor-mentee-list/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com select e1.employee_id as mentee_id, e1.name as mentee_name, e2.employee_id as mentor_id, e2.name as mentor_name from employees e1, employees e2 where e1.join_date between '2021-09-01' and '2021-12-31' and e2.join_date
Data Analystics/SQL 2022. 3. 19. 19:27
주간 SQL Study 5차시 (SolveSQL, programmers 문제풀이)

1️⃣ 복수 국적 메달 수상한 선수 찾기 Q1. https://solvesql.com/problems/multiple-medalist/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com SELECT a.name FROM records r LEFT JOIN teams t ON r.team_id = t.id LEFT JOIN athletes a ON r.athlete_id = a.id LEFT JOIN games g ON r.game_id = g.id WHERE g.year >= 2000 AND r.medal IS NOT NULL GROUP BY athlete_id HAVING COUNT(distinct team) > 1 2️⃣ 있었는데요 없었습니다 Q2...
Data Analystics/SQL 2022. 3. 16. 20:17
주간 SQL Study 3차시 (SolveSQL, LeetCode 문제풀이)

1️⃣ 일별 블로그 방문자 수 집계 Q1. https://solvesql.com/problems/blog-counter/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com SELECT event_date_kst AS dt, count(distinct user_pseudo_id) AS users FROM ga WHERE event_date_kst BETWEEN '2021-08-02' AND '2021-08-09' GROUP BY event_date_kst -- HAVING event_date_kst>='2021-08-02' AND event_date_kst ='2021-08-02' AND event_date_kst
Data Analystics/SQL 2022. 3. 9. 23:21
1