WITH
/*List of Phone Alert Contacts*/
  phoneAlertContacts as(
    SELECT
      userID,
      alertContactID,
      alertContactType,
      alertContactFriendlyName,
      alertContactValue,
      RIGHT (alertContactValue,3) alertContactValueLastChar,
      alertContactStatus

    FROM
      `uptimerobot-001.ur.alertcontacts`

    WHERE
      alertContactType IN (1,8,14)

  ),

/*Number of Unique Mobile App Alert Contacts per user*/
  phoneAlertContactsCount as (
  SELECT
    userID,
    COUNT(DISTINCT alertContactValueLastChar) uniquePhoneAlertContactsCount
  FROM
    phoneAlertContacts
  GROUP BY
    userID
  )

SELECT
  *

FROM
  phoneAlertContactsCount

ORDER BY
  uniquePhoneAlertContactsCount DESC