WITH
    lastPayment as(
      SELECT
        p1.userID,
        MAX(p1.paymentDateTime) paymentDateTime,
        MAX(p1.paymentAmount) paymentAmount,
        MAX(p1.paymentPeriod) paymentPeriod

      FROM
        `uptimerobot-001.ur.payments` p1

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

        WHERE
        paymentStatus = 1 AND
        paymentType = 2

        GROUP BY
          userID
       ) lp



      ON p1.paymentDateTime = lp.lastPaymentDateTime

      WHERE
      lastPaymentDateTime IS NOT NULL


      GROUP BY
        userID
    ),

    lastMRR as(
      SELECT
        userID,
        paymentDateTime,
        ROUND(SAFE_DIVIDE(paymentAmount, paymentPeriod)) lastPaymentMRR
      FROM
        lastPayment
    ),

    referredUsers as(
        SELECT
            userReferrerUserID,
            u.userID,
            p.paymentDateTime,
            paymentAmount,
            paymentPeriod,
            lastPaymentMRR


        FROM
            `uptimerobot-001.ur.users` u
        LEFT JOIN
          `uptimerobot-001.ur.payments` p

        ON u.userID = p.userID

        LEFT JOIN
          lastMRR
        ON u.userID = lastMRR.userID

        WHERE
            userReferrerUserID IS NOT NULL
            AND userReferrerUserID <> 999999999

        ORDER BY
            userReferrerUserID DESC,
            u.userID DESC,
            paymentDateTime DESC),

referredUsersRevenueMRR as (
        SELECT
          MAX(userReferrerUserID) userReferrerUserID,
          userID,
          SUM(paymentAmount) totalRevenue,
          MAX(lastPaymentMRR) lastPaymentMRR

        FROM
          referredUsers

        GROUP BY
          userID

        ORDER BY
          lastPaymentMRR DESC),

totalReferredRevenueMRR as (
  SELECT
    userReferrerUserID,
    count(DISTINCT userID) referredUsers,
    ROUND(SUM(totalRevenue)) sumTotalRevenue,
    SUM(lastPaymentMRR) sumLastPaymentMRR


  FROM
    referredUsersRevenueMRR

  GROUP BY
    userReferrerUserID

  ORDER BY
    sumLastPaymentMRR DESC)


SELECT
  *

FROM
  totalReferredRevenueMRR