with sku as
(
SELECT grass_date,
       main_category,
       count(distinct case when item_lowest_price_guarantee=True and item_stock>0 and item_status=1 then itemid else null end) as rvd_sku,
       count(distinct case when item_stock>0 and item_status=1 then itemid else null end) as all_sku
       from item_profile_history
       where grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)
       group by GROUPING SETS ((grass_date), (grass_date, main_category))
)

,sales as
(
SELECT grass_date,
       main_category,
       count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as ado,
       count(distinct orderid) as all_ado,
       cast(count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as DOUBLE)/count(distinct orderid) as ado_contribution,
       count(distinct case when has_lowest_price_guarantee=True then itemid else null end) as sku_with_sales,
       sum(case when has_lowest_price_guarantee=True then gmv_usd else null end) as gmv,
       sum(gmv_usd) as all_gmv,
       cast(sum(case when has_lowest_price_guarantee=True then gmv_usd else null end) as DOUBLE)/sum(gmv_usd) as gmv_contribution,
       sum(case when has_lowest_price_guarantee=True then shopee_item_rebate_usd else null end) as rebate,
       sum(case when has_lowest_price_guarantee=True then shopee_item_rebate_usd else null end)/count(itemid) as rebate_per_item,
       sum(case when has_lowest_price_guarantee=True then shopee_voucher_rebate_usd else null end)/count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as voucher_per_order,
       cast(count(distinct case when has_lowest_price_guarantee=True and shopee_voucher_rebate_usd>0 then userid else null end) as DOUBLE)/count(distinct case when shopee_voucher_rebate_usd>0 then userid else null end) as voucher_used,
       cast(count(distinct case when has_lowest_price_guarantee=True and shopee_voucher_rebate_usd>0 then orderid else null end) as DOUBLE)/count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as voucher_order_contribution,
       cast(count(distinct case when has_lowest_price_guarantee=True and free_shipping_voucher is not null  then orderid else null end) as DOUBLE)/count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as freeship_order_contribution,
       avg(case when has_lowest_price_guarantee=True then shopee_voucher_rebate_usd else null end) as avg_voucher_value,
       avg(case when has_lowest_price_guarantee=True and price_before_discount_usd>0 then price_before_discount_usd
                when has_lowest_price_guarantee=True and price_before_discount_usd=0 then price_usd
                else null end)-avg(case when has_lowest_price_guarantee=True then price_usd else null end) as avg_discount_value
from order_mart__order_item_profile
where grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)
group by GROUPING SETS ((grass_date), (grass_date, main_category))
)

, view as
(
SELECT h.grass_date,
       h.main_category,
       sum(item_view) as item_view,
       sum(unique_item_view) as unique_item_view,
       sum(click_times) as click_times
from item_profile_history h left join shopee_bi_vn_product_views v
on h.itemid=v.itemid
and h.shopid=v.shopid
and h.grass_date=v.grass_date
where h.item_lowest_price_guarantee=TRUE
and h.item_status=1
and h.grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)
and h.item_stock>0
group by GROUPING SETS((h.grass_date), (h.grass_date, h.main_category))
)
, user_rating as
(
SELECT  h.grass_date,
        h.main_category,
        avg(case when rating=1 then cast(rating_star as DOUBLE) else null end) as user_rating
from item_profile_history h left join shopee_user_comment_vn_db__user_cmt_tab u
on h.shopid=u.shopid
and h.grass_date=date(from_unixtime(u.mtime))
where h.item_lowest_price_guarantee=True
and grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)
group by GROUPING SETS((grass_date), (grass_date, main_category))
)

, item_rating as
(
SELECT  h.grass_date,
        h.main_category,
        avg(case when rating=1 then cast(rating_star as DOUBLE) else null end) as item_rating
from item_profile_history h left join shopee_item_comment_vn_db__item_cmt_v2_tab i
on h.shopid=i.shopid
and h.itemid=i.itemid
and h.grass_date=date(from_unixtime(i.mtime))
where h.item_lowest_price_guarantee=True
and grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)
group by GROUPING SETS((grass_date), (grass_date, main_category))
)



--,not_null as
--(
SELECT s.*,
       k.rvd_sku,
       k.all_sku,
       v.item_view,
       v.unique_item_view,
       v.click_times,
       ur.user_rating,
       ir.item_rating
from sales s inner join sku k
on s.grass_date=k.grass_date
--and s.main_category=k.main_category
and ((s.main_category=k.main_category) or (s.main_category is null and k.main_category is null))
inner join view v
on s.grass_date=v.grass_date
--and s.main_category=v.main_category
and ((s.main_category=v.main_category) or (s.main_category is null and v.main_category is null))
inner join user_rating ur
on s.grass_date=ur.grass_date
--s.main_category=ur.main_category
and ((s.main_category=ur.main_category) or (s.main_category is null and ur.main_category is null))
inner join item_rating ir
on s.grass_date=ir.grass_date
--and s.main_category=ir.main_category
and ((s.main_category=ir.main_category) or (s.main_category is null and ir.main_category is null))