선선

사용자 정의 함수: https://www.mysqltutorial.org/mysql-stored-function/ if statement: https://www.mysqltutorial.org/mysql-if-statement/ case vs if statement vs if function 차이 참고: https://stackoverflow.com/questions/30047983/mysql-case-vs-if-statement-vs-if-function 조건이 여러개이고 순차적이라면 case 쓰기! (if function은 standard sql이 아니라서 postgresql에는 적용 안된다.) 177. Nth Highest Salary https://leetcode.com/problems/nth-h..

튜토리얼: https://regexone.com/lesson/introduction_abcs 테스트 할 수 있는 곳: https://regexr.com Weather Observation Station 6 https://www.hackerrank.com/challenges/weather-observation-station-6/problem?isFullScreen=true /* select distinct city from satation where city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%' */ select distinct city from station where city regexp '^[..

group by와 다른 점: group by: 그룹들을 collapse하지 않고(한 줄 요약하지 않고) window function: 각각의 row에 결과물을 보여준다. https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html 누적 합 구하기: 1. sum(컬럼) over (order by 컬럼) as cumsum 2. sum(컬럼) over (partition by 컬럼 order by 컬럼) as cumsum_with_partition 3. self-join 활용하기 4. select sub-query 사용하기 순위 정하기: row_number(), rank(), dense_rank() 데이터 위치 바꾸기: lag(): 데이터 밀기 le..

The Report [Hackerrank] https://www.hackerrank.com/challenges/the-report/problem JOIN 조건에 BETWEEN을 쓸 수 있다. select case when g.grade < 8 then null else s.name end as name , g.grade , s.marks from students as s inner join grades as g on s.marks between g.min_mark and g.max_mark order by g.grade desc, s.name asc, s.marks asc 180. Consecutive-Number https://leetcode.com/problems/consecutive-numbers/..

https://leetcode.com/problems/delete-duplicate-emails/ Delete Duplicate Emails - LeetCode 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 delete from Person where id not in ( select sub.min_id from ( select email, min(id) as min_id from Person group by email )sub) delete p1 from Person p1 i..

https://leetcode.com/problems/swap-salary/ Swap Salary - LeetCode 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 update 한번만 쓰기 update salary set sex = case when sex='f' then 'm' else 'f' end

https://www.hackerrank.com/challenges/the-company/problem New Companies | HackerRank Find total number of employees. www.hackerrank.com 포인트: 여러 join문으로 연결하기 select C.company_code, C.founder, count(distinct(LM.lead_manager_code)), count(distinct(SM.senior_manager_code)), count(distinct(M.manager_code)), count(distinct(E.employee_code)) from Company C left join Lead_Manager LM on C.company_code = ..

595. Big Countries select name, population, area from World where area>=3000000 or population>=25000000 596. Classes More Than 5 Students select class from Courses group by class having count(*)>=5

570. Managers with at Least 5 Direct Reports select a.name from Employee a join Employee b on a.id = b.managerId group by a.id having count(*)>=5 571. 574. Winning Candidate select A.name from( select name, count(*) as cnt from Vote join Candidate on Vote.candidateId = Candidate.id group by Vote.candidateId) A order by A.cnt desc limit 1 577. Employee Bonus select name, bonus from Employee left ..

262. Trips and Users select request_at as Day, round(count(if(status like 'cancelled_%', status, null)) / count(*), 2) as 'Cancellation Rate' from Trips where client_id in ( select users_id from Users where banned = "No" ) and driver_id in ( select users_id from Users where banned = "No" ) and request_at between "2013-10-01" and "2013-10-03" group by request_at (풀었지만, 검색없이 풀어보기) 511. Game Play A..