Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH
- /*List of all successful subscription payments - Alltime*/
- SuccessSubsPayment AS(
- SELECT
- userID,
- paymentDateTime,
- paymentPeriod,
- productID
- FROM `uptimerobot-001.ur.payments`
- WHERE
- paymentType = 2
- AND paymentStatus = 1
- ),
- /*Last successful subscription payments by user*/
- LastSuccessSubsPayment AS(
- SELECT
- p1.userID,
- p1.paymentDateTime as lastPaymentDateTime,
- p1.paymentPeriod as lastPaymentPeriod,
- p1.productID as lastPaymentProductID
- FROM SuccessSubsPayment p1
- LEFT JOIN SuccessSubsPayment p2
- ON (p1.userID = p2.userID AND p1.paymentDateTime < p2.paymentDateTime)
- WHERE
- p2.userID IS NULL
- ),
- /*added userType - Current / Churned Customer*/
- LastSubsPaymentUsersType AS (
- SELECT
- userID,
- lastPaymentDateTime,
- lastPaymentPeriod,
- (CASE
- WHEN (DATE_ADD(DATETIME(lastPaymentDateTime), INTERVAL lastPaymentPeriod MONTH) >= CURRENT_DATE()) THEN 'CurrentCustomer'
- ELSE 'ChurnedCustomer'
- END) AS customerType,
- lastPaymentProductID
- FROM LastSuccessSubsPayment
- ),
- /*added productName & ProductValue*/
- LastSubsPaymentUsersTypeProduct AS (
- SELECT
- lsput.userID,
- lsput.lastPaymentDateTime,
- lsput.lastPaymentPeriod,
- lsput.customerType,
- lsput.lastPaymentProductID,
- pr.productName,
- pr.productValue
- FROM
- LastSubsPaymentUsersType as lsput
- LEFT JOIN `uptimerobot-001.ur.products` as pr
- ON pr.productID = lsput.lastPaymentProductID
- ),
- /*Deduplication of multiple last successful payments from the same user - some edge cases*/
- LastSuccessSubsPaymentUserTypeProduct AS (
- SELECT
- userID,
- MAX(lastPaymentDateTime) as lastPaymentDateTime,
- MAX(lastPaymentPeriod) as lastPaymentPeriod,
- MAX(customerType) as customerType,
- MAX(lastPaymentProductID) as lastPaymentProductID,
- MAX(productName) as lastPaymentProductName,
- MAX(productValue) as lastPaymentProductValue
- FROM LastSubsPaymentUsersTypeProduct
- GROUP BY userID
- ORDER BY userID DESC
- ),
- /*List of All Active Team Integration Alert Contacts*/
- TeamIntegrationAlertContacts AS (
- SELECT
- userID,
- alertContactType,
- 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 Active Not Default E-mail Alert Contacts*/
- NotDefaultEmailAlertContacts AS (
- SELECT
- userID,
- alertContactType,
- alertContactValue,
- alertContactStatus,
- rand() as random
- 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 users with the count of active unique not default Email Alert Contacts by user type - FREE / Current / Churned Customer & productName & productValue*/
- EmailAlertContacts AS(
- SELECT
- CountOfUniqueEmailAlertContactsPerUser.userID,
- uniqueEmailAlertContacts,
- (CASE
- WHEN customerType IS NULL THEN 'FREE'
- ELSE customerType
- END) as customerType,
- (CASE
- WHEN lastPaymentProductName IS NULL THEN 'FREE'
- ELSE lastPaymentProductName
- END) as lastPaymentProductName,
- (CASE
- WHEN lastPaymentProductValue IS NULL THEN 0
- ELSE lastPaymentProductValue
- END) as lastPaymentProductValue
- FROM CountOfUniqueEmailAlertContactsPerUser
- LEFT JOIN LastSuccessSubsPaymentUserTypeProduct
- on CountOfUniqueEmailAlertContactsPerUser.userID = LastSuccessSubsPaymentUserTypeProduct.userID
- ),
- /*Number of Users with active Not Default Email AlertContact by customer type and lastPaymentProduct*/
- /*
- SELECT
- customerType,
- lastPaymentProductName,
- count(userID) as userCount
- FROM EmailAlertContacts
- GROUP BY
- customerType,
- lastPaymentProductName,
- lastPaymentProductValue
- ORDER BY
- customerType,
- lastPaymentProductValue*/
- finalData as (SELECT
- NotDefaultEmailAlertContacts.userID,
- MD5(CAST(NotDefaultEmailAlertContacts.userID as string)) hashedUserID,
- DENSE_RANK() OVER (PARTITION BY customerType ORDER BY MD5(CAST(NotDefaultEmailAlertContacts.userID as string))) as rank,
- (CASE
- WHEN
- userEmail IS NULL THEN '_onlyDefaultEmailAlertContact'
- ELSE userEmail
- END) userEmailDefault,
- alertContactType,
- alertContactValue,
- (CASE
- WHEN alertContactValue = userEmail THEN '1'
- WHEN alertContactValue <> userEmail THEN '0'
- ELSE 'WTF'
- END
- ) isDefaultAlertContact,
- alertContactStatus,
- uniqueEmailAlertContacts,
- (CASE
- WHEN uniqueEmailAlertContacts > 6 THEN '>6uniqueEmailAlertContacts'
- WHEN uniqueEmailAlertContacts <= 6 THEN '<=6uniqueEmailAlertContacts'
- ELSE 'WTF'
- END) emailAlertContactsBucket,
- (CASE
- WHEN customerType IS NULL THEN 'FREE'
- ELSE customerType
- END) as customerType,
- (CASE
- WHEN lastPaymentProductName IS NULL THEN 'FREE'
- ELSE lastPaymentProductName
- END) as lastPaymentProductName,
- (CASE
- WHEN lastPaymentProductValue IS NULL THEN 0
- ELSE lastPaymentProductValue
- END) as lastPaymentProductValue,
- (CASE
- WHEN uniqueTeamIntegrationsCount IS NULL THEN 0
- ELSE uniqueTeamIntegrationsCount
- END) uniqueTeamIntegrationsCount
- FROM
- NotDefaultEmailAlertContacts
- LEFT JOIN
- CountOfUniqueEmailAlertContactsPerUser
- ON NotDefaultEmailAlertContacts.userID = CountOfUniqueEmailAlertContactsPerUser.userID
- LEFT JOIN
- `uptimerobot-001.ur.users` u
- ON NotDefaultEmailAlertContacts.userID = u.userID
- LEFT JOIN LastSuccessSubsPaymentUserTypeProduct
- ON NotDefaultEmailAlertContacts.userID = LastSuccessSubsPaymentUserTypeProduct.userID
- LEFT JOIN countOfTeamIntegrationAlertContactsByUser
- ON NotDefaultEmailAlertContacts.userID = countOfTeamIntegrationAlertContactsByUser.userID
- WHERE NotDefaultEmailAlertContacts.userID IS NOT NULL
- ORDER BY
- customerType DESC,
- hashedUserID,
- emailAlertContactsBucket,
- userID DESC)
- SELECT
- isDefaultAlertContact,
- uniqueEmailAlertContacts,
- count(DISTINCT userID)
- from
- finalData
- WHERE
- customerType = 'FREE'
- GROUP BY
- isDefaultAlertContact,
- uniqueEmailAlertContacts
- ORDER BY
- isDefaultAlertContact,
- uniqueEmailAlertContacts
Add Comment
Please, Sign In to add comment