gistfile1.txt

#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 (
SELECT device_category, result_position, num_conv*100/total_conv as perc_total_conv
FROM agg1 a, (SELECT COUNT(*) as total_conv FROM sandbox.ir_ranking_conversation_td_apr13_18 WHERE one_plus_conversation IS TRUE) b
)

SELECT * FROM agg2 ORDER BY 3 DESC