[solvesql] 문제(쉬움) 풀이

문제 풀어보기

• 난이도 쉬움 풀이

두 테이블 결합하기

select distinct(a.athlete_id)
from records a, events b
where a.event_id = b.id and b.sport = 'Golf'

레스토랑 웨이터의 팁 분석

select day, time, round(avg(tip),2) as avg_tip, 
                  round(avg(size),2) as avg_size
from tips
group by day, time 

몇 분이서 오셨어요?

• mod함수 활용 mod(column,2) == 0 or 1

select *
from tips
where mod(size,2) = 1;

최근 올림픽이 개최된 도시

• 주제: String/Date
• 문자열 자르기 : substr (oracle) / left,right(mysql)

select year, upper(substr(city,1,3)) as city
from games
where year >=  2000 
order by year desc ;

우리 플랫폼에 정착한 판매자 1

select seller_id, count(distinct(order_id)) as orders
from olist_order_items_dataset 
group by seller_id
having orders >= 100

최고의 근무일을 찾아라

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

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

select DATE(ORDER_DELIVERED_CARRIER_DATE) AS delivered_carrier_date
	   , COUNT(ORDER_ID) AS orders 
from olist_orders_dataset 
where order_delivered_customer_date IS null and
       order_delivered_carrier_date LIKE '2017-01%'
group by date(order_delivered_carrier_date)
 order by order_delivered_carrier_date;

쇼핑몰의 일일 매출액

select date(a.order_purchase_timestamp) as dt, round(sum(b.payment_value),2) as revenue_daily 
from olist_orders_dataset a, olist_order_payments_dataset b  
where a.order_id = b.order_id and a.order_purchase_timestamp >= '2018-01-01' 
group by date(a.order_purchase_timestamp)
order by dt

쇼핑몰의 일일 매출액과 ARPPU

select date(a.order_purchase_timestamp)  as dt,
      count(distinct(b.order_id)) as pu,
      round(sum(b.payment_value),2) as revenue_daily,
      round(sum(b.payment_value)/count(distinct(b.order_id)),2) as arppu
from olist_orders_dataset a, olist_order_payments_dataset b
where a.order_id = b.order_id and 
      date(a.order_purchase_timestamp) >= '2018-01-01'
group by date(a.order_purchase_timestamp )
order by date(a.order_purchase_timestamp)

멘토링 짝꿍 리스트

• SQL 문법 CHECK : <>

select A.employee_id as mentee_id,
       A.name as mentee_name,
       B.employee_id as mentor_id,
       B.name as mentor_name
from 
(select * from employees where join_date between '2021-09-01' and '2021-12-31') A,
(select * from employees where join_date <= '2019-12-31') B
where a.department <> b.department
group by mentee_id, mentor_id
order by mentee_id, mentor_id

점검이 필요한 자전거 찾기

select bike_id
from rental_history
WHERE DATE(rent_at) like '2021-01%'
group by bike_id
having sum(distance) >= 50000