데이터 분석

문제 Write a solution to find the people who have the most friends and the most friends number.The test cases are generated so that only one person has the most friends. 친구가 가장 많은 사람을 찾는 문제이다.단, 친구가 가장 많은 사람은 1명이다. Solution✔️ 쿼리 1WITH preprocessing AS (SELECT requester_id AS id , COUNT(*) AS numFROM RequestAcceptedGROUP BY requester_idUNION ALLSELECT accepter_id , COUNT(*) FROM RequestAcc..

문제  Write a solution to find managers with at least five direct reports.  5명 이상의 직속 부하 직원이 있는 관리자를 추출하는 문제이다. SolutuionSELECT nameFROM EmployeeWHERE id IN ( SELECT e.managerId FROM Employee e GROUP BY e.managerId HAVING COUNT(*) >= 5)

문제 Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players. 전체 유저 중 최초 접속한 날을 기준으로 이틀 연속 접속한 플레이어의 비율을 계산하..

문제 Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points. 일별 Cancellation Rate은 '취소 건수 / 요청 건수'와 같이 계산한다.단, client와 driver 모두 banned = 'No' 조건을 만족하는 경우만 고려한다. SolutionSELECT t.request_at AS Day , ROUND(COUNT(CASE ..

문제 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..

세 명이 서로 친구인 관계 찾기✔️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..