구름 아카이브

https://leetcode.com/problems/department-top-three-salaries/ -- RANK(), DENSE_RANK() SELECT t.department , t.employee , t.salary FROM( SELECT department.name AS dapartment , employee.name AS employee , employee.salary , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr FROM Employee INNER JOIN dapartment ON employee.departmentId = dapartment.id ) t WHERE t.dr

https://leetcode.com/problems/department-highest-salary/ *** SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음 SELECT department.name AS Department , sub.name AS Employee , sub.max_salary AS Salary FROM( SELECT id , name , salary , departmentid , MAX(salary) OVER (PARTITION BY departmentId) AS max_salary FROM employee -- WHERE salary = max_salary >>> SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음 ) as sub INNER JOIN D..

https://leetcode.com/problems/consecutive-numbers/ -- Use Window function -- *LEAD, *LAG SELECT DISTINCT l.NUM AS ConsecutiveNums SELECT NUM , LEAD(NUM,1) OVER (ORDER BY id) AS next , LEAD(NUM,2) OVER (ORDER BY id ) AS afternext FROM logs )l WHERE l.Num=next AND l.next=l.afternext

https://www.hackerrank.com/challenges/what-type-of-triangle/problem SELECT CASE WHEN A=B AND B=C THEN 'Equilateral' WHEN A+B

Symmetric Pairs - Medium https://www.hackerrank.com/challenges/symmetric-pairs/problem SELECT X, YG FROM Functions WHERE X=Y GROUP BY X,Y HAVING COUNT(*)>=2 UNION SELECT s1.X, s1.Y FROM functions AS s1 INNER JOIN functions AS s2 ON s1.X = s2.Y AND s1.Y = s2.X WHERE s1.X < s1.Y ORDER BY X

African Cities https://www.hackerrank.com/challenges/african-cities/problem SELECT city.name FROM city INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE WHERE continent = 'Africa' Population Census https://www.hackerrank.com/challenges/asian-population/problem SELECT SUM(city.population) FROM city INNER JOIN country ON city.CountryCode = Country.Code WHERE CONTINENT = 'Asia' New Companies - ..

* CEIL - 소수점 첫째 자리에서 올림 * FLOOR - 소수점 첫째 자리에서 내림 * ROUND - 숫자를 각 자리에서 반올림 * TRUNCATE(숫자, 버릴 자릿수) - 숫자를 버릴 자릿수 아래로 버림 * ABS - 절대값 * POWER - 제곱 * SQRT - 양수 x 에 대한 제곱근을 반환 Average Population https://www.hackerrank.com/challenges/average-population/problem SELECT Floor(AVG(population)) FROM CITY Weather Observation Station 2 https://www.hackerrank.com/challenges/weather-observation-station-2/submiss..

Revising Aggregations - Averages https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem SELECT AVG(population) FROM CITY WHERE District = "California" Revising Aggregations - The Sum Function https://www.hackerrank.com/challenges/revising-aggregations-sum/problem SELECT SUM(population) FROM CITY WHERE District = "California" Revising Aggregations - The Count Fun..

https://www.hackerrank.com/challenges/the-report/problem?h_r=internal-search - SELECT 구문에서 CASE 활용해서 구분점 만들기 - 정렬기준 여러개 - INNER 조인 조건에 BETWEEN 사용할 수 있음.hacker_id SELECT CASE WHEN g.grade

https://www.hackerrank.com/challenges/challenges/submissions/database/331359810 SELECT h.hacker_id , h.name , count(*) Challenges_created FROM Challenges c INNER JOIN Hackers h ON h.hacker_id = c.hacker_id GROUP BY h.hacker_id, h.name HAVING Challenges_created = (SELECT MAX(Challenges_created) --Challeges_created MAX 값이 50인 것 FROM( SELECT hacker_id , count(*) Challenges_created FROM Challenges G..