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

Question

I have 3 tables like this

create table Users (id serial primary key, country varchar(100) not null);

create table tweets(id serial 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');

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?

question from:https://stackoverflow.com/questions/65838831/how-to-find-percentage-within-a-group-in-sql-that-has-inner-join-as-subquery

Answer 1

One way to achieve the results you want is to check in a derived table whether a user has made any tweets about #food; then you can LEFT JOIN that table to Users and Logins to determine the average number of users from each country that have logged in from mobile and tweeted about food:

SELECT u.country,
       AVG(COALESCE(t.tfood, 0) AND COALESCE(l.client, '') LIKE '%mobile-%') * 100 AS tweet_users
FROM Users u
LEFT JOIN Logins l ON l.user_id = u.id
LEFT JOIN (
  SELECT user_id, MAX(text LIKE '%#food%') AS tfood
  FROM tweets
  GROUP BY user_id
) t ON t.user_id = u.id
GROUP BY u.country

Output:

country     tweet_users
China       100.0000
India       0.0000
Japan       66.6667
Moroco      50.0000

If you don't want countries with no users that meet the criteria, just add HAVING tweet_users > 0 to the end:

SELECT u.country,
       AVG(COALESCE(t.tfood, 0) AND COALESCE(l.client, '') LIKE '%mobile-%') * 100 AS tweet_users
FROM Users u
LEFT JOIN Logins l ON l.user_id = u.id
LEFT JOIN (
  SELECT user_id, MAX(text LIKE '%#food%') AS tfood
  FROM tweets
  GROUP BY user_id
) t ON t.user_id = u.id
GROUP BY u.country
HAVING tweet_users > 0

Demo on dbfiddle

Note this code takes advantage of the fact that in a numeric context, MySQL treats boolean expressions as 1 (true) or 0 (false).

Note that if a user might have multiple entries in the Logins table, you need to make a derived table from that too:

SELECT u.country,
       AVG(COALESCE(t.tfood, 0) AND COALESCE(l.mclient, 0)) * 100 AS tweet_users
FROM Users u
LEFT JOIN (
  SELECT user_id, MAX(client LIKE '%mobile-%') AS mclient
  FROM Logins
  GROUP BY user_id
) l ON l.user_id = u.id
LEFT JOIN (
  SELECT user_id, MAX(text LIKE '%#food%') AS tfood
  FROM tweets
  GROUP BY user_id
) t ON t.user_id = u.id
GROUP BY u.country

Demo on dbfiddle