Deep Dive 🌊

문제https://leetcode.com/problems/game-play-analysis-iv/ 코드내 답안# Write your MySQL query statement belowwith lag_date as (select *, min(event_date) over (partition by player_id) as first_datefrom activity)select round(count(distinct player_id) / (select count(distinct player_id) from activity), 2) as fractionfrom lag_datewhere datediff(event_date, first_date) = 1-- 10분 타 블로그 답안SELECT ROUN..

문제https://leetcode.com/problems/human-traffic-of-stadium/description/  WITH result AS ( SELECT id, visit_date, people, LAG(people) OVER (ORDER BY id) AS prev_people FROM stadium),session_marker AS ( SELECT id, visit_date, people, (CASE WHEN prev_people IS NULL THEN 1 WHEN prev_people = 3)ORDER BY session_gr..

문제https://school.programmers.co.kr/learn/courses/30/lessons/131534 프로그래머스SW개발자를 위한 평가, 교육, 채용까지 Total Solution을 제공하는 개발자 성장을 위한 베이스캠프programmers.co.kr 코드select year(sales_date) as year, month(sales_date) as month, count(distinct user_id) as purchased_users, round(count(distinct user_id) / (select count(*) from user_info where year(joined) = 2021), 1) as purchased_ratiofrom online_sal..

문제https://leetcode.com/problems/department-top-three-salaries/description/ 코드# Write your MySQL query statement belowwith em_rank as (select name as employee, departmentId, salary, dense_rank() over (partition by departmentId order by salary desc) as rankingfrom employee)select d.name as Department, e.employee as Employee, e.salary as Salaryfrom em_rank ejoin department don e.depart..

문제https://leetcode.com/problems/trips-and-users/ 코드 답안with result as (select id, client_id, driver_id, status, request_atfrom trips tjoin (select users_id, banned from users where role = 'client') con t.client_id = c.users_idjoin (select users_id, banned from users where role = 'driver') don t.driver_id = d.users_idwhere c.banned = 'No' and d.banned = 'No' ),request_rate as (select request_at as..

문제https://school.programmers.co.kr/learn/courses/30/lessons/293261 프로그래머스SW개발자를 위한 평가, 교육, 채용까지 Total Solution을 제공하는 개발자 성장을 위한 베이스캠프programmers.co.kr 코드with rank_info as ( select id, fish_type, length, rank() over (partition by fish_type order by length desc) as ranking from fish_info)select r.id, n.fish_name, lengthfrom rank_info rjoin fish_name_info non r.fish_type = n.fish_typew..

문제https://solvesql.com/problems/redefine-session/ https://solvesql.com/problems/redefine-session/ solvesql.com 이번 문제는 내용조차 이해가 안됐다event_name이 session_start인 행동의 시간이랑 (+ 1시간)인 시간을 구하면 되는 거 아닌가?생각했는데해설을 보니까 완전 다르게 풀어야 하는 문제였다. event별로 시간차가 1시간 이상인 경우 새로운 세션인 걸로 정의해서 각 세션의 시작과 끝을 구하는 거였다.event_name이 session_start인 거는 실제 세션과는 상관이 없다. 문제에서 요구하는 걸 이해했어도 어떻게 풀어야할지 감도 안왔을 것 같다.레코드별 시간차를 구하는 문제가 많고 실제로 내..

문제https://solvesql.com/problems/revenue-pct-per-category/ https://solvesql.com/problems/revenue-pct-per-category/ solvesql.com 코드gpt를 조져서 얻어낸 답안with all_sale as ( -- 서브 카테고리 별로 매출액을 구한다 select category, sub_category, sum(sales) as sales_sub_category from records group by category, sub_category),sales_with_totals as ( -- 서브 카테고리, 카테고리, 총 매출액을 구한다. select category, sub_categ..

문제https://solvesql.com/problems/day-of-furniture/ https://solvesql.com/problems/day-of-furniture/ solvesql.com 코드 전문1차 시도with calc as ( SELECT order_date, count(*) AS total_orders, count( CASE WHEN category = 'Furniture' THEN 1 ELSE 0 END ) AS furniture_orders FROM records GROUP BY order_date )select order_date, furniture..

📌 Case문 여러 조건을 한번에 처리가 가능한 '다중 분기' 문법이다 스토어드 프로시저에서 SQL 프로그래밍문에서 활용 SELECT 문에서 CASE문으로 값 호출 CASE WHEN 조건1 THEN SET 변수값 WHEN 조건1 THEN SET 변수값 WHEN 조건1 THEN SET 변수값 ELSE SET 변수값 END CASE; ✏️ SELECT문에서 CASE문 활용해서 회원등급 분류하기 SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액", CASE WHEN (SUM(price*amount) >= 1500) THEN '최우수고객' WHEN (SUM(price*amount) >= 1000) THEN '우수고객' WHEN (SUM(price*amount) >= ..