해리문의 데이터_log

solvesql - 폐쇄할 따릉이 정류소 찾기 2👉 문제 바로가기(solvesql)-- 2018년 데이터 테이블 생성WITH t_2018 AS(SELECT t1.station_id, t1.name, t1.local, CASE WHEN (COALESCE(t2.rent_count, 0) + COALESCE(t3.return_count, 0)) IS NULL THEN 0 ELSE (COALESCE(t2.rent_count, 0) + COALESCE(t3.return_count, 0)) END as 'count_18'FROM station t1LEFT OUTER JOIN (SELECT rent_station_id, COUNT(*) as rent_count FROM rental_histo..

solvesql - 친구 수 집계하기👉 문제 바로가기(solvesql)-- 데이터베이스에 포함된 모든 사용자에 대해 각 사용자의 친구 수를 집계해 출력WITH tmp_t AS ( SELECT user_id, SUM(num_friends) as num_friends FROM (SELECT user_a_id as user_id, COUNT(DISTINCT user_b_id) as num_friends FROM edges GROUP BY user_a_id UNION ALL SELECT user_b_id as user_id, COUNT(DISTINCT user_a_id) as num_friends FROM edges GROUP BY user_b_id) GR..

solvesql - 지역별 주문의 특징👉 문제 바로가기(solvesql)-- 자역별, 특정 카테고리별 주문 건수SELECT t1.region as Region, COUNT(DISTINCT t1.Furniture) as 'Furniture', COUNT(DISTINCT t1.Office_Supplies) as 'Office Supplies', COUNT(DISTINCT t1.Technology) as 'Technology'FROM (SELECT region, CASE WHEN category == 'Furniture' THEN order_id END as 'Furniture', CASE WHEN category == 'Office Supplies' THEN order_id END..