How to find percentage within a group in sql that has inner join as subquery?

I have 3 tables like this

Users  id  country  1   Japan  2   Moroco  3   Japan  4   India  5   India  6   Japan  7   Moroco  8   China    tweets  id  user_id text  733 1   I love #food  734 1   I love food  735 2   I love #food  736 5   I love food  737 6   I love #food  738 3   I love #food  739 8   I love #food    Logins  user_id client  1   mobile-ios  2   mobile-ios  3   mobile-ios  4   web  8   mobile-ios  

I need to find percentage of users from each country whose users have used '#food' in their tweets and the other condition is that user should have logged in using 'mobile' device

I have written the following query so far -

select t.country, count(t.country) as tweet_users  from   (select Mobile_User_Tweets.user_id, U.country from Users as U  inner join  (select distinct user_id from tweets   where text like '%#food%'   and user_id in (select distinct user_id                                   from Logins                                  where client like '%mobile-%')) as Mobile_User_Tweets  on U.id = Mobile_User_Tweets.user_id) as t  group by t.country ;  

This gives the number of users from a country that have user #food in their tweets

Result below -

country tweet_users  Japan   2  Moroco  1  China   1  

I want the following result -

 country    tweet_users    Japan   66.67      -------------> (2 out of 3 users from Japan)     Moroco  50         -------------> (1 out of 2 users from Moroco)    China   100        -------------> (1 out of 1 user from China)  

I tried number of different queries to find the percentage but haven't been able to get the result? Can some one help me with this?

Queries to create the above schema -

create table Users (id int primary key, country varchar(100) not null);    create table tweets(id int primary key, user_id int, text varchar(100) not null, CONSTRAINT FK_TWEETSUSERS FOREIGN KEY (user_id)      REFERENCES Users(id));    create table Logins(user_id int, client varchar(100),  CONSTRAINT FK_LOGIN_USERS FOREIGN KEY (user_id)      REFERENCES Users(id));                                                                                     insert into Users  values  (1,'Japan'),  (2, 'Moroco'),  (3,'Japan'),  (4,'India'),  (5,'India'),  (6,'Japan'),  (7,'Moroco'),  (8,'China');                         insert into tweets  values  (733,1,'I love #food'),  (734,1,'I love food'),  (735,2,'I love #food'),  (736,5,'I love food'),  (737,6,'I love #food'),  (738,3,'I love #food'),  (739,8,'I love #food');                               insert into Logins  values  (1,'mobile-ios'),  (2,'mobile-ios'),  (3,'mobile-ios'),  (4,'web'),  (8,'mobile-ios');                                                                    

https://stackoverflow.com/questions/65838831/how-to-find-percentage-within-a-group-in-sql-that-has-inner-join-as-subquery January 22, 2021 at 11:13AM