susinlee 님의 블로그

[문제]https://datalemur.com/questions/signup-confirmation-rate [풀이]1. email 테이블을 돌면서 text 테이블의 email_id와 같으면서 action이 Confirmed 인 애들을 1 아니면 null로 변환2. 를 count를 해주면 null이 아닌 id만 카운트3. 전체 count로 나눠주면 ok (정수/정수가 되지않아 1.으로 소수로 만들어줌).  [코드]SELECT ROUND( COUNT( CASE WHEN EXISTS ( SELECT 1 FROM texts t WHERE t.email_id = e.email_id AND t.signup_action = 'Confirmed' ) THEN 1 E..

[문제]https://datalemur.com/questions/completed-trades Robinhood SQL Interview Question | DataLemurRobinhood SQL Interview Question: Write a query to find the cities with the highest completed trades.datalemur.com [풀이]1. 두 테이블을 병합한 후 완료된 주문만 필터링2. 도시별로 그룹화 한뒤 주문 개수를 계산하고 개수를 기준으로 내림차순 정렬3. 상위 3개만 반환 [코드]SELECT city, COUNT(order_id) AS total_ordersFROM trades tJOIN users uON t.user_id = u.user_idWH..

[문제]https://datalemur.com/questions/duplicate-job-listings [풀이]1. company_id, title, description 별로 job_id를 계산한 뒤 2개 이상인 company_id만 추출한다2. 추출한 company_id에서 고유값의 개수를 계산하고 반환한다 [코드]SELECT COUNT(DISTINCT company_id) AS duplocate_companiesFROM( SELECT company_id FROM job_listings GROUP BY company_id, title, description HAVING COUNT(job_id) >= 2) sub

퍼널 분석(Funnel Analysis)퍼널 분석은 사용자가 특정 목표(예: 구매, 회원가입, 결제 등)에 도달하기까지의 경로를 단계별로 분석하는 방법각 단계에서 사용자가 얼마나 이탈(drop-off)하는지 파악하여, 전화율(conversion rate)을 최적화하는데 사용퍼널 분석의 주요 개념퍼널(Funnel, 깔때기)사용자가 목표 행동을 수행하기까지 거치는 여러 단계의 흐름처음에는 많은 사용자가 유입되지만, 단계가 진행될수록 이탈이 발생하여 최종 목표에 도달하는 사요자는 줄어이탈(Drop-off Rate)각 단계에서 탈락한 사용자 비율특정 단계에서 이탈률이 높다면, 문제를 개선해야 함전환율(Conversion Rate)한 단계에서 다음 단계로 이동한 사용자 비율목표를 달성한 사용자 비율을 높이는 것이..

라이브 세션 강의SQL 문풀 및 정리 (4문제)1, 2강 복습 및 정리 (퍼널분석 완벽히 이해하기)취업우리은행 공채 지원서 작성 및 제출최종프로젝트연관분석 공부 및 적용

[문제]https://datalemur.com/questions/user-retention Facebook SQL Interview Question | DataLemurFacebook SQL Interview Question: Find the number of monthly active users (MAUs) in July 202.datalemur.com [풀이]1. 기존 테이블에서 최신 월을 가져온다2. 현재 월에서 event_type이 3개인 유저만 가져온다3. 현재 월을 가져오고 유저 수를 계산해준다  [코드]WITH current_month AS ( SELECT MAX(EXTRACT(MONTH FROM event_date)) AS month FROM user_actions), recent_ac..

[문제]https://datalemur.com/questions/top-fans-rank Spotify SQL Interview Question | DataLemurSpotify SQL Interview Question: Write a query to find top 5 ranked artists.datalemur.com [풀이]1. 테이블을 병합해 준뒤 2. 아티스트 별로 곡의 개수를 세어준다3. 곡의 개수를 기준으로 순위를 매겨준다(랭크 함수 ORDER BY에 집계함수를 사용하여 2, 3번을 한번에 처리해준다)4. 상위 5명만 필터링 해준다 (아티스트 오름차순 정렬하면 오답처리됨 - 문제에 문제있음) [코드]WITH artist_rank AS ( SELECT a.artist_name , ..

[문제]https://datalemur.com/questions/sql-top-three-salaries [풀이]RANK() : 동일한 값이 있을 경우, 같은 순위를 부여하고 다음 순위를 건너뜀 (1, 1, 3, 4 ...)DENSE_RANK() : 동일한 값이 있을 경우 같은 순위를 부여하지만, 다음 순위를 건너뛰지 않음 (1, 1, 2, 3 ...)ROW_NUMBER() : 동일한 값이 있어도 무조건 순차적으로 증가 (고유한 순위) 1. 두 테이블을 병합한 후 랭크함수를 사용하여 부서별로 급여 순으로 랭크를 매겨준다.2. 랭크를 통해 상위 3명만 필터링한다. [코드]SELECT department_name , name , salaryFROM( SELECT e.name , d.depa..