데이터 분석

문제You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day). Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.Return the result table ordered by visited_on in ascending order. 고객이 매일 얼마나 지출했는지에 대한 7일 이동 평균을 계산하는..

문제 Write a solution to find the rank of the scores. The ranking should be calculated according to the following rules:The scores should be ranked from the highest to the lowest.If there is a tie between two scores, both should have the same ranking.After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks. score 값을 기..

문제 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에 해당하는 급여를 받는 직원의 부서명, 이름, 급여를 조회하는 문제이다.단, 같은 급여가 반복되더라도 한 번만 계산해야 한..

문제 Find all numbers that appear at least three times consecutively. 연속 3번 이상 등장하는 num을 조회하는 문제이다. Solution✔️ 쿼리 1 : 셀프조인을 이용하여 해결SELECT DISTINCT l1.num AS ConsecutiveNumsFROM Logs l1JOIN Logs l2 ON l2.id = l1.id + 1JOIN Logs l3 ON l3.id = l2.id + 1WHERE l1.num = l2.num AND l1.num = l3.num ✅ Logs 테이블을 3번 사용하여, id를 기준으로 각각 다음 행과 그다음 행을 조인✅ 현재 행과 다음 행의 num 값이 동일하고, 현재 행과 다다음 행의 num 값도 동일한 경우를 만족하면..

문제  Write a solution to find the first login date for each player. 각 플레이어별 최초 로그인 날짜를 조회하는 문제이다. Solution✔️ 쿼리 1 : GROUP BY를 이용하여 해결SELECT player_id , MIN(event_date) AS first_loginFROM ActivityGROUP BY player_id✔️ 쿼리 2 : MIN()을 활용한 윈도우 함수를 이용하여 해결SELECT DISTINCT player_id , MIN(event_date) OVER(PARTITION BY player_id) AS first_loginFROM Activity  ✔️ 쿼리 3 : ROW_NUMBER()을 활용한 윈도우 함수를 이용하여..

문제 Write a solution to report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive. 2019년 1분기에만 팔린 상품을 조회하는 문제이다. Solution✔️ 쿼리 1SELECT product_id , product_nameFROM ProductWHERE product_id IN ( SELECT product_id FROM Sales GROUP BY product_id HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) ✅ WHERE절에 서브쿼리를 ..

문제  Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.  모든 상품을 구매한 고객을 조회하는 문제이다. SolutionSELECT customer_idFROM CustomerGROUP BY customer_idHAVING COUNT(DISTINCT product_key) = (SELECT COUNT(product_key) FROM Product) ✅ 고객 id별 구매 이력이 있는 고유 상품 개수를 집계하여, 이것이 Product 테이블의 상품 개수와 동일한 경우를 만족하는 고객을 반환한다.

문제 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. 전체 유저 중 최초 접속한 날을 기준으로 이틀 연속 접속한 플레이어의 비율을 계산하..