데이터 분석

문제 A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.Write a solution to find the employees who are high earners in each of the departments. 각 부서마다 top 3에 해당하는 급여를 받는 직원의 부서명, 이름, 급여를 조회하는 문제이다.단, 같은 급여가 반복되더라도 한 번만 계산해야 한..

문제 Write a solution to find employees who have the highest salary in each of the departments.Return the result table in any order. 부서별 최고 급여를 갖는 직원의 부서명, 이름, 급여를 조회하는 문제이다.📢동일한 최고 급여를 가진 경우 모두 출력해야 하기 때문에 순위함수 중 ROW_NUMBER 함수는 사용해서는 안 된다. Solution ✅ WHERE절 다중 컬럼 서브쿼리를 활용WITH highest AS ( SELECT departmentID , MAX(salary) FROM Employee GROUP BY departmentID)SELECT d.name AS Dep..

문제 Write a solution to find the employees who earn more than their managers.Return the result table in any order. 본인의 매니저보다 급여가 높은 직원의 이름을 조회하는 문제이다.Joe의 salary 옆에 Sam의 salary를 붙여놓고 비교하는 방식으로 접근하면 해결할 수 있을 것이다. Solution ✅ 동일한 두 테이블을 SELF-JOIN하여 접근한다. ● e1 테이블은 기존에 기록된 직원들의 정보를 알기 위해 사용● e2 테이블은 기존에 기록된 직원들의 매니저 정보를 알기 위해 사용SELECT e.name AS EmployeeFROM Employee e JOIN Employee m ON e.manager..

문제Write a solution to find all customers who never order anything. Return the result table in any order. 주문 이력이 없는 고객을 조회하는 문제이다. SolutionSELECT c.name AS CustomersFROM Customers c LEFT JOIN Orders o ON c.id = o.customerID WHERE o.customerID IS NULL/*SELECT name AS CustomersFROM Customers WHERE id NOT IN (SELECT DISTINCT customerId FROM Orders)*/ ● Customers 테이블의 모든 행을 유지하면서, Orders 테이블에서 일치..

문제 Reformat the table such that there is a department id column and a revenue column for each month. Return the result table in any order.  Solution ✅ 제공 테이블의 id, month, revenue 데이터를 가지고, 각 month를 열로 변환하여 revenue 데이터를 각 열에 맞게 매핑해야 하는 문제이다. 👉 CASE문과 집계 함수(MAX, SUM, ... )를 조합하여 피봇 테이블을 구현한다. ● 1 SELECT id , MAX(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue , MAX(CASE WHEN month..

# 1문제 일 별로 pm10의 이동 평균을 계산하여라. 이동 평균은 당일, 전날, 다음날의 pm10을 이용해 계산할 것. 단, 데이터가 시작하는 2022년 1월 1일에는 당일과 다음날의 pm10 수치 정보만 사용할 것. Solution✅ 윈도우 함수 사용하는 방법SELECT measured_at , pm10 , AVG(pm10) OVER(ORDER BY measured_at ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS pm10_running_averageFROM measurements Advent of SQL 2024 챌린지의 [DAY 14] 문제에서 이동평균을 SQL로 구현하는 방법을 공부했기 때문에 쉽게 해결하였다.https://hwangbyeongh..

세 명이 서로 친구인 관계 찾기✔️0215 복습 소셜 네트워크 분석에서는 세 명의 사용자가 서로 친구 관계인 경우를 중요하게 생각한다.  일반적인 사용자는 세 명의 사용자가 서로 친구인 경우가 다수 있지만,  스팸 사용자 또는 친구 관계를 무작위로 맺는 사용자의 경우 전체 친구 수에 비해 세 명의 사용자가 친구인 경우가 많지 않아 이상 사용자 탐지에 유용하게 쓸 수 있기 때문이다. 문제 조건1. ID가 3820인 사용자를 포함해 세 명의 사용자가 친구 관계인 경우를 출력2. 중복된 세 친구 관계를 제외하기 위해 user_a_id 를 만족하도록 출력  SolutionWITH T1 AS ( SELECT e.user_a_id, e.user_b_id, e2.user_c_id FR..

유량(Flow)과 저량(Stock)✔️0215 복습 ‘연도별로 새롭게 소장하게 된 작품의 수’와 같이 일정 기간 동안 측정되는 지표를 ‘유량(Flow) 지표’라고 하고, ‘누적 소장 작품 수’와 같이 특정 시점에 측정되는 지표를 ‘저량(Stock) 지표’라고 한다. 문제 조건1. 연도별로 새롭게 소장하게 된 작품의 수와, 연도별 누적 소장 작품 수를 계산2. 저량 지표에 변화가 없는 연도는 출력되지 않아야 하고, 소장 년도 정보가 없는 작품은 집계에서 제외3. 소장 연도 컬럼 기준 오름차순 정렬 SolutionWITH T1 AS ( SELECT strftime('%Y', acquisition_date) as year, COUNT(*) AS cnt FROM artworks WHERE a..

친구 수 집계하기 ✔️0215 복습문제 조건1. 데이터베이스에 포함된 모든 사용자에 대해 각 사용자의 친구 수를 집계2. 친구 수가 많은 사용자부터 출력하며, 만약 친구 수가 같은 사용자가 여럿이라면 그 사이에서는 사용자 ID가 작은 사용자가 먼저 출력 SolutionWITH friends_count AS ( SELECT user_id, COUNT(CASE WHEN user_id = e.user_a_id THEN 1 END) + COUNT(CASE WHEN user_id = e.user_b_id THEN 1 END) AS num_friends FROM users u LEFT JOIN edges e ON u.user_id = e.user_a_id OR u.user_i..

세션 유지 시간을 10분으로 재정의하기  ✔️0214 복습문제 조건1. 세션을 종료하는 기존의 기준(30분 이상)을 사용자가 10분 이상 행동하지 않을 때로 수정2. 사용자 'a8Xu9GO6TB’의 세션을 재정의해 세션 ID를 계산하는 쿼리를 작성 3. 재정의한 세션 ID는 1부터 시작해 세션 시작 시간이 빠른 순서대로 1씩 증가하는 자연수일 것4. 이벤트 발생 시각이 빠른 순서대로 정렬  SolutionWITH step1 AS ( SELECT user_pseudo_id , event_timestamp_kst , LAG(event_timestamp_kst, 1) OVER (ORDER BY event_timestamp_kst) AS last_event -- 이후에 전체 유저에 확장시..