gistfile1.txt

#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)
FROM max_scroll_per_search
GROUP BY 1
ORDER BY 2 DESC