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(CAST(lastPaymentDateTime AS DATETIME), INTERVAL lastPaymentPeriod MONTH) >= CURRENT_DATE()) THEN 'CurrentCustomer'
                    ELSE 'ChurnedCustomer'
                END) AS customerType,
                lastPaymentProductID
            FROM LastSuccessSubsPayment
    ),

/*added productName*/
    LastSubsPaymentUsersTypeProduct AS (
            SELECT
                lsput.userID,
                lsput.lastPaymentDateTime,
                lsput.lastPaymentPeriod,
                lsput.customerType,
                lsput.lastPaymentProductID,
                pr.productName

            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

    FROM LastSubsPaymentUsersTypeProduct

    GROUP BY userID
    ORDER BY userID DESC
    ),

/*List of All Active Telegram Contacts*/
    TelegramAlertContacts AS (

        SELECT
            userID,
            alertContactType,
            alertContactValue,
            alertContactStatus
        FROM `uptimerobot-001.ur.alertcontacts`
        WHERE alertContactType = 18 AND alertContactStatus = 2
        ),

/*Number of Active Telegram Contacts per user*/
    CountOfUniqueTelegramAlertContactsPerUser AS(
        SELECT
            userID,
            count(DISTINCT alertContactValue) uniqueEmailAlertContacts
    FROM TelegramAlertContacts
    GROUP BY userID
    ORDER BY uniqueEmailAlertContacts DESC

    ),

/*List of all users with the count of active unique Telegram by user type - FREE / Current / Churned Customer & product name*/
TelegramContacts AS(
    SELECT
        CountOfUniqueTelegramAlertContactsPerUser.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


    FROM CountOfUniqueTelegramAlertContactsPerUser
    LEFT JOIN LastSuccessSubsPaymentUserTypeProduct
        on CountOfUniqueTelegramAlertContactsPerUser.userID = LastSuccessSubsPaymentUserTypeProduct.userID
    )
/*Number of Users with active Telegram by customer type and lastPaymentProduct*/
SELECT
    customerType,
    lastPaymentProductName,
    count(userID) as userCount
FROM TelegramContacts
GROUP BY
    customerType,
    lastPaymentProductName
ORDER BY
    customerType,
    userCount DESC