본문 바로가기

DATA/SQL

 (7)
[SQL 풀이] DataLemur - User's Third Transaction 모든 유저의 세 번째 거래에 대한 정보를 출력하는 쿼리를 만드는 문제 RANK함수를 이용해서 user_id 내 순위를 구하는 쿼리를 만든다. SELECT user_id, spend, transaction_dateFROM (SELECT user_id, spend, transaction_date, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY transaction_date) AS row_num FROM transactions) AS trans_numWHERE row_num = 3; 1. RANK: 동일한 숫자가 있으면 제외하고 출력(1, 1, 3) 2. DENSE_RANK: 동일한 숫자가 ..
SQL을 활용한 RFM 분석 📌 RFM분석 정의고객들의 '구매 패턴'을 파악하여 사용자 별로 얼마나 최근에, 자주, 많은 금액을 지출했는지에 따라 사용자들의 분포를 확인하거나 사용자 그룹을 나누어 분류하는 분석 기법Recency: 얼마나 최근에 구매했는지이탈 기준을 세울 때 사용하는 지표Frequency: 얼마나 자주 구매했는지Monetary: 얼마나 많은 금액을 구매했는지전체 매출액에서 고객 집단이 차지하는 비중을 보아야 함. 전체 매출의 대부분을 차지하는 특정 고객층, 중간 고객층, 가장 낮은 금액을 구매하는 층  이탈 기준 정하기유저의 접속 세션 데이터를 이용하여 이탈(churn) 기준을 잘 정하는 방법boxnwhis.kr  📌 RFM 분석 기법의 프로세스  📌 SQL에서 RFM을 구하는 쿼리 예시- Case when 구..
SQL: Leet code 문제풀이(570, 1934, 620) 570. Managers with at least 5 direct reports https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/ #구할 값: name # 조건절에서 managerId가 5번 이상 언급되는 값을 찾아서 집계해준다.select namefrom Employeewhere id in (select managerId from Employee group by managerId having count(*) >= 5)  1934. Confirmation Ratehttps://leetcode.com/problems/confirmation-rate/description/ 각 사용자의 확..
SQL풀이: Leetcode 1661. Average Time of Process per Machine https://leetcode.com/problems/average-time-of-process-per-machine/ 문제machine_id와 기계 종류별로 처리 평균 시간을 구하면 되는 문제로 평균 시간은 소수 3째자리에서 반올림해주어야 한다. 방법1 select machine_id, round(sum(case when activity_type = 'start' then - timestamp else timestamp end) / count(distinct process_id), 3) as processing_timefrom Activitygroup by machine_id; 방법2테이블을 두 개로 분리해서 푸는 방법SELECT a.machine_id, ROUND(AVG(b...
SQL: LAG 함수 활용하여 현재 데이터와 이전 데이터를 비교 LAG 함수 정의 SELECT LAG([대상 컬럼], [이전 offset], [기본값]) OVER (PARTITION BY [..] ORDER BY [..]) LAG함수를 이용하면, 이전 행 값과 다음 행의 값을 비교할 수 있다.   예시 문제: 이전 일과 비교했을 때, 온도가 더 높았던 날짜를 집계하기with tmp as (select id ,RecordDate,Temperature,LAG (Temperature,1) OVER (ORDER BY RecordDate) AS prev_Temperature,LAG (RecordDate,1) OVER (ORDER BY RecordDate) AS prev_RecordDatefrom Weather)select id from tmp where Temperature>..
241211 SQL 코드카타(조건에 맞는 사용자 정보 조회하기, 조건에 부합하는 중고거래 상태 조회하기, 취소되지 않은 진료 예약 조회하기) 1. 조건에 맞는 사용자 정보 조회하기 🅰️풀이과정# SELECT: USER_ID, NICKNAMECONCAT 함수로 전체주소의 형식을 만들어준다. (CITY, STREET_ADDRESS1,2를 붙여줌) 중간에 " "공백을 삽입해주어 가독성을 높인다. CONCAT과 SUBSTRING함수로 전화번호 형식을 만들어준다.  1~3까지 절단 후 하이픈(-) 삽입, 4번째에서 4글자 불러오고 하이픈 삽입. 마지막 자리 모두 불러오기 #FROM절: JOIN함수로 USER_ID와 WRITER_ID를 연결한다.  #GROUP BY: USER_ID기준으로 정렬. 같은 사람이 몇 번 거래했는지 알아야 하므로 #HAVING 절로 WRITER_ID가 세번 언급된 사람을 찾는다. SELECT U.USER_ID, ..
SQL 코드카타 풀이(없어진 기록 찾기, 과일로 만든 아이스크림 고르기, 재구매가 일어난 상품과 회원 구하기) 1. 없어진 기록찾기 ❓문제#입양을 간 기록은 있는데 보호소에 돌아온 기록이 없는 동물의 ID와 이름을 조회하라.  🅰️풀이방법# 두 테이블을 비교하여 없는 값을 찾아내고자 함. # LEFT JOIN 두 테이블을 비교했을 때, 왼쪽에 있는 데이터는 무조건 가져오지만, 오른쪽에 오는 테이블은 조건에 맞는 데이터가 없을 시에 NULL로 표시한다. ❗참고로, LEFT OUTER JOIN과 LEFT JOIN은 동일 / JOIN과 INNER JOIN도 동일 #입양을 간 기록은 존재한다고 했으므로, ANIMAL_OUTS 데이터를 왼쪽에 배치하여 모든 값이 무조건 나오게 해야 한다.SELECT O.ANIMAL_ID, O.NAMEFROM ANIMAL_OUTS AS O LEFT JOIN ANIMAL_..

티스토리툴바