SHARE
TWEET

Untitled

a guest May 19th, 2019 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH
  2.   listing_table AS (
  3.   SELECT
  4.   id AS listing_id,
  5.   agent_id,
  6.   type_code
  7.   FROM
  8.     `propertyguru-datalake-v0.propertydb_staging.sg_v_listing_active_ds`
  9.   WHERE DATE(_PARTITIONTIME) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
  10.     AND status_code = 'ACT'
  11.   GROUP BY 1,2,3
  12.   ),
  13.  
  14.   listing_msg_table AS (
  15.   SELECT
  16.   listing_id,
  17.   COUNT(listing_id) as total_msg
  18.   FROM
  19.     `propertyguru-datalake-v0.production_pg_db05_propertydb.contact_history`
  20.     WHERE _fivetran_deleted = false
  21.     AND DATE(created_date) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
  22.   GROUP BY 1
  23.   ORDER BY 2
  24.   ),
  25.  
  26.   impression AS (
  27.   SELECT
  28.     CAST(id AS int64) id,
  29.     COUNT(*) AS impression
  30.   FROM (
  31.     SELECT
  32.       date,
  33.       k.productSKU AS id
  34.     FROM
  35.       `propertyguru-datalake-v0.103434014.ga_sessions_*`,
  36.       UNNEST(hits) AS h,
  37.       UNNEST(h.product) AS k
  38.     WHERE
  39.       h.eventInfo.eventAction = 'productImpression'
  40.       AND _TABLE_SUFFIX BETWEEN
  41.       FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) AND
  42.       FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  43.       )
  44.   GROUP BY id
  45.   ),
  46.    
  47.   lead_view AS (
  48.   SELECT
  49.     listing_id,
  50.     IF(SUM(views) IS NULL, 0, SUM(views)) as listing_views,
  51.     IF(SUM(ViewPhone) IS NULL, 0, SUM(ViewPhone)) as total_Viewphone_lead,
  52.     (IF(SUM(ViewPhone) IS NULL, 0, SUM(ViewPhone)) + IF(SUM(Email) IS NULL, 0, SUM(Email)) + IF(SUM(WhatsApp) IS NULL, 0, SUM(WhatsApp)) + IF(SUM(SMS) IS NULL, 0, SUM(SMS)) + IF(SUM(Call) IS NULL, 0, SUM(Call))) AS total_lead
  53.   FROM (
  54.          (
  55.         SELECT
  56.           listing_id,
  57.           SUM(IF(metric = 'VIEWS',value,0 )) views,
  58.           SUM(IF(metric = 'LEADS'AND communication_channel = 'Email',value,0 )) Email,
  59.           SUM(IF(metric = 'LEADS'AND communication_channel = 'WhatsApp',value,0 )) WhatsApp,
  60.           SUM(IF(metric = 'LEADS'AND communication_channel = 'ViewPhone',value,0 )) ViewPhone,
  61.           SUM(IF(metric = 'LEADS'AND communication_channel = 'SMS',value,0 )) SMS,
  62.           SUM(IF(metric = 'LEADS'AND communication_channel = 'Call',value,0 )) Call
  63.         FROM
  64.           `datamart_v0.sg_fact_leads_views`
  65.         WHERE
  66.           DATE(_PARTITIONTIME) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
  67.         GROUP BY
  68.           1)
  69.     UNION ALL
  70.           (
  71.         SELECT
  72.           listing_id,
  73.           SUM(IF(metric = 'VIEWS',value,0 )) views,
  74.           SUM(IF(metric = 'LEADS' AND communication_channel = 'Email',value,0 )) Email,
  75.           SUM(IF(metric = 'LEADS' AND communication_channel = 'WhatsApp',value,0 )) WhatsApp,
  76.           SUM(IF(metric = 'LEADS' AND communication_channel = 'ViewPhone',value,0 )) ViewPhone,
  77.           SUM(IF(metric = 'LEADS' AND communication_channel = 'SMS',value,0 )) SMS,
  78.           SUM(IF(metric = 'LEADS' AND communication_channel = 'Call',value,0 )) Call
  79.         FROM
  80.           `datamart_v0.sg_fact_leads_views_mobile`
  81.         WHERE
  82.           DATE(_PARTITIONTIME) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
  83.         GROUP BY 1
  84.         )
  85.       )
  86.     GROUP BY 1),
  87.    
  88.   combined_table AS (
  89.    SELECT
  90.     CAST(lt.listing_id AS INT64) AS listing_id,
  91.     CAST(agent_id AS INT64) AS agent_id,
  92.     type_code,
  93.     lv.listing_views as total_view,
  94.     impression.impression as impression,
  95.     total_ViewPhone_lead total_ViewPhone_lead,
  96.     IF(total_msg IS NULL, 0, total_msg) AS total_msg,
  97.     (IF(total_lead IS NULL,0, total_lead)*15 + IF(total_msg IS NULL,0, total_msg)*15 + IF(lv.listing_views IS NULL,0, lv.listing_views)) AS lead_view_score
  98.   FROM listing_table lt
  99.   LEFT JOIN lead_view lv ON lt.listing_id = lv.listing_id
  100.   LEFT JOIN listing_msg_table lm ON lt.listing_id = lm.listing_id
  101.   LEFT JOIN impression ON lt.listing_id = impression.id
  102.   ),
  103.  
  104.   final_table AS (
  105.   SELECT
  106.   listing_id,
  107.   agent_id,
  108.   type_code,
  109.   IF(total_view IS NULL,0, total_view) AS total_view,
  110.   IF(impression IS NULL,0, impression) AS impression,
  111.   IF(total_ViewPhone_lead IS NULL,0, total_ViewPhone_lead) AS total_ViewPhone_lead,
  112.   IF(total_msg IS NULL,0, total_msg) AS total_msg,
  113.   IF(lead_view_score IS NULL,0, lead_view_score) AS lead_view_score
  114.   FROM combined_table
  115.   )
  116.  
  117.   SELECT
  118.   *
  119.   FROM final_table
  120.   WHERE impression >= total_msg
  121.   AND impression >= total_ViewPhone_lead
  122.   AND total_view >= total_msg
  123.   AND total_view >= total_ViewPhone_lead
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