What percent of users have ever viewed the user profile page?-CodePudding

I have two tables, users(email_address, first_name, id, last_name) and events(event_id, event_name, event_time, platform, user_id). I want to calculate the percentage of users that have ever viewed the user profile page. I have this code and don't how to continue.

SELECT u.id as user_id, COUNT(e.event_id) as num_views
  FROM 
    dsv1069.users u 
  LEFT OUTER JOIN 
    dsv1069.events e 
  ON 
    e.user_id=u.id 
  WHERE 
    e.event_name = 'view_user_profile'
  GROUP BY 
    u.id

CodePudding user response：

For each user use EXISTS to get a 1 for true or 0 for false, as a result for viewing the user profile page and get the average of these 1s and 0s with AVG() aggregate function:

SELECT 100 * AVG(
               EXISTS (
                 SELECT *
                 FROM dsv1069.events e
                 WHERE e.user_id = u.id AND e.event_name = 'view_user_profile'
               )
             ) AS percentage
FROM dsv1069.users u;

CodePudding user response：

The fastest way to calculate percentage is to do 2 separate count queries

select 
   100*( SELECT  count(distinct user_id) 
           FROM dsv1069.events e 
          WHERE e.event_name = 'view_user_profile')
   / ( SELECT count(*) FROM dsv1069.users) views_percentage

In case of 0 in select count(*) from dsv1069.users this calculation returns null