Tech for good

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중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도..

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;