[solvesql] SQL 문제 풀이 (3)

1. 최고의 근무일을 찾아라

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

 

2. 버뮤다 삼각지대에 들어가버린 택배

select
  date(order_delivered_carrier_date) as delivered_carrier_date,
  count(*) as orders
from
  olist_orders_dataset
where
  date(order_delivered_carrier_date) between '2017-01-01' and '2017-01-31'
  and order_delivered_carrier_date is not null
  and order_delivered_customer_date is null
group by
  date(order_delivered_carrier_date);

.3. 쇼핑몰의 일일 매출액

select
  date(order_purchase_timestamp) as dt,
  round(sum(oopd.payment_value), 2) as revenue_daily
from
  olist_orders_dataset ord
  join olist_order_payments_dataset oopd on ord.order_id = oopd.order_id
where
  dt >= '2018-01-01'
group by
  date(order_purchase_timestamp);