[SQL문제풀기 - Advent of SQL 2025 🎅] 연도별 배송 업체 이용 내역 분석하기

문제

solvesql.com

https://solvesql.com/problems/yearly-shipping-usage/

내가 작성한 정답

MySQL

select year(purchased_at) year, 
       sum(case when is_returned and shipping_method = 'Standard' then 2
                when is_returned then 1 
                when shipping_method = 'Standard' then 1 end) standard,
       sum(case when shipping_method = 'Express' then 1 end) express,
       sum(case when shipping_method = 'Overnight' then 1 end) overnight
from transactions
where is_online_order
group by year(purchased_at)
order by 1;

select year(purchased_at) year, 
       sum(case when is_returned then 1 end) +
       sum(case when shipping_method = 'Standard' then 1 end) standard,
       sum(case when shipping_method = 'Express' then 1 end) express,
       sum(case when shipping_method = 'Overnight' then 1 end) overnight
from transactions
where is_online_order
group by year(purchased_at)
order by 1;

PostgreSQL

select extract(year from purchased_at) as year, 
       sum(case when is_returned and shipping_method = 'Standard' then 2
                when is_returned then 1 
                when shipping_method = 'Standard' then 1 end) standard,
       sum(case when shipping_method = 'Express' then 1 end) express,
       sum(case when shipping_method = 'Overnight' then 1 end) overnight
from transactions
where is_online_order
group by extract(year from purchased_at)
order by 1;

select extract(year from purchased_at) as year, 
       sum(case when is_returned then 1 end) +
       sum(case when shipping_method = 'Standard' then 1 end) standard,
       sum(case when shipping_method = 'Express' then 1 end) express,
       sum(case when shipping_method = 'Overnight' then 1 end) overnight
from transactions
where is_online_order
group by extract(year from purchased_at)
order by 1;

SQLite

select strftime('%Y',purchased_at) year, 
       sum(case when is_returned and shipping_method = 'Standard' then 2
                when is_returned then 1 
                when shipping_method = 'Standard' then 1 end) standard,
       sum(case when shipping_method = 'Express' then 1 end) express,
       sum(case when shipping_method = 'Overnight' then 1 end) overnight
from transactions
where is_online_order
group by strftime('%Y',purchased_at)
order by 1;

select strftime('%Y',purchased_at) year, 
       sum(case when is_returned then 1 end) +
       sum(case when shipping_method = 'Standard' then 1 end) standard,
       sum(case when shipping_method = 'Express' then 1 end) express,
       sum(case when shipping_method = 'Overnight' then 1 end) overnight
from transactions
where is_online_order
group by strftime('%Y',purchased_at)
order by 1;