6.22
1) 쇼핑몰의 일일 매출액
select date(o.order_purchase_timestamp) as dt,
round(sum(p.payment_value),2) as revenue_daily
from olist_orders_dataset o
inner join olist_order_payments_dataset p using(order_id)
where date(o.order_purchase_timestamp) >= '2018-01-01'
group by 1
order by 1
2) 점검이 필요한 자전거 찾기
select bike_id
from rental_history
where return_at like '2021-01%' and rent_at like '2021-01%'
group by 1
having sum(distance) >= 50000
3) 레스토랑의 대목
select *
from tips
where day in (select day from tips group by day having sum(total_bill) >= 1500)
6.23
1) 레스토랑의 요일별 VIP
select *
from tips
where total_bill in (select max(total_bill) from tips group by day)
2) 다음날도 서울숲의 미세먼지 농도는 나쁨 😢
select *
from (
select
measured_at as today,
lead(measured_at, 1) over (order by measured_at) as next_day,
pm10,
lead(pm10, 1) over (order by measured_at) as next_pm10
from measurements
) as t
where next_pm10 > pm10
3) 제목이 모음으로 끝나지 않는 영화
select title
from film
where rating in ('R', 'NC-17')
and title not like '%A' and title not like '%E'
and title not like '%I' and title not like '%O'
and title not like '%U'
- 마지막 모음이 아닌 경우 찾는 다른 방법(케이스)
select title
from film
where rating in ('R','NC-17')
and substr(title,-1) not in ('A','E','I','O','U')
6.24
1) 언더스코어(_)가 포함되지 않은 데이터 찾기
select distinct page_location
from ga
where page_location not like '%@_%' ESCAPE '@'
order by 1
- 이스케이프(ESCAPE)를 활용하여 특수 문자를 조회하거나 조건을 걸 수 있다.
2) 게임을 10개 이상 발매한 게임 배급사 찾기
select c.name
from companies c
join games g on c.company_id = g.publisher_id
group by g.publisher_id
having count(*) >= 10
3) 3년간 들어온 소장품 집계하기
select classification,
sum(case when year(acquisition_date) = 2014 then 1 else 0 end) as '2014',
sum(case when year(acquisition_date) = 2015 then 1 else 0 end) as '2015',
sum(case when year(acquisition_date) = 2016 then 1 else 0 end) as '2016'
from artworks
group by 1
order by 1
6.25
1) 12월 우수 고객 찾기
select customer_id
from records
where month(order_date) = 12
group by 1
having sum(sales) >= 1000
2) 스탬프를 찍어드려요
select case when total_bill >= 25 then 2
when total_bill >= 15 then 1 else 0 end as stamp,
count(*) as count_bill
from tips
group by 1
order by 1
3) DVD 대여점 우수 고객 찾기
select customer_id
from rental r
join customer c using(customer_id)
where c.active = 1
group by 1
having count(*) >= 35
6.26
1) 이틀 연속 미세먼지가 나빠진 날
with base as (
select measured_at,
pm10,
lag(pm10,1) over(order by measured_at) as pm10_prev,
lag(pm10,2) over(order by measured_at) as pm10_prev2
from measurements
where year(measured_at) = 2022
)
select measured_at as date_alert
from base
where pm10 >= 30
and pm10_prev2 < pm10_prev
and pm10_prev < pm10
order by 1
2) 레스토랑의 주중, 주말 매출액 비교하기
select case when day = 'Sat' or day = 'Sun' then 'weekend'
else 'weekday' end as week,
sum(total_bill) as sales
from tips
group by 1
order by 2 desc
3) 한국 감독의 영화 찾기
select a.name as artist,
aw.title
from artists a
join artworks_artists aa using(artist_id)
join artworks aw using(artwork_id)
where a.nationality = 'Korean'
and aw.classification like 'Film%'
