Oracle11 leetcode 178. Rank Scores 1 2 3 4 5 /* Write your PL/SQL query statement below */ SELECT score , DENSE_RANK() OVER (ORDER BY score DESC ) as rank FROM Scores; Colored by Color Scripter cs 이게 리트코드 Mysql은 구버전이라 그런지 rank() 함수가 없어서 오라클로 돌림. 최신버전 Mysql 도 Rank 함수 있습니다~~ 2022. 4. 2. leetcode 1581. Customer Who Visited but Did Not Make Any Transactions 1 2 3 4 5 6 7 8 /* Write your PL/SQL query statement below */ select customer_id, count(visit_id) as count_no_trans from visits where visit_id not in ( select visit_id from transactions ) group by customer_id Colored by Color Scripter cs 2022. 3. 30. leetcode 1795. Rearrange Products Table 1 2 3 4 5 6 7 8 9 /* Write your PL/SQL query statement below */ select * from( select product_id, 'store1' store, store1 price from products union select product_id, 'store2' store, store2 price from products union select product_id, 'store3' store, store3 price from products ) where price is not null; Colored by Color Scripter cs 2022. 3. 30. leetcode 1873. Calculate Special Bonus 1 2 3 4 5 /* Write your PL/SQL query statement below */ select employee_id ,case when mod(employee_id, 2)=1 and substr(name,1,1)!='M' then salary else 0 end as bonus from employees; Colored by Color Scripter cs 2022. 3. 29. leetcode 1741. Find Total Time Spent by Each Employee 1 2 3 4 5 /* Write your PL/SQL query statement below */ select TO_CHAR(event_day, 'YYYY-MM-DD') as day, emp_id, sum(out_time - in_time) as total_time from Employees group by event_day, emp_id order by 1, 2, 3 Colored by Color Scripter cs 2022. 3. 29. leetcode 1757. Recyclable and Low Fat Products 1 2 3 4 5 6 /* Write your PL/SQL query statement below */ select p.product_id from products p where 1=1 and p.low_fats='Y' and p.recyclable='Y' cs 2022. 3. 29. hacker rank The Report 1 2 3 4 5 6 7 8 select case when g.grade >=8 then s.name else null end, g.grade, s.marks from students s join grades g on s.marks between g.min_mark and g.max_mark where s.marks > 8 order by g.grade desc, s.name, s.marks; cs 2022. 3. 29. Hacker Rank Top Competitors 1 2 3 4 5 6 7 8 9 10 SELECT H.hacker_id, H.name FROM Submissions S INNER JOIN Challenges C ON S.challenge_id = C.challenge_id INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level INNER JOIN Hackers H ON S.hacker_id = H.hacker_id WHERE S.score = D.score AND C.difficulty_level = D.difficulty_level GROUP BY H.hacker_id, H.name HAVING COUNT(H.hacker_id) > 1 ORDER BY COUNT(H.hacker_id) .. 2022. 3. 29. hacker rank Weather Observation Station 19 1 2 3 4 5 6 /* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. */ select round(sqrt(power(min(LAT_N)-max(LAT_N), 2)+power(min(LONG_W)-max(LONG_W), 2)), 4) from station Colored by Color Scripter cs 2022. 3. 29. Hackerrank Weather Observation Station 18 1 2 3 4 5 6 /* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. */ select round((max(lat_n)-min(lat_n))+(max(long_w)-min(long_w)),4) from station Colored by Color Scripter cs 2022. 3. 29. leetcode 595. Big Countries 1 2 3 4 5 /* Write your PL/SQL query statement below */ select name, population, area from world where area>=3000000 or population>=25000000 cs 2022. 3. 17. 이전 1 다음
