[sql] DAY13 (solvesql)

lv쉬움. GROUP BY : 최고의 근무일을 찾아라
요일별로 팁 총액을 집계하고 팁이 가장 많았던 요일과 그날의 팁 총액을 출력하는 쿼리를 작성해주세요.

결과 데이터는 아래 컬럼을 포함해야 합니다.

day - 요일
tip_daily - 요일 별 팁 총액

select distinct
  day,
  sum(tip) as tip_daily
from
  tips
group by
  day
order by
  sum(tip) desc
limit
  1

---

lv쉬움. GROUP BY : 버뮤다 삼각지대에 들어가버린 택배
2017년 1월 한 달 동안 택배사에 전달되었지만 배송 완료는 되지 않은 주문 건수를 택배사 도착일을 기준으로 집계하는 쿼리를 작성해주세요. 쿼리 결과는 택배사 도착일을 기준으로 오름차순 정렬되어야 하고, 아래 컬럼을 포함해야 합니다.

delivered_carrier_date - 택배사 도착 날짜 (예: 2017-01-16)
orders - 택배사에 도착했지만, 고객에게 배송되지 않은 주문 건 수

select distinct
  substr(order_delivered_carrier_date, 1, 10) as delivered_carrier_date,
  count(*) as orders 
from
  olist_orders_dataset
where
  order_delivered_carrier_date like "2017-01-%"
  and order_delivered_customer_date isnull
group by
  delivered_carrier_date

---

lv쉬움. JOIN : 쇼핑몰의 일일 매출액
2018년 1월 1일 이후 쇼핑몰의 일일 매출액을 계산하는 쿼리를 작성해주세요. 쿼리 결과는 아래 두 컬럼을 포함해야 하고, 매출 날짜 기준으로 오름차순 정렬되어 있어야 합니다. 매출액은 반올림 해 소수점 둘째자리까지 출력해주세요.

dt - 매출 날짜 (예: 2018-01-01)
revenue_daily - 해당 날짜의 매출액

select distinct
  date(o.order_purchase_timestamp) as dt,
  round(sum(p.payment_value), 2) as revenue_daily
from
  olist_orders_dataset as o
  left join olist_order_payments_dataset as p
  on o.order_id = p.order_id
where
  dt >= '2018-01-01'
group by
  dt
order by
  dt

---

lv쉬움. JOIN : 쇼핑몰의 일일 매출액과 ARPPU
2018년 1월 1일 이후 일별로 집계된 쇼핑몰의 결제 고객 수, 매출액, ARPPU를 계산하는 쿼리를 작성해주세요. ARPPU는 Average Revenue Per Paying User의 약자로, 결제 고객 1인 당 평균 결제 금액을 의미합니다. 전체 매출액을 결제 고객 수로 나누면 ARPPU를 계산할 수 있습니다.쿼리 결과는 아래 네 개의 컬럼을 포함해야 하고, 매출 날짜 기준으로 오름차순 정렬되어 있어야 합니다. 매출액과 ARPPU는 반올림 해 소수점 둘째자리까지 출력해주세요.

dt - 매출 날짜 (예: 2018-01-01)
pu - 결제 고객 수
revenue_daily - 해당 날짜의 매출액
arppu - 결제 고객 1인 당 평균 결제 금액

select distinct
  date(o.order_purchase_timestamp) as dt,
  count(distinct o.customer_id) as pu,
  round(sum(p.payment_value), 2) as revenue_daily,
  round(
    sum(p.payment_value) / count(distinct o.customer_id),
    2
  ) as arppu
from
  olist_orders_dataset as o
  left join olist_order_payments_dataset as p
  on o.order_id = p.order_id
where
  dt >= '2018-01-01'
group by
  dt
order by
  dt

---

lv쉬움. JOIN : 멘토링 짝꿍 리스트
회사에서 신규 입사자들의 빠른 적응을 돕기 위해 멘토링 프로그램을 운영하려고 합니다. 멘티가 될 신규 입사자들은 ‘2021년 12월 31일’을 기준으로 3개월 이내 입사한 인원 전체이며, 멘토는 ‘2021년 12월 31일’을 기준으로 재직한지 2년 이상이 된 직원들만 배정하려고 합니다. 또한 최대한 다양한 분야의 직원들이 서로 교류 할 수 있도록 서로 다른 부서에 속하는 직원끼리 멘토링을 진행하려고 합니다.

위 조건을 모두 만족하는 멘티-멘토 짝꿍 리스트를 계산하는 쿼리를 작성해주세요. 쿼리 결과에는 매칭 가능한 멘토가 없는 경우도 모두 포함되어야 합니다. 추가로 쿼리 결과는 멘티 ID를 기준으로 오름차순 정렬되어 있어야 하고, 멘티 1명에 대해 배정 가능한 멘토가 여러 명인 경우 멘토 ID로 오름차순 정렬되어 있어야 합니다.

mentee_id - 멘티 ID
mentee_name - 멘티 이름
mentor_id - 멘토 ID
mentor_name - 멘토 이름

#내 코드
#멘토, 멘티 값을 구하는 것까진 했는데, 어떻게 두 데이터를 합쳐야 하는지 몰랐다.
select name as mentee_name,
employee_id as mentee_id, 
join_date
from employees
where join_date >= datetime('2021-12-31','-3 months')

select name as mentor_name,
employee_id as mentor_id, 
join_date
from employees
where join_date <= datetime('2021-12-31','-2 years')

#정답 코드
#from에 여러가지를 한 번에 쓸 수 있다는 점
#이렇게 지칭한 이름을 where에 각각 조건을 줄 수 있다는 것도 알게 됐다.

select
  e1.employee_id as mentee_id,
  e1.name as mentee_name,
  e2.employee_id as mentor_id,
  e2.name as mentor_name
from
  employees as e1,
  employees as e2
where
  e1.join_date >= datetime('2021-12-31', '-3 months')
  and e2.join_date <= datetime('2021-12-31', '-2 years')
  and e1.department != e2.department
order by
  e1.join_date