gistfile1.txt

#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 (
  SELECT search_request_pk, COUNTIF(clicked IS TRUE) as num_clicks
  FROM clicks
  GROUP BY 1
  HAVING num_clicks > 0
)

SELECT c.search_request_pk, c.service_pk, clicked FROM search_atleast_one_click s INNER JOIN clicks c ON c.search_request_pk = s.search_request_pk WHERE clicked IS TRUE