1WITH ranked_table AS (
2SELECT *, Row_number() OVER (partition BY user_pseudo_id, event_name ORDER BY event_timestamp ) rank_
3FROM `tc-da-1.turing_data_analytics.raw_events`
4ORDER BY user_pseudo_id),
5
6unique_events AS (
7SELECT *
8FROM ranked_table
9WHERE rank_=1)
10
11SELECT
12RANK () OVER (ORDER BY COUNT(user_pseudo_id) DESC) AS event_order,
13event_name,
14
15COUNT(CASE WHEN country = 'United States' THEN event_name ELSE NULL END) AS united_states_events,
16COUNT(CASE WHEN country = 'India' THEN event_name ELSE NULL END) AS india_events,
17COUNT(CASE WHEN country = 'Canada' THEN event_name ELSE NULL END) AS canada_events,
18
19CONCAT(ROUND(COUNT(
20event_name)/(SELECT COUNT(event_name) FROM unique_events WHERE event_name = 'page_view') * 100, 2), '%') AS full_perc_drop,
21
22CONCAT(ROUND(COUNT(
23CASE WHEN country = 'United States' THEN event_name ELSE NULL END)/(SELECT COUNT(event_name) FROM unique_events WHERE event_name = 'page_view' AND country = 'United States') * 100, 2), '%') AS united_states_perc_drop,
24
25CONCAT(ROUND(COUNT(
26CASE WHEN country = 'India' THEN event_name ELSE NULL END)/(SELECT COUNT(event_name) FROM unique_events WHERE event_name = 'page_view' AND country = 'India') * 100, 2), '%') AS india_perc_drop,
27
28CONCAT(ROUND(COUNT(
29CASE WHEN country = 'Canada' THEN event_name ELSE NULL END)/(SELECT COUNT(event_name) FROM unique_events WHERE event_name = 'page_view' AND country = 'Canada') * 100, 2), '%') AS canada_perc_drop
30
31FROM unique_events
32WHERE event_name = 'page_view' OR event_name = 'scroll' OR event_name = 'view_item' OR event_name = 'add_to_cart' OR event_name = 'begin_checkout' OR event_name = 'purchase'
33GROUP BY event_name
34ORDER BY COUNT(event_name) DESC