I tried to use the inner join however the results returned are all record from user log table matching criteria age 70, howerver to calculate percentage it is also needed that count of user
who has log time > 5 from userlog table
can anyone please provide some guidance as how to retreive percentage using optimized join
Here is the query I tried.
select ux.userid, ux.logtime, count(*) tot from userlog ux Inner join userbasic uo on uo.userid = ux.userid and uo.userage='70' group by ux.userid,ux.logtime;
By providing logtime as > 5 it will only return user who has log time 5. however the expected result is the percentage of user who has log time > 5 and for this I understand firstly we need to get all the users who are in userage 70 and then derived the percentage who are in log time > 5.
I am not sure how in an optimized way i can get the full set and derived the percetage from that.
The expected output is just one column i.e. calculated percentage of user who has log time > 5.
In the above query based on the data provided there are 7 records returned for user with age > 70 however there are 2 user who has log time greater than 5 there from the above query i am able to get the total records however to calculate the percentage i understand what is needed is
count of user log time > 5 / tot user * 100.
from the above query i could get the tot user what is needed is further filter to get count of user with log time > 5. there from the above data the percentage is
2* 100/7 = 29%.
I am thinking there could be a way through inner join to filter further on the final count however not sure if this is correct and could not get it yet.