WITH

/*List of all successful subscription payments - Alltime*/
    SuccessSubsPayment AS(
         SELECT
            userID,
            paymentDateTime,
            paymentAmount,
            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.paymentAmount as lastPaymentAmount,
            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,
                lastPaymentAmount,
                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.lastPaymentAmount,
                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 multipla last successful payments from the same user - some edge cases*/
  LastSuccessSubsPaymentUserTypeProduct AS (
        SELECT
            userID,
            MAX(lastPaymentDateTime) as lastPaymentDateTime,
            MAX(lastPaymentAmount) as lastPaymentAmount,
            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
        )

SELECT
    customerType,
    lastPaymentProductName,
    count(userID) as userCount,
    ROUND(SUM(lastPaymentAmount)) as Revenue,
    ROUND(SUM(lastPaymentAmount/lastPaymentPeriod)) as MRR

FROM
    LastSuccessSubsPaymentUserTypeProduct

WHERE lastPaymentPeriod IS NOT NULL AND lastPaymentPeriod > 0

GROUP BY
    customerType,
    lastPaymentProductName