Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH
- /*List of All Mobile App Alert Contacts*/
- MobileAlertContacts AS (
- SELECT
- userID,
- alertContactType,
- alertContactValue,
- alertContactStatus
- FROM `uptimerobot-001.ur.alertcontacts`
- WHERE alertContactType IN (12,13)
- ),
- /*Number of Unique Mobile App Alert Contacts per user*/
- CountOfUniqueMobileAppAlertContactsPerUser AS(
- SELECT
- userID,
- count(DISTINCT alertContactValue) uniqueMobileAlertContacts
- FROM MobileAlertContacts
- GROUP BY
- userID
- ORDER BY
- uniqueMobileAlertContacts DESC),
- /*List of Phone Alert Contacts*/
- phoneAlertContacts as(
- SELECT
- userID,
- alertContactID,
- alertContactType,
- 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*/
- CountOfUniquePhoneAlertContactsPerUser as (
- SELECT
- userID,
- COUNT(DISTINCT alertContactValueLastChar) uniquePhoneAlertContactsCount
- FROM
- phoneAlertContacts
- GROUP BY
- userID
- ),
- /*List of All Active Not Default E-mail Alert Contacts*/
- NotDefaultEmailAlertContacts AS (
- SELECT
- userID,
- alertContactType,
- alertContactValue,
- alertContactStatus
- FROM `uptimerobot-001.ur.alertcontacts`
- WHERE alertContactType = 2 AND alertContactStatus = 2
- ),
- /*Number of Active Unique E-mail Alert Contacts per user*/
- CountOfUniqueEmailAlertContactsPerUser AS(
- SELECT
- userID,
- count(DISTINCT alertContactValue) uniqueEmailAlertContacts
- FROM NotDefaultEmailAlertContacts
- GROUP BY userID
- ORDER BY uniqueEmailAlertContacts DESC),
- preFinalData as (
- SELECT
- u.userID,
- userEmail,
- (CASE
- WHEN uniqueMobileAlertContacts IS NULL THEN 0
- ELSE uniqueEmailAlertContacts
- END) uniqueMobileAlertContacts,
- (CASE
- WHEN uniquePhoneAlertContactsCount IS NULL THEN 0
- ELSE uniquePhoneAlertContactsCount
- END) uniquePhoneAlertContactsCount,
- (CASE
- WHEN uniqueEmailAlertContacts IS NULL THEN 0
- ELSE uniqueEmailAlertContacts
- END) uniqueEmailAlertContacts,
- (CASE
- WHEN userIsProPlan IS NULL THEN 'FREE'
- WHEN userIsProPlan = 0 THEN 'FREE'
- WHEN userIsProPlan = 1 THEN 'PRO'
- ELSE 'WTF'
- END) userIsProPlan
- FROM
- `uptimerobot-001.ur.users` u
- LEFT JOIN
- CountOfUniqueMobileAppAlertContactsPerUser
- ON CountOfUniqueMobileAppAlertContactsPerUser.userID = u.userID
- LEFT JOIN
- CountOfUniquePhoneAlertContactsPerUser
- ON CountOfUniquePhoneAlertContactsPerUser.userID = u.userID
- LEFT JOIN
- CountOfUniqueEmailAlertContactsPerUser
- ON CountOfUniqueEmailAlertContactsPerUser.userID = u.userID)
- SELECT
- *
- FROM
- preFinalData
- WHERE
- (uniqueMobileAlertContacts > 2 OR uniquePhoneAlertContactsCount >2)
- AND uniqueEmailAlertContacts = 0
- ORDER BY
- uniqueMobileAlertContacts DESC,
- uniquePhoneAlertContactsCount DESC,
- uniqueEmailAlertContacts DESC
Add Comment
Please, Sign In to add comment