LeetCode Hard 1479 "Sales by Day of the Week" Amazon Interview SQL Question with Explanation

[@DEEPAK-jx5si]

amazing explanation!

[@EverydayDataScience]

Glad that you found it useful.

[@cheerfulchai]

Hi! Can you share the solution on GitRepo as well?

[@SchreiEs]

Another way of doing this with pivot function using SQL Server:
--Method 1
select
*
from
orderitem;
select
*
from
item;
with
combined as
(
select
item_category
,order_date
,datename(weekday,order_date) as weekday
,isnull(sum(quantity),0) as total_orders
from
orderitem o
right join item i on o.item_id = i.item_id
group by
item_category
,order_date
)
,orders as
(
select
item_category as category
,weekday
,sum(total_orders) as total_orders
from
combined
group by
item_category
,weekday
)
--select * from combined;
--select * from orders;
select
category as Category
,isnull([Monday],0) as Monday
,isnull([Tuesday],0) as Tuesday
,isnull([Wednesday],0) as Wednesday
,isnull([Thursday],0) as Thursday
,isnull([Friday],0) as Friday
,isnull([Saturday],0) as Saturday
,isnull([Sunday],0) as Sunday
from
orders
pivot
(
sum(total_orders)
for
weekday in
([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
) as pvt
order by
category;