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*/

    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