I have two CTE's: with screen as ( ( SELECT user _ _ pseudo _ _ id
Fantastic news! We've Found the answer you've been seeking!
Question:
I have two CTE's:
with screen as
(
SELECT
user_pseudo_id as user_id,
geo.country as country,
event_timestamp,
CASE
WHEN params1.value.string_value = 'Duas' or params1.value.string_value = 'DuasTopics' THEN 'Duas'
WHEN params1.value.string_value = 'Explore' or params1.value.string_value = 'Topic' THEN 'Explore by Topic'
ELSE params1.value.string_value
END AS screen_name,
FROM
`table` AS base,
UNNEST(event_params) AS params1
WHERE
event_name = 'screen_view'
AND params1.key = 'firebase_screen'
AND event_date between ' 2 0 2 3 1 0 0 1 ' " id="MathJax-Element-35-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231001' and ' 2 0 2 3 1 2 3 1 ' " id="MathJax-Element-36-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231231'
)
,
remove AS (
SELECT
distinct user_pseudo_id
FROM
`table`
where event_name='app_remove'
AND event_date between ' 2 0 2 3 1 0 0 1 ' " id="MathJax-Element-48-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231001' and ' 2 0 2 3 1 2 3 1 ' " id="MathJax-Element-49-Frame" role="presentation" style="font-size: 121%; position: relative;" tabindex="0">'20231231'
)
I want to find out the top 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% users who viewed 'settings' before removing the app.
Related Book For
Systems Analysis And Design
ISBN: 978-1119496489
7th Edition
Authors: Alan Dennis, Barbara Wixom, Roberta M. Roth
Posted Date: