Leetcode SQL (#1164, #1173 – #1174, #1179)

select 
p2.product_id, 
p2.new_price as price
from(
    select 
    product_id, 
    new_price, 
    change_date, 
    row_number() over(partition by product_id order by change_date desc) as r
    from(
        select 
        product_id, 
        new_price, 
        change_date
        from Products
        where change_date <= '2019-08-16'
        union
        select 
        product_id, 
        10 as new_price,
        min(change_date) as change_date
        from Products
        group by 1,2
        having min(change_date) > '2019-08-16'
    ) p
) p2
where p2.r = 1
order by 2 desc

select 
round(100 * 
      (count(distinct case when order_date = customer_pref_delivery_date then delivery_id else null end)
       /
       count(distinct delivery_id)),2
     ) as immediate_percentage
from Delivery

select 
round(
    (count(distinct case when type = 'immediate' then customer_id else null end) 
     / 
     count(distinct customer_id))*100, 2
) as immediate_percentage
from(
    select 
    customer_id,
    case 
        when order_date = customer_pref_delivery_date then 'immediate' 
        else 'scheduled' 
    end as type,
    rank() over(partition by customer_id order by order_date) as order_rank
    from Delivery
) o
where o.order_rank = 1

select 
d.id, 
j.jan_revenue, 
f.feb_revenue, 
m.mar_revenue, 
a.apr_revenue, 
m2.may_revenue, 
j2.jun_revenue,
j3.jul_revenue, 
a2.aug_revenue, 
s.sep_revenue, 
o.oct_revenue, 
n.nov_revenue, 
d2.dec_revenue
from (
    select distinct 
    id 
    from department
) d
    left join (select id, revenue as jan_revenue from department where month = 'Jan') j 
        on d.id = j.id
    left join (select id, revenue as feb_revenue from department where month = 'Feb') f 
        on d.id = f.id
    left join (select id, revenue as mar_revenue from department where month = 'Mar') m 
        on d.id = m.id
    left join (select id, revenue as apr_revenue from department where month = 'Apr') a 
        on d.id = a.id
    left join (select id, revenue as may_revenue from department where month = 'May') m2 
        on d.id = m2.id
    left join (select id, revenue as jun_revenue from department where month = 'Jun') j2 
        on d.id = j2.id
    left join (select id, revenue as jul_revenue from department where month = 'Jul') j3 
        on d.id = j3.id
    left join (select id, revenue as aug_revenue from department where month = 'Aug') a2 
        on d.id = a2.id
    left join (select id, revenue as sep_revenue from department where month = 'Sep') s 
        on d.id = s.id
    left join (select id, revenue as oct_revenue from department where month = 'Oct') o 
        on d.id = o.id
    left join (select id, revenue as nov_revenue from department where month = 'Nov') n 
        on d.id = n.id
    left join (select id, revenue as dec_revenue from department where month = 'Dec') d2 
        on d.id = d2.id
order by d.id