Advertisement
Guest User

Untitled

a guest
Feb 27th, 2020
201
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.80 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement