CODEWITHSUNDEEP
mysqlsqlaggregate-functions
sown
sown

Reputation: 23

calculating percentage on multiple sql tables

There are 2 tables mobile and web

Table name: Mobile

user_id  date        page
1        19.1.2020   pg1mob
2        13.12.2019  pg2mob 
3        14.2.2017   pg456mob

Table name: web

user_id  date        page
15       20.1.2020   pg3web
4        23.12.2019  pg5web 
3        14.2.2017   pg652web

I need to calculate percentage of users who only visited : mobile, 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 should be 1.

I tried using union but I am unsure if it is the right way. How can I calculate the percentage?

Upvotes: 0

Views: 210

Answers (2)

forpas
forpas

Reputation: 164099

First get all the rows, including a column indicating where the row comes from, of both tables with UNION ALL.
Then group by user to get if the user is in only 1 table or both.
Finally use conditional aggregation to get the percentages:

select 
  avg(type = 1) Mobile_only,
  avg(type = 2) web_only,
  avg(type = 3) Mobile_and_web
from (
  select user_id, sum(type) type
  from (
    select distinct user_id, 1 type from Mobile      
    union all
    select distinct user_id, 2 type from web      
  ) t 
  group by t.user_id
) t

See the demo.
Results:

| Mobile_only | web_only | Mobile_and_web |
| ----------- | -------- | -------------- |
| 0.4         | 0.4      | 0.2            |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would approach this using union all and aggregation. This starts by getting flag information per user:

select user_id, max(is_mobile) as is_mobile, max(is_web_as is_web)
from ((select distinct user_id, 1 as is_mobile, 0 as is_web
       from mobile
      ) union all
      (select distinct user_id, 0, 1
       from web
      )
     ) u
group by user_id;

Then aggregate this further:

select sum(is_mobile) as num_mobile,
       sum(is_web) as num_web,
       sum(is_mobile * is_web) as num_mobile_and_web,
       avg(is_mobile * (1 - is_web)) as mobile_only_rate,
       avg(is_web * (1 - is_mobile)) as web_only_rate,
       avg(is_mobile * is_web) as mobile_and_web_rate
from (select user_id, max(is_mobile) as is_mobile, max(is_web_as is_web)
      from ((select distinct user_id, 1 as is_mobile, 0 as is_web
             from mobile
            ) union all
            (select distinct user_id, 0, 1
             from web
            )
           ) u
      group by user_id
     ) u;

Upvotes: 0

Related Questions