hyezdata 님의 블로그

# 성별 남여 서로 바꾸기# select문 작성x update문으로만update salaryset sex= case when sex='m' then 'f' else 'm' end update문 오랜만에 사용해서 다시 찾아봄UPDATE 테이블_이름SET 열_이름1 = 변경할_값1, 열_이름2 = 변경할_값2WHERE 조건; Swap Sex of Employees - LeetCodeCan you solve this real interview question? Swap Sex of Employees - Table: Salary +-------------+----------+ | Column Name | Type | +-------------+----------+ | i..

# 연속된 id 둘씩 순서 바꾸기# 햑생 수 홀수면 마지막 학생은 바꾸기x# id 오름차순select case when id%2=0 then id-1 when (id%2!=0 and id=(select max(id) from seat)) then id when id%2!=0 then id+1 end id, studentfrom seatorder by id 댓글에 적어놓은 힌트 참고 함1] You can use a CASE statement to handle swapping between adjacent seats. The key isto check whether the id is odd or even:2] If the id is odd, s..

# 한번만 나타나는 숫자 중 가장 큰 숫자 # 만약 없다면 nullselect max(num) as numfrom (select num from mynumbers group by num having count(num)=1) as single_num Biggest Single Number - LeetCodeCan you solve this real interview question? Biggest Single Number - Table: MyNumbers +-------------+------+ | Column Name | Type | +-------------+------+ | num | int | +-------------+------+ This table may contain dup..

# 삼각형을 만드는 여부select x, y, z, case when (x + y > z) and (x + z > y) and (y + z > x) then 'Yes' else 'No' end as 'triangle'from triangle Triangle Judgement - LeetCodeCan you solve this real interview question? Triangle Judgement - Table: Triangle +-------------+------+ | Column Name | Type | +-------------+------+ | x | int | | y | int | | z | int | +-------------+------+ In SQL, (..

# 'red'라는 회사에서 어떤 주문도 하지 않은 salesperson의 이름select namefrom salespersonwhere sales_id not in ( select o.sales_id from company c join orders o on c.com_id=o.com_id where c.name='RED') Sales Person - LeetCodeCan you solve this real interview question? Sales Person - Table: SalesPerson +-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_..

# 가장 많이 주문한 customer_number 찾기select customer_numberfrom ordersgroup by customer_numberorder by count(*) desclimit 1 Customer Placing the Largest Number of Orders - LeetCodeCan you solve this real interview question? Customer Placing the Largest Number of Orders - Table: Orders +-----------------+----------+ | Column Name | Type | +-----------------+----------+ | order_number | int | | custome..

# 처음 로그인 한 다음날 또 로그인 한 player/전체 플레이어# 소수점 둘째 자리select round(count(distinct a1.player_id) /(select count(distinct player_id) from activity), 2) as fractionfrom activity a1 join (select player_id, min(event_date) as first_login from activity group by player_id) a2 on a1.player_id=a2.player_id and datediff(event_date, first_login) = 1 이 문제의 핵심은플레이어의 첫 로그인 날짜 다음날에도 접속했는지 그..

# 취소율=차단x 사용자의 취소 요청 수/차단x 사용자 전체 요청 (소수두번째)# 차단x 사용자 최소 한번 이상 운행 (2013-10-01~2013-10-03 사이)select request_at as Day, round(sum(status like 'cancelled_by_%')/count(*), 2) as 'Cancellation Rate'from tripswhere client_id in (select distinct users_id from users where banned='No') and driver_id in (select distinct users_id from users where banned='No') and request_at between '2013-10-01' an..

# 처음으로 로그인한 날짜select player_id, min(event_date) as first_loginfrom activitygroup by player_id

# 전날에 비해 높은 온도인 날 id 출력select w1.id as Idfrom weather w1 join weather w2 on datediff(w1.recordDate, w2.recordDate) = 1where w1.temperature > w2.temperature 첫번째 답# 전날에 비해 높은 온도인 날 id 출력select id as Idfrom ( select id, temperature, lag(temperature, 1) over (order by recordDate) as before_temp from Weather) awhere temperature >= before_temp -> 이전 행이 반드시 전날일거라는 가능성 없을 수도 있음-> 그래서 lag..