✨나만의 개발일지✨

https://solvesql.com/problems/mentor-mentee-list/ https://solvesql.com/problems/mentor-mentee-list/ solvesql.com [코드] select a.employee_id as mentee_id, a.name as mentee_name, b.employee_id as mentor_id, b.name as mentor_name from employees as a left outer join employees as b on a.employee_id != b.employee_id where a.join_date > '2021-09-31' and b.join_date < '2020-01-01' and a.department != b.d..

https://solvesql.com/problems/characteristics-of-orders/ https://solvesql.com/problems/characteristics-of-orders/ solvesql.com [코드] select region as 'Region', sum(case when category = 'Furniture' then order_id end) as 'Furniture', sum(case when category = 'Office Supplies' then order_id end) as 'Office Supplies', sum(case when category = 'Technology' then order_id end) as 'Technology' from (sele..

https://solvesql.com/problems/daily-arppu/ https://solvesql.com/problems/daily-arppu/ solvesql.com [코드] select date(a.order_purchase_timestamp) as dt, count(DISTINCT a.customer_id) as pu, round(sum(b.payment_value), 2) as revenue_daily, round( sum(b.payment_value) / count(distinct a.customer_id), 2 ) as arppu from olist_orders_dataset as a inner join olist_order_payments_dataset as b on a.order_..

https://solvesql.com/problems/estimated-delivery-date/ https://solvesql.com/problems/estimated-delivery-date/ solvesql.com [코드] select date(order_purchase_timestamp) as purchase_date, count(case when date(order_delivered_customer_date) = date(order_estimated_delivery_date) th..

https://solvesql.com/problems/settled-sellers-2/ https://solvesql.com/problems/settled-sellers-2/ solvesql.com [코드] select seller_id, count(distinct order_id) as orders from olist_order_items_dataset where price >= 50 group by seller_id having orders >= 100 order by orders DESC; [고찰] 이번 문제는 주문이 100건 이상인 컬럼들을 출력해야 하므로 집계함수 COUNT에 조건을 달아주기 위해 seller_id로 그룹을 묶어주고 having 절에 orders >= 100 조건을 주어 해결할 ..

https://solvesql.com/problems/blog-counter/ https://solvesql.com/problems/blog-counter/ solvesql.com [코드] select event_date_kst as dt, count(DISTINCT user_pseudo_id) as users from ga where dt BETWEEN '2021-08-02' and '2021-08-09' group by dt order by dt [고찰] 이번 문제는 날짜별 방문자의 수를 구하는 문제였다. 이때 해당 일자 내 로그 테이블에 이벤트가 하나라도 기록 된 경우 방문자로 집계하기 때문에 중복된 사람이 있을 수 있다. 따라서 count를 해줄 때 중복을 제거해주어야 한다.