/*Monthly Customers by Product Value and YearMonth*/

WITH
    preparedData as (
        SELECT
            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
        FROM
            `uptimerobot-001.ur.payments` p

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

      ON p.productID = pr.productID

        WHERE
            paymentStatus = 1
      AND paymentPeriod = 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
    )

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

ORDER BY
  reportingYearMonth DESC,
  paymentPeriod,
  productValue