SHARE
TWEET

Untitled

a guest Feb 27th, 2020 98 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH auth AS (
  2.   SELECT DISTINCT st.shopid
  3.   FROM shopee_id_anlys.id_bi_seller_type st
  4.   JOIN shopee_id_bi_team__rm_name_pass rm
  5.   ON rm.rm_name = st.rm_name
  6.   WHERE rm.rm_name IN ({{RM_name}}) AND rm.pass IN ({{Password}})
  7. ),
  8.  
  9. od AS (
  10.   SELECT
  11.     itemid, modelid,
  12.     SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.order_fraction,0) END) AS fs_order,
  13.     SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.order_fraction,0) END) AS non_fs_order,
  14.     SUM(COALESCE(o.order_fraction,0)) AS total_gross_order,
  15.     SUM(CASE WHEN LOWER(o.be_status) NOT IN ('invalid', 'return_procesing', 'cancel_pending', 'cancel_completed', 'cancel_processing', 'return_completed') THEN COALESCE(order_fraction,0) END) AS net_order,
  16.     SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.amount,0) END) AS fs_qty_sold,
  17.     SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.amount,0) END) AS non_fs_qty_sold,
  18.     SUM(COALESCE(o.amount,0)) AS qty_sold,
  19.     SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_FS_selling_price,
  20.     SUM(CASE WHEN promo_source != 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_Non_FS_selling_price,
  21.     SUM(order_price*o.amount)/SUM(amount) AS Avg_Non_FS_selling_price
  22.   FROM
  23.     order_mart__order_item_profile o
  24.   WHERE grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
  25.   AND shopid = {{shopid}}
  26.   AND shopid IN (SELECT shopid FROM auth)
  27.   GROUP BY 1,2
  28. ),
  29.  
  30. A AS (
  31. SELECT
  32.     ip.main_category,
  33.     ip.sub_category,
  34.     ip.level3_category,
  35.     ip.itemid,
  36.     ip.modelid,
  37.   DATE(ip.item_ctime) AS registered_date,
  38.     ip.item_name AS product_name,
  39.     ip.model_name,
  40.     ip.item_price AS current_price,
  41.   CASE WHEN ip.item_price_before_discount != 0 THEN ip.item_price_before_discount ELSE ip.item_price END AS original_price,
  42.     ip.item_stock,
  43.   od.fs_order,
  44.   od.non_fs_order,
  45.   od.total_gross_order,
  46.   od.net_order,
  47.   od.fs_qty_sold,
  48.   od.non_fs_qty_sold,
  49.   od.qty_sold,
  50.   od.Total_FS_selling_price,
  51.   od.Total_Non_FS_selling_price,
  52.   od.Avg_Non_FS_selling_price
  53. FROM item_profile ip
  54. LEFT JOIN od ON ip.itemid = od.itemid AND ip.modelid = od.modelid
  55. )
  56.  
  57. SELECT A.*,
  58. Total_FS_selling_price / fs_qty_sold AS Avg_FS_Selling_Price,
  59. Total_Non_FS_selling_price / non_fs_qty_sold AS Avg_Non_FS_selling_price,
  60. SUM(pv_cd) AS views,
  61. 1.0000 * total_gross_order / SUM(pv_cd) AS CR
  62. FROM A
  63. LEFT JOIN traffic_mart_dws__product_exposure v
  64. ON a.itemid = v.itemid
  65. WHERE v.grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
  66.     GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
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