Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH
- listing_table AS (
- SELECT
- id AS listing_id,
- agent_id,
- type_code
- FROM
- `propertyguru-datalake-v0.propertydb_staging.sg_v_listing_active_ds`
- WHERE DATE(_PARTITIONTIME) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
- AND status_code = 'ACT'
- GROUP BY 1,2,3
- ),
- listing_msg_table AS (
- SELECT
- listing_id,
- COUNT(listing_id) as total_msg
- FROM
- `propertyguru-datalake-v0.production_pg_db05_propertydb.contact_history`
- WHERE _fivetran_deleted = false
- AND DATE(created_date) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
- GROUP BY 1
- ORDER BY 2
- ),
- impression AS (
- SELECT
- CAST(id AS int64) id,
- COUNT(*) AS impression
- FROM (
- SELECT
- date,
- k.productSKU AS id
- FROM
- `propertyguru-datalake-v0.103434014.ga_sessions_*`,
- UNNEST(hits) AS h,
- UNNEST(h.product) AS k
- WHERE
- h.eventInfo.eventAction = 'productImpression'
- AND _TABLE_SUFFIX BETWEEN
- FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) AND
- FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
- )
- GROUP BY id
- ),
- lead_view AS (
- SELECT
- listing_id,
- IF(SUM(views) IS NULL, 0, SUM(views)) as listing_views,
- IF(SUM(ViewPhone) IS NULL, 0, SUM(ViewPhone)) as total_Viewphone_lead,
- (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
- FROM (
- (
- SELECT
- listing_id,
- SUM(IF(metric = 'VIEWS',value,0 )) views,
- SUM(IF(metric = 'LEADS'AND communication_channel = 'Email',value,0 )) Email,
- SUM(IF(metric = 'LEADS'AND communication_channel = 'WhatsApp',value,0 )) WhatsApp,
- SUM(IF(metric = 'LEADS'AND communication_channel = 'ViewPhone',value,0 )) ViewPhone,
- SUM(IF(metric = 'LEADS'AND communication_channel = 'SMS',value,0 )) SMS,
- SUM(IF(metric = 'LEADS'AND communication_channel = 'Call',value,0 )) Call
- FROM
- `datamart_v0.sg_fact_leads_views`
- WHERE
- DATE(_PARTITIONTIME) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
- GROUP BY
- 1)
- UNION ALL
- (
- SELECT
- listing_id,
- SUM(IF(metric = 'VIEWS',value,0 )) views,
- SUM(IF(metric = 'LEADS' AND communication_channel = 'Email',value,0 )) Email,
- SUM(IF(metric = 'LEADS' AND communication_channel = 'WhatsApp',value,0 )) WhatsApp,
- SUM(IF(metric = 'LEADS' AND communication_channel = 'ViewPhone',value,0 )) ViewPhone,
- SUM(IF(metric = 'LEADS' AND communication_channel = 'SMS',value,0 )) SMS,
- SUM(IF(metric = 'LEADS' AND communication_channel = 'Call',value,0 )) Call
- FROM
- `datamart_v0.sg_fact_leads_views_mobile`
- WHERE
- DATE(_PARTITIONTIME) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 MONTH)
- GROUP BY 1
- )
- )
- GROUP BY 1),
- combined_table AS (
- SELECT
- CAST(lt.listing_id AS INT64) AS listing_id,
- CAST(agent_id AS INT64) AS agent_id,
- type_code,
- lv.listing_views as total_view,
- impression.impression as impression,
- total_ViewPhone_lead total_ViewPhone_lead,
- IF(total_msg IS NULL, 0, total_msg) AS total_msg,
- (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
- FROM listing_table lt
- LEFT JOIN lead_view lv ON lt.listing_id = lv.listing_id
- LEFT JOIN listing_msg_table lm ON lt.listing_id = lm.listing_id
- LEFT JOIN impression ON lt.listing_id = impression.id
- ),
- final_table AS (
- SELECT
- listing_id,
- agent_id,
- type_code,
- IF(total_view IS NULL,0, total_view) AS total_view,
- IF(impression IS NULL,0, impression) AS impression,
- IF(total_ViewPhone_lead IS NULL,0, total_ViewPhone_lead) AS total_ViewPhone_lead,
- IF(total_msg IS NULL,0, total_msg) AS total_msg,
- IF(lead_view_score IS NULL,0, lead_view_score) AS lead_view_score
- FROM combined_table
- )
- SELECT
- *
- FROM final_table
- WHERE impression >= total_msg
- AND impression >= total_ViewPhone_lead
- AND total_view >= total_msg
- AND total_view >= total_ViewPhone_lead
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement