There are two tables. One table has all mobile actions (all pages visited by the users on mobile). The other table has all web actions (all pages visited on web by the users). I am trying to return the percentage of users who only visited mobile, only web, and both. That is, the percentage of users who are only in the mobile table, only in the web table and in both tables. The sum of the percentages should return 1. I am struggling to figure out why my solution is incorrect. Here is the dataset if needed. Thanks in advance.
mobile_table
- user_id (id of the user who visited a given page on mobile, ex: 128)
- page (page visited by that user on mobile, ex: page_5_mobile)
web_table
- user_id (id of the user who visited a given page on web, ex: 1210)
- page (page visited by that user on web, ex: page_1_web)
Here's my attempted solution:
with t1 as (select count(*) as t1_count from mobile_table), t2 as (select count(*) as t2_count from web_table), t3 as (select count(*) as t3_count from web_table join mobile_table on q2_web.user_id = q2_mobile.user_id) select round(cast(t1_count as numeric)*100/(t1_count+t2_count+ t3_count),2) as mobile_usage, round(cast(t2_count as numeric)*100/(t1_count+t2_count+t3_count),2) as web_usage, round(cast(t3_count as numeric)*100/(t1_count+t2_count+t3_count),2) as mobile_and_web_usage from t1, t2, t3 The outcome of my query:
- mobile_usage: 37.06
- web_usage: 25.14
- mobile_and_web_usage: 37.80
The only issue is that the official solution is different:
- mobile_usage: 16
- web_usage: 31
- mobile_and_web_usage: 52
没有评论:
发表评论