## 1164. Product Price at a Given Date

Question:

Solution:

``````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``````

## 1173. Immediate Food Delivery I

Question:

Solution:

``````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``````

## 1174. Immediate Food Delivery II

Question:

Solution:

``````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``````

## 1179. Reformat Department Table

Question:

Solution:

``````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``````