I am using Bigquery.

I am trying to find bounce percentage for each app version. A bounced user is defined as a user who first launched the app in the selected date range, then removed the app $($in the selected date range$)$ after using the app only once, i$.$e$.$ a single session.

Here is my code:

with launched as $($

select distinct user$\_$pseudo$\_$id as total$\_$distinct$\_$users

FROM $`$rayn$-$deen$-$app.analytics$\_317927526.$events$\_*`$

where

user$\_$pseudo$\_$id in $($

SELECT distinct user$\_$pseudo$\_$id

FROM $`$rayn$-$deen$-$app.analytics$\_317927526.$events$\_*`$

WHERE

event$\_$name $=$ 'first$\_$open'

AND event$\_$date

BETWEEN

@DS$\_$START$\_$DATE AND @DS$\_$END$\_$DATE

$))$

$,$

demo$\_$launched as

$($SELECT

user$\_$pseudo$\_$id$,$

$--$country

$--$operating$\_$system,

$--$device$\_$brand,

$--$device$\_$model,

$--$os$\_$with$\_$version,

app$\_$info$\_$version

$--$traffic$\_$source$\_$name

$--$language

FROM rayn$-$deen$-$app.churn$\_$analysis.user$\_$demographics

where user$\_$pseudo$\_$id in $($select $*$ from launched$)$

$)$

$,$

removed as $($

select distinct user$\_$pseudo$\_$id as total$\_$distinct$\_$users

FROM $`$rayn$-$deen$-$app.analytics$\_317927526.$events$\_*`$

WHERE user$\_$pseudo$\_$id IN $($

SELECT distinct user$\_$pseudo$\_$id

FROM $`$rayn$-$deen$-$app.analytics$\_317927526.$events$\_*`$

WHERE event$\_$name $=$ "app$\_$remove"

AND event$\_$date

BETWEEN

@DS$\_$START$\_$DATE AND @DS$\_$END$\_$DATE

$)$

AND event$\_$name $=$ 'first$\_$open'

AND event$\_$date

BETWEEN

@DS$\_$START$\_$DATE AND @DS$\_$END$\_$DATE

$)$

$,$

counts as$($

select user$\_$pseudo$\_$id$,$ count$($event$\_$name$)$ as event$\_$countss

from $`$rayn$-$deen$-$app.analytics$\_317927526.$events$\_*`$

where event$\_$name$=$'session$\_$start'

and user$\_$pseudo$\_$id in $($select $*$ from removed$)$

group by user$\_$pseudo$\_$id

$)$

$,$

dataset as

$($select distinct user$\_$pseudo$\_$id from counts where event$\_$countss$=1)$

$,$

demo$\_$bounced as

$($SELECT

user$\_$pseudo$\_$id$,$

$--$country

$--$operating$\_$system,

$--$device$\_$brand,

$--$device$\_$model,

$--$os$\_$with$\_$version,

app$\_$info$\_$version

$--$traffic$\_$source$\_$name

$--$language

FROM rayn$-$deen$-$app.churn$\_$analysis.user$\_$demographics

where user$\_$pseudo$\_$id in $($select $*$ from dataset$)$

$)$

$,$

group$\_$launched as $($

select app$\_$info$\_$version $,$count$($distinct user$\_$pseudo$\_$id$)$ as launched$\_$users

from demo$\_$launched

group by app$\_$info$\_$version$)$

$,$

group$\_$bounced as $($

select app$\_$info$\_$version $,$count$($distinct user$\_$pseudo$\_$id$)$ as bounced$\_$users

from demo$\_$bounced

group by app$\_$info$\_$version

order by bounced$\_$users desc

$--$limit $10$

$)$

$,$

final as $($

select group$\_$launched.app$\_$info$\_$version, group$\_$bounced.bounced$\_$users $,$round$(($bounced$\_$users$/$launched$\_$users$)*100)$ as percentage$\_$bounced

from group$\_$launched

right join group$\_$bounced

on group$\_$launched.app$\_$info$\_$version$=$group$\_$bounced.app$\_$info$\_$version

$)$

select $*$ from final

where app$\_$info$\_$version$<>$'null'

order by final.bounced$\_$users desc

$-------------------------$

I wanted to get this checked, because it is producing some unusual results.