SQL/StrataScratch (15) 썸네일형 리스트형 [MySQL] (Medium) Find all wineries which produce wines by possessing aromas of plum, cherry, rose, or hazelnut 문제: https://platform.stratascratch.com/coding/10026-find-all-wineries-which-produce-wines-by-possessing-aromas-of-plum-cherry-rose-or-hazelnut?code_type=3 풀이: 못 풀었땅 솔루션! select DISTINCT winery from winemag_p1 where lower(description) REGEXP '(plum|rose|cherry|hazelnut)([^a-z])' regexp 사용법에 대해 알아보자 regexp 란 Regular expression 의 약자를 뜻한다! 정규 표현식이라고 함 패턴을 찾아서 문자열을 처리하는 방법으로 쓰인다 문법은 솔루션처럼 WHERE data .. [MySQL] (Medium) Reviews of Categories 문제: https://platform.stratascratch.com/coding/10049-reviews-of-categories?code_type=3 StrataScratch - Reviews of Categories platform.stratascratch.com 나의 풀이: select categories , SUM(review_count) as total_reviews_count from yelp_business group by categories order by 1; 단순하게 생각하고 이지~~ 했는데 틀렸음!! ㅋㅋ 이 문제의 포인트는 categories안에 카테고리가 ; 으로 구분되어 여러개가 묶여있어서 (Asian Fusion;Japanese;Restaurants;Salad) 이걸 분리해줘.. [MySQL] (Medium) Highest Energy Consumption 문제: https://platform.stratascratch.com/coding/10064-highest-energy-consumption?code_type=3 나의풀이: with tb as ( select date , ifnull(e.consumption, 0) as eu_con , ifnull(a.consumption, 0) as a_con , ifnull(n.consumption, 0) as n_con from fb_eu_energy e left join fb_asia_energy a using (date) left join fb_na_energy n using (date) union select date , ifnull(e.consumption, 0) as eu_con , ifnull(a.con.. [MySQL] (Medium) Spam Posts 문제: https://platform.stratascratch.com/coding/10134-spam-posts?code_type=3 나의 풀이: select post_date , count(distinct(case when post_keywords like '%spam%' then post_id end)) / count(distinct post_id) * 100 as span_share from facebook_posts join facebook_post_views using (post_id) group by post_date; 우하항 맞췄다 솔루션: SELECT spam_summary.post_date, (n_spam/n_posts)*100 AS spam_share FROM (SELECT post_d.. [MySQL] (Medium) Apple Product Counts 문제: https://platform.stratascratch.com/coding/10141-apple-product-counts/official-solution?code_type=3 나의풀이: select language , count(distinct(case when device in ('macbook pro', 'iphone 5s', 'ipad air') then user_id end)) as n_apple_user , count(distinct user_id) as n_total_users from playbook_events e join playbook_users u using (user_id) group by language order by 3 desc; 정답~~예엥 솔루션도 똑같다! 처음에는.. [MySQL] (Medium) Number Of Units Per Nationality 문제: https://platform.stratascratch.com/coding/10156-number-of-units-per-nationality?code_type=3 나의풀이: select nationality , count(*) as apartment_count from airbnb_hosts h join airbnb_units u using (host_id) where age < 30 and unit_type = 'Apartment' group by nationality order by 2 desc; 틀림!!! 왜??' 솔루션: SELECT nationality, count(distinct unit_id) as apartment_count FROM airbnb_units apartment INN.. [MySQL] (Medium) Ranking Most Active Guests 문제: https://platform.stratascratch.com/coding/10159-ranking-most-active-guests?code_type=3 나의 풀이: select id_guest , dense_rank() over (order by sum(n_messages) desc) as ranks , sum(n_messages) as messages from airbnb_contacts group by id_guest order by 3 desc; 솔루션: select id_guest, dense_rank() over (order by sum(n_messages) desc) as ranks, sum(n_messages) messages from airbnb_contacts group by .. [MySQL] (Medium) Acceptance Rate By Date 문제: https://platform.stratascratch.com/coding/10285-acceptance-rate-by-date?code_type=3 나의 풀이: with tb as ( select user_id_sender , user_id_receiver , min(date) as date , count(*) as cnt from fb_friend_requests group by user_id_sender, user_id_receiver) select date , count(case when cnt = 2 then 1 end) / count(*) as rate from tb group by date order by date; 그루핑을 어떻게 할 건지 고민을 조금 하다가 결국 풀었당!! 😊 하지.. 이전 1 2 다음