초니로그

문제 URL : https://solvesql.com/problems/weekday-stats-airpollution/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select case strftime('%u',measured_at)
  when '1' then '월요일'
  when '2' then '화요일'
  when '3' then '수요일'
  when '4' then '목요일'
  when '5' then '금요일'
  when '6' then '토요일'
  when '7' then '일요일'
  end as weekday,
  round(avg(no2),4) as no2,
  round(avg(o3),4) as o3,
  round(avg(co),4) as co,
  round(avg(so2),4) as so2,
  round(avg(pm10),4) as pm10,
  round(avg(pm2_5),4) as pm2_5

from measurements
group by 1
order by strftime('%u',measured_at)

strftime을 통해 날짜 데이터를 기반으로 요일 값(1~7) 추출

case when문을 통해 특정 조건에 따른 새로운 열 생성

avg를 통해 평균값 도출

round을 통해 소수 반올림 수행

문제 URL : https://solvesql.com/problems/shoppingmall-monthly-summary/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select 
  strftime('%Y-%m',order_date) as order_month,
  sum(case when orders.order_id not like 'C%' then price*quantity else 0 end) as ordered_amount,
  sum(case when orders.order_id like 'C%' then price*quantity else 0 end) as canceled_amount,
  sum(price*quantity) as total_amount

from orders left join order_items
on orders.order_id = order_items.order_id

group by order_month
order by order_month

strftime(형식, 데이터)를 통해 특정 형태의 날짜 데이터 반환

case when 문을 통해 특정 조건에 해당하는 값만 추출하여 활용

left join ~ on 문을 통해 테이블 결합

문제 URL : https://solvesql.com/problems/mentor-mentee-list/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select YB.employee_id as mentee_id, 
       YB.name as mentee_name,
       OB.employee_id as mentor_id,
       OB.name as mentor_name

from employees YB cross join employees OB

where YB.join_date between '2021-09-01' and '2021-12-31'
and OB.join_date <= '2019-12-31'
and YB.department != OB.department

order by mentee_id, mentor_id

두 테이블 간의 결합에 있어 가능한 모든 조합을 확인할 수 있는 CROSS JOIN 사용

문제 URL : https://solvesql.com/problems/artists-without-artworks/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select artist_id, name
from artists
where death_year is not null
and artist_id not in (
  select artist_id from artworks_artists
)

서브쿼리를 통한 조건 추가 사용

문제 URL : https://solvesql.com/problems/daily-arppu/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

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

중복되는 주문ID가 존재하여 DISTINCT 사용

또한 중복으로 인하여 AVG 함수를 사용하면, 나누어지는 값이 중복을 포함하여 더 크므로 평균값이 기댓값보다 낮게 나옴

이를 해결하고자 (총 금액)/(중복을 제외한 주문건수)로 평균값을 구함

문제 URL : https://solvesql.com/problems/estimated-delivery-date/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select date(order_purchase_timestamp) as purchase_date,
count(case when order_delivered_customer_date <= order_estimated_delivery_date then order_id end) as success,
count(case when order_delivered_customer_date > order_estimated_delivery_date then order_id end) as fail
from olist_orders_dataset
where purchase_date like '2017-01-%'
group by purchase_date
order by purchase_date

CASE WHEN절과 COUNT함수를 함께 사용하여, 특정 경우에 해당하는 행만 집계함

특정 년월에 해당하는 조건 설정을 위해 LIKE절과 와일드카드 사용

(SQLite 기반으로, date 데이터 내 년,월,일 정보를 추출하는 것은 year 등의 메소드가 아니라 strftime 메소드를 사용해야 함)

문제 URL : https://solvesql.com/problems/characteristics-of-orders/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select region as Region,
count(distinct(case when category = 'Furniture' then order_id end)) as 'Furniture',
count(distinct(case when category = 'Office Supplies' then order_id end)) as 'Office Supplies',
count(distinct(case when category = 'Technology' then order_id end)) as 'Technology'
from records
group by Region
order by Region

구하고자 하는 열 별로 각기 다른 조건에 대해 판단하고 카운팅할 때, CASE WHEN절을 집계함수와 함께 사용하면 풀이 가능

문제 URL : https://solvesql.com/problems/installment-month/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select payment_installments,
count(distinct order_id) as order_count,
min(payment_value) as min_value,
max(payment_value) as max_value,
avg(payment_value) as avg_value
from olist_order_payments_dataset
where payment_type = "credit_card"
group by payment_installments

min, max, avg -> 각각 열의 최솟값, 최댓값, 평균값을 구하는 집계함수

group by를 통해 특정 기준으로 데이터를 그룹화하여 그 안에서 각각 집계 수행

문제 URL : https://solvesql.com/problems/multiple-medalist/

*문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

select athletes.name
from athletes, records, games
where athletes.id = records.athlete_id
and records.game_id = games.id
and medal is not null
and year >= 2000
group by athlete_id
having count(distinct team_id) > 1
order by name

3개의 테이블을 조인하기 위하여 WHERE절을 이용한 Multiple Joins를 적용

count(~)를 where절 내에 작성했다가 "misuse of aggregate: count()" 오류 발생 -> having절 내에 작성하여 해결 

문제 URL : https://solvesql.com/problems/olist-daily-revenue/

* 문제 저작권으로 인하여 직접 작성한 쿼리문만 첨부

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

SQLite에서 DATE(datetime) - 'yyyy-mm-dd' 형태로 반환해줌

ROUND(데이터,반올림할 자릿수) - 데이터의 주어진 자릿수까지 반올림하여 반환

SUM(데이터) - 해당하는 행 값을 모두 합하여 반환

tbl1 LEFT JOIN tbl2 ON (조건) - tbl1을 기준으로 조건에 맞는 행을 연결하는 방식으로 tbl2 테이블 결합