I have two CTE's:

with screen as

($($

SELECT

user_$\_$pseudo_$\_$id as user_$\_$id,$,$

geo.country as country,

event_$\_$timestamp,

CASE

WHEN params1.$1.$value.string_$\_$value =$=$ 'Duas' or params1.$1.$value.string_$\_$value =$=$ 'DuasTopics' THEN 'Duas'

WHEN params1.$1.$value.string_$\_$value =$=$ 'Explore' or params1.$1.$value.string_$\_$value =$=$ 'Topic' THEN 'Explore by Topic'

ELSE params1.$1.$value.string_$\_$value

END AS screen_$\_$name,

FROM

`$`$table`$`$ AS base,

UNNEST($($event_$\_$params)$)$ AS params1$1$

WHERE

event_$\_$name =$=$ 'screen_$\_$view'

AND params1.$1.$key =$=$ 'firebase_$\_$screen'

AND event_$\_$date between '20231001'" id="MathJax-Element-35-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231001'$\text{'}20231001\text{'}$ and '20231231'" id="MathJax-Element-36-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231231'$\text{'}20231231\text{'}$

)$)$

,$,$

remove AS ($($

SELECT

distinct user_$\_$pseudo_$\_$id

FROM

`$`$table`$`$

where event_$\_$name=$=$'app_$\_$remove'

AND event_$\_$date between '20231001'" id="MathJax-Element-48-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231001'$\text{'}20231001\text{'}$ and '20231231'" id="MathJax-Element-49-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231231'$\text{'}20231231\text{'}$

)$)$

I want to find out the top 10$10$ countries where screen_$\_$name 'settings' was the last screen viewed before app removal, along with the %$\%$ of associated users.

For example Malaysia has 70%$70\%$ users who viewed 'settings' before removing the app.

Answer rating: 100% (QA)

Heres the SQL query to find the top 10 countries where settings was the last screen viewed before ap... View the full answer