데이터 분석

문제 Biologists are studying basic patterns in DNA sequences. Write a solution to identify sample_id with the following patterns:Sequences that start with ATG (a common start codon)Sequences that end with either TAA, TAG, or TGA (stop codons)Sequences containing the motif ATAT (a simple repeated pattern)Sequences that have at least 3 consecutive G (like GGG or GGGG)Return the result table ordered ..

문제 Write a solution to find the students who have shown improvement. A student is considered to have shown improvement if they meet both of these conditions: Have taken exams in the same subject on at least two different dates.Their latest score in that subject is higher than their first score.Return the result table ordered by student_id, subject in ascending order.  다음 조건을 만족하면 성적이 향상된 학생으로 판단..

문제 Write a solution to find all the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times. 3번 이상 협업한 적이 있는 actor_id, director_id 쌍을 조회하는 문제이다. SolutionSELECT actor_id, director_idFROM ActorDirectorGROUP BY actor_id, director_idHAVING COUNT(*) >=3 ✅ (actor_id, director_id) 기준으로 해당 조합이 등장한 횟수를 집계하여, 3 이상인 쌍만 결과에 포함하는 간단한 쿼리! https://hwangbyeongho.t..

문제  Write a solution to find the names of all the salespersons who did not have any orders related to the company with the name "RED".  "RED"라는 이름의 회사와 관련된 주문이 전혀 없는 모든 영업사원의 이름을 조회하는 문제이다. 💡여사건 개념을 떠올린다‼️"RED" 회사와 관련된 주문을 한 영업사원을 제외한 나머지를 조회.   SolutionSELECT nameFROM SalesPersonWHERE sales_id NOT IN ( -- 그렇지 않은 사원 SELECT o.sales_id -- "RED" 회사와 관련된 주문을 한 사원 FROM Orders o LEFT ..

문제Write a solution to find the customer_number for the customer who has placed the largest number of orders.The test cases are generated so that exactly one customer will have placed more orders than any other customer. 가장 많은 주문을 한 고객의 customer_number를 조회하는 문제이다.  SolutionWITH step1 AS ( SELECT customer_number , COUNT(order_number) AS ord_cnt FROM Orders GROUP BY customer_number..

문제 상황 특정 기간 후 이탈률이 높아지는 지점을 식별하여 집중적인 고객 관리 전략을 수립하기 위한 비즈니스 시나리오를 가정하고, 유저들의 첫번째 주문일을 기준으로 코호트를 나눈 후 유저의 첫 번째 주문 이후의 주문 리텐션을 계산해 보았다. 단, 한 유저가 같은 날 여러 건의 주문을 했을 경우 하루에 한 건으로 집계한다. SolutionWITH step1 AS ( SELECT r.* , c.first_order_date , DATEDIFF(r.order_date, c.first_order_date) AS day_diff FROM records r JOIN customer_stats c ON r.customer_id = c.customer_id)SELECT first_orde..

문제 상황 고객별 인기 상품 정보를 바탕으로 교차 판매 및 상향 판매 전략을 수립하기 위한 비즈니스 시나리오를 가정하고, 1분기 동안의 고객별 인기 상품 Top3을 조회해 보았다. 단, 주문 수량이 10/9/9/8였다면 상위 주문 수량 3개는 10/9/8 이므로 총 4개의 상품명을 출력한다. SolutionWITH step1 AS ( SELECT customer_id , product_name , SUM(quantity) AS total_quantity FROM records WHERE order_date BETWEEN '2020-01-01' AND '2020-03-31' GROUP BY customer_id, product_name), step2 AS ( SELECT * ..

문제 상황 solvesql의 플레이그라운드 서비스는 사용자가 원하는 데이터베이스를 선택하여 자유롭게 분석해 볼 수 있는 환경을 제공한다. 이에 나는 매출 기여도가 높은 카테고리를 식별하여 이에 대한 재고 관리를 더욱 철저히 하고자 하는 비즈니스 시나리오를 가정하고, 이를 위해 카테고리별 매출액, 총매출액, 차지하는 비율을 집계해 보았다.  SolutionSELECT DISTINCT category , SUM(sales) OVER (PARTITION BY category) AS sales_category , SUM(sales) OVER () AS sales_total , ROUND(SUM(sales) OVER (PARTITION BY category) / SUM(sales) OVER ..

문제  Write a solution to find for each user, the join date and the number of orders they made as a buyer in 2019. 💡이 문제의 핵심은 JOIN과 LEFT JOIN의 특징을 정확히 알고, 적절한 조인 조건을 활용하는 것이다.  SolutionSELECT u.user_id AS buyer_id , u.join_date , COUNT(CASE WHEN o.order_date LIKE '2019%' THEN o.order_id END) AS orders_in_2019 -- NULL은 COUNT 시 0FROM Users u LEFT JOIN Orders o ON u.user_id = o.buyer_idGROU..

문제 Write a solution to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each. Return the result table ordered by visit_date in ascending order.  'id'가 연속적인 세 개 이상의 행을 찾되, 각 행의 'people'값이 100 이상인 경우에 해당하는 레코드를 출력하는 문제이다. 💡연속적이라는 단서를 보면 self-join을 통해 접근해 보자!  Solution (1) ✔️ Stadium 테이블을 셀프 조인하여 세 개의 연속된 id 확인SELECT *F..