How to avoid multiple SELECT when counting all and selected rows-CodePudding

I need to find the count of all distinct users and count of all distinct users in last 30 days. I am using the below SQL,

SELECT   total_active_users
        , active_users_last_30_days
FROM
  (SELECT  COUNT(DISTINCT user_pseudo_id) as total_active_users
  FROM    `myDbSet.events_*`
  WHERE   event_name = 'screen_view'),
  (SELECT  COUNT(DISTINCT user_pseudo_id) as active_users_last_30_days
  FROM    `myDbSet.events_*`
  WHERE   event_name = 'screen_view'
          AND _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))

It is working fine but I am thinking to avoid the 2 SELECT.

CodePudding user response：

You may apply your condition inside the count function as the following:

SELECT COUNT(DISTINCT user_pseudo_id) as total_active_users,
       COUNT(
              DISTINCT IF(_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)) 
                                        AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),
                  user_pseudo_id, NULL)
             ) as active_users_last_30_days
FROM `myDbSet.events_*`
WHERE event_name = 'screen_view'