Growing Business Analyst

1️⃣ Contest Leaderboard Q1. https://www.hackerrank.com/challenges/contest-leaderboard/problem Contest Leaderboard | HackerRank Generate the contest leaderboard. www.hackerrank.com 다시한번 느끼지만 문제를 이해하고 정의하는 능력이 중요함!! 콘테스트 리더보드 관련 문제다. 해커id, 이름이 저장된 Hackers 테이블이 있고 Submission_id, hacker_id, challenge_id, score가 저장된 submissions 테이블이 있다. 두개 테이블 조인해서 무언가 해야하는 문제라는걸 알 수 있다. 제시된 조건은 해커, 챌린지별 Max Score 값을..

1️⃣ Percent of Sessions with Search Runs and Autocompletes Q1. https://app.mode.com/benn/reports/c1039e799f77/runs/21a44c0d302d/embed https://app.mode.com/benn/reports/c1039e799f77/runs/21a44c0d302d/embed Report not found There was a problem finding this report. app.mode.com # 쿼리 한 줄씩 해석해보기 # bounds 라는 서브쿼리 생성 > # last_event : user_id로 구분 & 발생순으로 주어질 때, 해당 로그의 이전 이벤트 발생시간을 현 발생시간에서 뺀 시간 # next_e..

1️⃣ Weekly Active Users Q1. https://app.mode.com/modeanalytics/reports/cbb8c291ee96/runs/7925c979521e/embed https://app.mode.com/modeanalytics/reports/cbb8c291ee96/runs/7925c979521e/embed Report not found There was a problem finding this report. app.mode.com SELECT date_trunc('week', occurred_at) as occurred_at , count(distinct user_id) as wau FROM tutorial.yammer_events WHERE event_name = 'logi..

1️⃣ 멘토링 짝꿍 리스트 Q1. https://solvesql.com/problems/mentor-mentee-list/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com select e1.employee_id as mentee_id, e1.name as mentee_name, e2.employee_id as mentor_id, e2.name as mentor_name from employees e1, employees e2 where e1.join_date between '2021-09-01' and '2021-12-31' and e2.join_date

1️⃣ 복수 국적 메달 수상한 선수 찾기 Q1. https://solvesql.com/problems/multiple-medalist/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com SELECT a.name FROM records r LEFT JOIN teams t ON r.team_id = t.id LEFT JOIN athletes a ON r.athlete_id = a.id LEFT JOIN games g ON r.game_id = g.id WHERE g.year >= 2000 AND r.medal IS NOT NULL GROUP BY athlete_id HAVING COUNT(distinct team) > 1 2️⃣ 있었는데요 없었습니다 Q2...

1️⃣ 지역별 주문의 특징 Q1. https://solvesql.com/problems/characteristics-of-orders/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com select Region , sum(case when category = 'Furniture' then orders end) as Furniture , sum(case when category = 'Office Supplies' then orders end) as 'Office Supplies' , sum(case when category = 'Technology' then orders end) as Technology FROM (select region AS 'Regi..

1️⃣ 일별 블로그 방문자 수 집계 Q1. https://solvesql.com/problems/blog-counter/ solvesql © Copyright 2021-2022 solvesql.com solvesql.com SELECT event_date_kst AS dt, count(distinct user_pseudo_id) AS users FROM ga WHERE event_date_kst BETWEEN '2021-08-02' AND '2021-08-09' GROUP BY event_date_kst -- HAVING event_date_kst>='2021-08-02' AND event_date_kst ='2021-08-02' AND event_date_kst

1️⃣ 중성화 여부 파악하기 Q1. https://programmers.co.kr/learn/courses/30/lessons/59409 코딩테스트 연습 - 중성화 여부 파악하기 ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디 programmers.co.kr SELECT ANIMAL_ID , NAME , CASE WHEN SEX_UPON_INTAKE IN ('Neutered Male','Spayed Female') THEN 'O' ELSE 'X' END AS '중성화'..

1️⃣ 첫 주문과 마지막 주문(Aggregation) Q1. https://solvesql.com/problems/first-and-last-orders/ solvesql solvesql.com SELECT date(MIN(order_purchase_timestamp)) AS first_order_date, date(MAX(order_purchase_timestamp)) AS last_order_date FROM olist_orders_dataset 2️⃣ 고양이와 개는 몇 마리 있을까? Q2. https://programmers.co.kr/learn/courses/30/lessons/59040 코딩테스트 연습 - 고양이와 개는 몇 마리 있을까 ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정..

SQL Basic Cheat Sheet 스터디 기록 UNION, UNION ALL