Tech for good

select salaryfrom ( select salary, row_number() over (order by salary desc) as rnk from worker) ctewhere rnk = 5;윈도우 함수ROW_NUMBER()결과 집합의 파티션 내 각 행에 순차적인 정수를 할당하는 순위 함수(윈도우 함수)행의 번호는 각 파티션에 대해 1번부터 할당한다.정렬의 중복 값이 있어도 서로 다른 정수를 할당한다.# 예시SELECT first_name, last_name, city, ROW_NUMBER() OVER ( PARTITION BY city ORDER BY first_name..

https://platform.stratascratch.com/coding/9854-find-the-5-highest-salaries?code_type=3  # 풀이1select department, worker_id, salaryfrom (select *, rank() over(partition by department order by salary desc) as rnk from worker) as ctewhere rnk = 1;# 풀이2select worker_id, department, salaryfrom( select *, max(salary) over(partition by department) as max_dep from worker) as ctewhere salary = max_de..

https://platform.stratascratch.com/coding/9853-find-the-top-5-highest-salaries?code_type=3 select worker_id, department, salaryfrom (select *, rank() over(order by salary desc) as rnk from worker) ctewhere rnk 순위함수rank() over중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하도록 하는 함수.dense_rank() over중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도..

select country, count(company) as n_companiesfrom forbes_global_2010_2014group by countryorder by n_companies desclimit 1;

with cte as ( select feature_id, user_id, max(step_reached) as max_step from facebook_product_features_realizations group by feature_id, user_id)select f.feature_id, ifnull(avg(cte.max_step/f.n_steps)*100, 0) avg_percentagefrom facebook_product_features fleft join cte on f.feature_id = cte.feature_idgroup by f.feature_id;

# Solution 1SELECT COUNT(*) AS n_approvedFROM facebook_friendship_requestsWHERE MONTH(date_approved) IN(1,2);# Solution 2SELECT COUNT(*) AS n_approvedFROM facebook_friendship_requestsWHERE date_approved IS NOT NULLAND DATE_FORMAT(date_approved, '%Y-%m') IN ('2019-01', '2019-02');

with cte as ( select day, user1 from facebook_user_interactions union all select day, user2 as user1 from facebook_user_interactions) select day, count(*)/2, count(distinct user1)from ctegroup by day;

# Soltuion 1SELECT ROUND( (SELECT COUNT(*) FROM facebook_messages_received) / (SELECT COUNT(*) FROM facebook_messages_sent), 2 ) AS success_ratio;# Solution 2SELECT (SELECT COUNT(*) FROM facebook.messages.received) / (SELECT COUNT(*) FROM facebook.messages.sent) AS received_to_sent_ratio;