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,
- alertContactFriendlyName,
- 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,
- 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*/
- 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,
- alertContactFriendlyName,
- 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),
- /*List of All Active Team Integration Alert Contacts*/
- TeamIntegrationAlertContacts AS (
- SELECT
- userID,
- alertContactType,
- alertContactFriendlyName,
- alertContactValue,
- alertContactStatus
- FROM `uptimerobot-001.ur.alertcontacts`
- WHERE alertContactType IN (5,7,11,15,17,18,20) AND alertContactStatus = 2
- ),
- /*List users with number of unique team integrations alertContacts*/
- countOfTeamIntegrationAlertContactsByUser AS (
- SELECT
- userID,
- COUNT(DISTINCT alertContactValue) as uniqueTeamIntegrationsCount
- FROM
- TeamIntegrationAlertContacts
- GROUP BY
- userID
- ),
- /*List of All Users with Mobile,Phone,Team Integration and Email Alert Contacts Count*/
- MobilePhoneTeamItegrationAndEmailAlertContactsCount as (
- SELECT
- u.userID,
- userEmail,
- (CASE
- WHEN uniqueMobileAlertContacts IS NULL THEN 0
- ELSE uniqueMobileAlertContacts
- END) uniqueMobileAlertContactsCount,
- (CASE
- WHEN uniquePhoneAlertContactsCount IS NULL THEN 0
- ELSE uniquePhoneAlertContactsCount
- END) uniquePhoneAlertContactsCount,
- (CASE
- WHEN uniqueEmailAlertContacts IS NULL THEN 0
- ELSE uniqueEmailAlertContacts
- END) uniqueEmailAlertContactsCount,
- (CASE
- WHEN uniqueTeamIntegrationsCount IS NULL THEN 0
- ELSE uniqueTeamIntegrationsCount
- END) uniqueTeamIntegrationsForFREECount,
- (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
- LEFT JOIN
- countOfTeamIntegrationAlertContactsByUser
- ON countOfTeamIntegrationAlertContactsByUser.userID = u.userID
- ),
- /*List of All Users with ()>2 Mobile or > 1 Phone or > 1 Team Integration) and 0 additional Email Alert Contacts*/
- MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts as (
- SELECT
- *
- FROM
- MobilePhoneTeamItegrationAndEmailAlertContactsCount
- WHERE
- (uniqueMobileAlertContactsCount > 2 OR uniquePhoneAlertContactsCount >1 OR uniqueTeamIntegrationsForFREECount >1)
- AND uniqueEmailAlertContactsCount = 0
- ORDER BY
- uniquePhoneAlertContactsCount DESC,
- uniqueMobileAlertContactsCount DESC,
- uniqueTeamIntegrationsForFREECount DESC,
- uniqueEmailAlertContactsCount DESC)
- SELECT
- ac.userID,
- userEmail,
- userIsProPlan,
- alertContactType,
- alertContactFriendlyName,
- STRING_AGG(alertContactFriendlyName,', ' ) OVER (PARTITION BY ac.userID) alertContactFriendlyNameMerged,
- alertContactValue,
- STRING_AGG(alertContactValue,', ' ) OVER (PARTITION BY ac.userID) alertContactValueMerged,
- alertContactStatus,
- uniqueMobileAlertContactsCount,
- uniquePhoneAlertContactsCount,
- uniqueEmailAlertContactsCount,
- uniqueTeamIntegrationsForFREECount
- FROM
- `uptimerobot-001.ur.alertcontacts` ac
- LEFT JOIN
- MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts
- ON ac.userID = MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts.userID
- WHERE
- /*Filter for All Push, Phone, Email, Team Integration Alert Contacts*/
- alertContactType IN (1,5,7,8,11,12,13,14,15,17,18,20)
- AND MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts.userID IS NOT NULL
- ORDER BY
- ac.userID
Add Comment
Please, Sign In to add comment