WITH
    preparedData as (
        SELECT
            userID,
      productID,
            paymentAmount,
            paymentPeriod,
            paymentDateTime,
            DATE_TRUNC(DATETIME(paymentDateTime), MONTH) as prepaidPeriodStart,
      DATE_TRUNC(DATE_ADD(DATETIME(paymentDateTime), INTERVAL paymentPeriod MONTH), MONTH) as prepaidPeriodEnd
        FROM
            `uptimerobot-001.ur.payments`
        WHERE
            paymentStatus = 1
            AND paymentPeriod BETWEEN 1 AND 12
            AND userID IS NOT NULL
    ),
    calendar as (
        select
            DATE_TRUNC(DATETIME(paymentDateTime), MONTH) as reportingYearMonth
        FROM `uptimerobot-001.ur.payments`
        GROUP BY reportingYearMonth
    ),

customersYearMonthPaymentPeriod as (

SELECT
 cal.reportingYearMonth,
 paymentPeriod,
 count(DISTINCT userID) activeCustomers
FROM calendar cal
JOIN preparedData pd ON cal.reportingYearMonth BETWEEN pd.prepaidPeriodStart AND prepaidPeriodEnd
GROUP BY
    cal.reportingYearMonth,
  paymentPeriod

ORDER BY
  reportingYearMonth DESC,
  paymentPeriod)

SELECT

  reportingYearMonth,
  SUM(CASE WHEN paymentPeriod = 1 THEN activeCustomers END) as monthlyCustomers,
  SUM(CASE WHEN paymentPeriod = 12 THEN activeCustomers END) as annualCustomers

FROM
  customersYearMonthPaymentPeriod

GROUP BY
  reportingYearMonth

ORDER BY
  reportingYearMonth DESC