/*Churned Customers Monthly*/
WITH
    preparedData as (
        SELECT
            p.userID,
      p.productID,
      pr.productValue,
            paymentAmount,
            paymentPeriod,
            paymentDateTime,
            DATE_TRUNC(DATETIME(paymentDateTime), MONTH) as prepaidPeriodStart,
      DATE_TRUNC(DATE_ADD(DATETIME(paymentDateTime), INTERVAL paymentPeriod MONTH), MONTH) as prepaidPeriodEnd,
      DATE_TRUNC(DATE_ADD(DATETIME(paymentDateTime), INTERVAL paymentPeriod MONTH), DAY) as prepaidPeriodEndDay
        FROM
            `uptimerobot-001.ur.payments` p

    LEFT JOIN
    `uptimerobot-001.ur.products` pr

      ON p.productID = pr.productID

        LEFT JOIN   (
            SELECT
                userID,
                MAX(paymentDateTime) lastPaymentDateTime
            FROM
                `uptimerobot-001.ur.payments`

            WHERE
                paymentStatus = 1
                AND paymentPeriod IN (1,12)
                AND userID IS NOT NULL
            GROUP BY
                userID
            ) lp
            ON p.userID =lp.userID AND p.paymentDateTime = lp.lastPaymentDateTime

        WHERE
            paymentStatus = 1
            AND paymentPeriod IN (1,12)
            AND p.userID IS NOT NULL
            AND lp.lastPaymentDateTime IS NOT NULL
    ),
    calendar as (
        SELECT
        DATE_TRUNC(DATETIME(paymentDateTime), MONTH) as reportingYearMonth
        FROM `uptimerobot-001.ur.payments`
        GROUP BY reportingYearMonth
    )

  SELECT
        reportingYearMonth,
        COUNT(DISTINCT userID)

  FROM
    preparedData pd

    LEFT JOIN calendar cal
        ON pd.prepaidPeriodEndDay > reportingYearMonth AND pd.prepaidPeriodEndDay < DATE_ADD(cal.reportingYearMonth, INTERVAL 1 MONTH)

    GROUP BY
        reportingYearMonth

    ORDER BY
        reportingYearMonth DESC