preetamtt

WITH clicks AS (
  SELECT ir.*, service_page_viewed as clicked
  FROM `sandbox.ir_ranking_training_ds_apr17_18` ir
  INNER JOIN `instant_results.search_results` s ON CAST(ir.service_pk AS INT64) = s.service_pk AND CAST(ir.search_request_pk as INT64) = s.search_request_pk
),

search_atleast_one_conv AS (
  SELECT search_request_pk, COUNTIF(one_plus_conversation IS TRUE) as num_conv
  FROM clicks
  GROUP BY 1

preetamtt

WITH clicks AS (
  SELECT ir.*, service_page_viewed as clicked
  FROM `sandbox.ir_ranking_training_ds_apr17_18` ir
  INNER JOIN `instant_results.search_results` s ON CAST(ir.service_pk AS INT64) = s.service_pk AND CAST(ir.search_request_pk as INT64) = s.search_request_pk
),

search_atleast_one_quote AS (
  SELECT search_request_pk, COUNTIF(quote_pk IS NOT NULL) as num_quotes
  FROM clicks
  GROUP BY 1

preetamtt

#standardsql

-- 590024 rows, View -> service page view
WITH clicks AS (
  SELECT ir.*, service_page_viewed as clicked
  FROM `sandbox.ir_ranking_training_ds_apr17_18` ir
  INNER JOIN `instant_results.search_results` s ON CAST(ir.service_pk AS INT64) = s.service_pk AND CAST(ir.search_request_pk as INT64) = s.search_request_pk
),

search_atleast_one_click AS (

preetamtt

#standardSQL

SELECT 
       id.user as user_id_events,
       visitor_pk,
       time,
       referer.referer as referer,
       user_agent.device_category as device_category,
       native_app.name as native_app_name,
       native_app.version as native_app_version,

preetamtt

#standardsql

WITH max_scroll_per_search AS(
  SELECT DISTINCT ir.search_request_pk, 
         FIRST_VALUE(result_position) OVER (PARTITION BY ir.search_request_pk ORDER BY result_position DESC) as max_position_viewed
  FROM sandbox.ir_ranking_training_ds_apr17_18 ir
  INNER JOIN instant_results.search_results s ON CAST(s.search_request_pk AS STRING) = ir.search_request_pk AND s.service_pk = ir.service_pk
)

SELECT max_position_viewed, COUNT(DISTINCT search_request_pk)

preetamtt

SELECT COUNT(DISTINCT search_request_pk) as total_contacts FROM sandbox.ir_ranking_training_ds_apr17_18

preetamtt

#standardSQL

SELECT 
       id.user as user_id_events,
       visitor_pk,
       time,
       referer.referer as referer,
       user_agent.device_category as device_category,
       native_app.name as native_app_name,
       native_app.version as native_app_version,

preetamtt

#standardsql

WITH agg1 AS(
  SELECT device_category, result_position, COUNT(*) as num_contacts
  FROM sandbox.ir_ranking_contacts_td_apr17_18
  WHERE quote_pk IS NOT NULL AND quote_pk > 0
  GROUP BY 1,2
),

agg2 AS (

preetamtt

#standardsql

WITH agg1 AS(
  SELECT device_category, result_position, COUNT(*) as num_conv
  FROM sandbox.ir_ranking_conversation_td_apr13_18
  WHERE one_plus_conversation IS TRUE
  GROUP BY 1,2
),

agg2 AS (

preetamtt

SELECT
  COUNTIF(customer_interaction_count_3d >= 2 AND hired_time IS NOT NULL)/COUNTIF(customer_interaction_count_3d >= 2) AS hire_given_twoplus_interactions
  , COUNTIF(customer_interaction_count_after_pro_response_to_first_contact_3d >= 1 AND hired_time IS NOT NULL)/COUNTIF(customer_interaction_count_after_pro_response_to_first_contact_3d >= 1) AS hire_given_oneplus_conversation
FROM a.contacts
WHERE TIMESTAMP_TRUNC(first_customer_contact_time, MONTH) = '2018-01-01 00:00:00'