일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 | 31 |
- 코딩
- 니다
- 얼음여왕
- 벚꽃개화시기
- 코오롱베네트
- GA
- 구글애널리틱스
- Google Analytics
- 채용공고
- Python
- 벚꽃
- 데이터문해력
- 감사인사
- ㅂㅂ
- 데이터 분석
- 수명예측 인공지능
- 알파줄거리
- GA4
- 6시 기상
- 기사스크랩
- 독서
- 미라클 모닝
- 프로그래머스
- 티스토리
- 구글애널리틱스4
- 명상
- 영화 올드 줄거리
- Today
- Total
목록데이터 분석 직무/Coding Test (96)
Data Analyst KIM
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/cASAfO/btsAzcqiBv7/7KCtaRPVgiMSmsfUrkZPsk/img.png)
LeetCode - The World's Leading Online Programming Learning Platform Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 이 문제는 DENSE_RANK를 사용하면 되는 간단한 문제였음 + AS 이후 'Rank'처럼 따옴표를 사용해주는 것이 포인트 SELECT Score , DENSE_RANK() OVER (ORDER BY Score DESC) AS 'Rank' FROM Scores
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/bk0ekk/btsAaYAQXRw/P5pSjgNKb1rSZlZ5NT6eMk/img.png)
1. 문제 설명 2. 문제 3. 예시 3. 내 코드 - SUM(Weight) OVER(ORDER BY Turn)을 통해서 누적 합 계산 - 조건절과 LIMIT를 이용하여 1개의 값을 출력 WITH t AS ( SELECT Turn , person_id , person_name , Weight , SUM(Weight) OVER(ORDER BY Turn) AS total FROM Queue ORDER BY Turn desc ) SELECT person_name FROM t WHERE total
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/qbiiC/btsz2nAsDtV/HKmNkRUS6oAhSrWsdArAw0/img.png)
문제 풀이 1. 부모 노드가 없는 경우 => Root 2. 부모노드가 있고, 자신이 부모노드인 경우 => Inner 3. 나머지 => Leaf SELECT id , (CASE WHEN p_id IS NULL THEN 'Root' WHEN id IN (SELECT p_id FROM Tree) THEN 'Inner' ELSE 'Leaf' END) AS 'type' FROM Tree ORDER BY id
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/luFS6/btsz6ODhwO6/86skmlbU8X2kTLRxinxAg0/img.png)
설명 문제 예시 Buy이면 -price , Sell이면 price를 집계 SELECT stock_name , SUM(CASE WHEN operation = 'Buy' THEN -price ELSE price END) AS capital_gain_loss FROM Stocks GROUP BY stock_name
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/dEiOdm/btszC6FQHmv/klWjKbC4aZ7J2CuTtgTSQ1/img.png)
WITH tab AS ( SELECT car.daily_fee , car.car_type , his.history_id , DATEDIFF(END_DATE, START_DATE) + 1 AS period , CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상' WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상' ELSE 'NONE' END AS duration_type FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS his INNER JOIN CAR_RENTA..
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/cOJmzT/btszHGy6Q6e/7ZnnlPt8CHfekrAoMkjP2k/img.png)
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE WHERE SALES_DATE >= '2022-03-01' and SALES_DATE = '2022-03-01' and SALES_DATE < '2022-04-01' ORDER BY SALES_DATE , PRODUCT_ID , USER_ID