SHARE
TWEET

Untitled

a guest Feb 27th, 2020 100 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.  
  10. smaller AS (
  11.   SELECT * FROM order_mart__order_item_profile o
  12.   WHERE grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
  13.   AND shopid = {{shopid}}
  14.   AND shopid IN (SELECT shopid FROM auth)
  15. ),
  16.  
  17. od AS (
  18.   SELECT
  19.     itemid, modelid,
  20.     SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.order_fraction,0) END) AS fs_order,
  21.     SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.order_fraction,0) END) AS non_fs_order,
  22.     SUM(COALESCE(o.order_fraction,0)) AS total_gross_order,
  23.     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,
  24.     SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.amount,0) END) AS fs_qty_sold,
  25.     SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.amount,0) END) AS non_fs_qty_sold,
  26.     SUM(COALESCE(o.amount,0)) AS qty_sold,
  27.     SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_FS_selling_price,
  28.     SUM(CASE WHEN promo_source != 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_Non_FS_selling_price,
  29.     SUM(order_price*o.amount)/SUM(amount) AS Avg_Non_FS_selling_price
  30.   FROM
  31.     smaller o
  32.   GROUP BY 1,2
  33. ),
  34.  
  35. A AS (
  36. SELECT
  37.     ip.main_category,
  38.     ip.sub_category,
  39.     ip.level3_category,
  40.     ip.itemid,
  41.     ip.modelid,
  42.   DATE(ip.item_ctime) AS registered_date,
  43.     ip.item_name AS product_name,
  44.     ip.model_name,
  45.     ip.item_price AS current_price,
  46.   CASE WHEN ip.item_price_before_discount != 0 THEN ip.item_price_before_discount ELSE ip.item_price END AS original_price,
  47.     ip.item_stock,
  48.   od.fs_order,
  49.   od.non_fs_order,
  50.   od.total_gross_order,
  51.   od.net_order,
  52.   od.fs_qty_sold,
  53.   od.non_fs_qty_sold,
  54.   od.qty_sold,
  55.   od.Total_FS_selling_price,
  56.   od.Total_Non_FS_selling_price,
  57.   od.Avg_Non_FS_selling_price
  58. FROM item_profile ip
  59. LEFT JOIN od ON ip.itemid = od.itemid AND ip.modelid = od.modelid
  60. WHERE shopid = {{shopid}}
  61. AND shopid IN (SELECT shopid FROM auth)
  62. ),
  63.  
  64. traffic AS (
  65.   SELECT pe.itemid, SUM(pv_cd) AS views
  66.   FROM traffic_mart_dws__product_exposure pe
  67.   JOIN A
  68.     ON A.itemid = A.itemid
  69.   WHERE grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
  70.   GROUP BY 1
  71. )
  72.  
  73. SELECT
  74.   A.*,
  75.   Total_FS_selling_price / fs_qty_sold AS Avg_FS_Selling_Price,
  76.   Total_Non_FS_selling_price / non_fs_qty_sold AS Avg_Non_FS_selling_price,
  77.   views,
  78.   1.0000 * total_gross_order / views AS CR
  79. FROM A
  80. LEFT JOIN traffic v
  81. ON a.itemid = v.itemid
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