SHARE
TWEET

rvd

quan8410 May 26th, 2019 63 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with sku as
  2. (
  3. SELECT grass_date,
  4.        main_category,
  5.        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,
  6.        count(distinct case when item_stock>0 and item_status=1 then itemid else null end) as all_sku
  7.        from item_profile_history
  8.        where grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)  
  9.        group by GROUPING SETS ((grass_date), (grass_date, main_category))
  10. )
  11.  
  12. ,sales as
  13. (
  14. SELECT grass_date,
  15.        main_category,
  16.        count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as ado,
  17.        count(distinct orderid) as all_ado,
  18.        cast(count(distinct case when has_lowest_price_guarantee=True then orderid else null end) as DOUBLE)/count(distinct orderid) as ado_contribution,
  19.        count(distinct case when has_lowest_price_guarantee=True then itemid else null end) as sku_with_sales,
  20.        sum(case when has_lowest_price_guarantee=True then gmv_usd else null end) as gmv,
  21.        sum(gmv_usd) as all_gmv,
  22.        cast(sum(case when has_lowest_price_guarantee=True then gmv_usd else null end) as DOUBLE)/sum(gmv_usd) as gmv_contribution,
  23.        sum(case when has_lowest_price_guarantee=True then shopee_item_rebate_usd else null end) as rebate,
  24.        sum(case when has_lowest_price_guarantee=True then shopee_item_rebate_usd else null end)/count(itemid) as rebate_per_item,
  25.        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,
  26.        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,
  27.        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,
  28.        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,
  29.        avg(case when has_lowest_price_guarantee=True then shopee_voucher_rebate_usd else null end) as avg_voucher_value,
  30.        avg(case when has_lowest_price_guarantee=True and price_before_discount_usd>0 then price_before_discount_usd
  31.                 when has_lowest_price_guarantee=True and price_before_discount_usd=0 then price_usd
  32.                 else null end)-avg(case when has_lowest_price_guarantee=True then price_usd else null end) as avg_discount_value
  33. from order_mart__order_item_profile
  34. where grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)  
  35. group by GROUPING SETS ((grass_date), (grass_date, main_category))
  36. )
  37.  
  38. , view as
  39. (
  40. SELECT h.grass_date,
  41.        h.main_category,
  42.        sum(item_view) as item_view,
  43.        sum(unique_item_view) as unique_item_view,
  44.        sum(click_times) as click_times
  45. from item_profile_history h left join shopee_bi_vn_product_views v
  46. on h.itemid=v.itemid
  47. and h.shopid=v.shopid
  48. and h.grass_date=v.grass_date
  49. where h.item_lowest_price_guarantee=TRUE
  50. and h.item_status=1
  51. and h.grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)  
  52. and h.item_stock>0
  53. group by GROUPING SETS((h.grass_date), (h.grass_date, h.main_category))
  54. )
  55. , user_rating as
  56. (
  57. SELECT  h.grass_date,
  58.         h.main_category,
  59.         avg(case when rating=1 then cast(rating_star as DOUBLE) else null end) as user_rating
  60. from item_profile_history h left join shopee_user_comment_vn_db__user_cmt_tab u
  61. on h.shopid=u.shopid
  62. and h.grass_date=date(from_unixtime(u.mtime))
  63. where h.item_lowest_price_guarantee=True
  64. and grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)  
  65. group by GROUPING SETS((grass_date), (grass_date, main_category))
  66. )
  67.  
  68. , item_rating as  
  69. (
  70. SELECT  h.grass_date,
  71.         h.main_category,
  72.         avg(case when rating=1 then cast(rating_star as DOUBLE) else null end) as item_rating
  73. from item_profile_history h left join shopee_item_comment_vn_db__item_cmt_v2_tab i
  74. on h.shopid=i.shopid
  75. and h.itemid=i.itemid
  76. and h.grass_date=date(from_unixtime(i.mtime))
  77. where h.item_lowest_price_guarantee=True
  78. and grass_date between date_add('day',-30,current_date) and date_add('day',-1,current_date)  
  79. group by GROUPING SETS((grass_date), (grass_date, main_category))
  80. )      
  81.  
  82.  
  83. ,not_null as
  84. (
  85. SELECT s.*,
  86.        k.rvd_sku,
  87.        k.all_sku,
  88.        v.item_view,
  89.        v.unique_item_view,
  90.        v.click_times,
  91.        ur.user_rating,
  92.        ir.item_rating
  93. from sales s inner join sku k
  94. on s.grass_date=k.grass_date
  95. and s.main_category=k.main_category
  96. --and ((s.main_category=k.main_category) or (s.main_category is null and k.main_category is null))
  97. inner join view v
  98. on s.grass_date=v.grass_date
  99. and s.main_category=v.main_category
  100. --and ((s.main_category=sku.main_category) or (s.main_category is null and v.main_category is null))
  101. inner join user_rating ur
  102. on s.grass_date=ur.grass_date
  103. and s.main_category=ur.main_category
  104. --and ((s.main_category=sku.main_category) or (s.main_category is null and ur.main_category is null))
  105. inner join item_rating ir
  106. on s.grass_date=ir.grass_date
  107. and s.main_category=ir.main_category
  108. where s.main_category is not null
  109. and k.main_category is not null
  110. and ur.main_category is not null
  111. and ir.main_category is not null
  112. --and ((s.main_category=sku.main_category) or (s.main_category is null and ir.main_category is null))
  113. )
  114.  
  115. , is_null as
  116.  
  117. (
  118. SELECT distinct s.*,
  119.        k.rvd_sku,
  120.        k.all_sku,
  121.        v.item_view,
  122.        v.unique_item_view,
  123.        v.click_times,
  124.        ur.user_rating,
  125.        ir.item_rating
  126. from sales s inner join sku k
  127. on s.grass_date=k.grass_date
  128. --and s.main_category=k.main_category
  129. --and ((s.main_category=k.main_category) or (s.main_category is null and k.main_category is null))
  130. inner join view v
  131. on s.grass_date=v.grass_date
  132. --and s.main_category=v.main_category
  133. --and ((s.main_category=sku.main_category) or (s.main_category is null and v.main_category is null))
  134. inner join user_rating ur
  135. on s.grass_date=ur.grass_date
  136. --and s.main_category=ur.main_category
  137. --and ((s.main_category=sku.main_category) or (s.main_category is null and ur.main_category is null))
  138. inner join item_rating ir
  139. on s.grass_date=ir.grass_date
  140. --and s.main_category=ir.main_category
  141. --and ((s.main_category=sku.main_category) or (s.main_category is null and ir.main_category is null))
  142. where s.main_category is null
  143. and k.main_category is null
  144. and ur.main_category is null
  145. and ir.main_category is null
  146. )
  147.  
  148. (SELECT * from not_null)
  149. UNION ALL
  150. SELECT * from is_null
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top