Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #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
Add Comment
Please, Sign In to add comment