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;

re.sub(pattern, repl, string, count, flags) re.sub -> '패턴에 일치되는 문자열은 대체 문자열로 바꿔준다.' pattern = 패턴 repl = 대체될 문자열 string = 문자열 데이터 count = 최대 몇 개까지 치환할 것인가를 지정 * (만약 일치되는 문자열이 3인데 count=2라고 지정되어 있으면 마지막 세 번째 문자열은 치환되지 않는다.) flags = (아래 블로그 포스팅 표 참조) https://greeksharifa.github.io/%EC%A0%95%EA%B7%9C%ED%91%9C%ED%98%84%EC%8B%9D(re)/2018/07/21/regex-usage-02-basic/ Python, Machine & Deep Learning Pyth..

iteritems(): returns an iterator of the dictionary's list in the form of (key, value) tuple pairs -> 딕셔너리로 부를 수 있는 함수 / 키 값과, 밸류 값의 리스트를 부른다. 아래의 형태로 많이 쓰인다. for k, v in dic.iteritems(): ...