Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH auth AS (
- SELECT DISTINCT st.shopid
- FROM shopee_id_anlys.id_bi_seller_type st
- JOIN shopee_id_bi_team__rm_name_pass rm
- ON rm.rm_name = st.rm_name
- WHERE rm.rm_name IN ({{RM_name}}) AND rm.pass IN ({{Password}})
- ),
- smaller AS (
- SELECT * FROM order_mart__order_item_profile o
- WHERE grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
- AND shopid = {{shopid}}
- AND shopid IN (SELECT shopid FROM auth)
- ),
- od AS (
- SELECT
- itemid, modelid,
- SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.order_fraction,0) END) AS fs_order,
- SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.order_fraction,0) END) AS non_fs_order,
- SUM(COALESCE(o.order_fraction,0)) AS total_gross_order,
- 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,
- SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE(o.amount,0) END) AS fs_qty_sold,
- SUM(CASE WHEN promo_source != 'flash_sale' OR promo_source IS NULL THEN COALESCE(o.amount,0) END) AS non_fs_qty_sold,
- SUM(COALESCE(o.amount,0)) AS qty_sold,
- SUM(CASE WHEN promo_source = 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_FS_selling_price,
- SUM(CASE WHEN promo_source != 'flash_sale' THEN COALESCE((order_price*o.amount),0) END) AS Total_Non_FS_selling_price,
- SUM(order_price*o.amount)/SUM(amount) AS Avg_Non_FS_selling_price
- FROM
- smaller o
- GROUP BY 1,2
- ),
- A AS (
- SELECT
- ip.main_category,
- ip.sub_category,
- ip.level3_category,
- ip.itemid,
- ip.modelid,
- DATE(ip.item_ctime) AS registered_date,
- ip.item_name AS product_name,
- ip.model_name,
- ip.item_price AS current_price,
- CASE WHEN ip.item_price_before_discount != 0 THEN ip.item_price_before_discount ELSE ip.item_price END AS original_price,
- ip.item_stock,
- od.fs_order,
- od.non_fs_order,
- od.total_gross_order,
- od.net_order,
- od.fs_qty_sold,
- od.non_fs_qty_sold,
- od.qty_sold,
- od.Total_FS_selling_price,
- od.Total_Non_FS_selling_price,
- od.Avg_Non_FS_selling_price
- FROM item_profile ip
- LEFT JOIN od ON ip.itemid = od.itemid AND ip.modelid = od.modelid
- WHERE shopid = {{shopid}}
- AND shopid IN (SELECT shopid FROM auth)
- ),
- traffic AS (
- SELECT pe.itemid, SUM(pv_cd) AS views
- FROM traffic_mart_dws__product_exposure pe
- JOIN A
- ON A.itemid = A.itemid
- WHERE grass_date BETWEEN DATE {{START_date}} AND DATE {{END_date}}
- GROUP BY 1
- )
- SELECT
- A.*,
- Total_FS_selling_price / fs_qty_sold AS Avg_FS_Selling_Price,
- Total_Non_FS_selling_price / non_fs_qty_sold AS Avg_Non_FS_selling_price,
- views,
- 1.0000 * total_gross_order / views AS CR
- FROM A
- LEFT JOIN traffic v
- ON a.itemid = v.itemid
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement