WITH
/*list of users with last subs payment via PayPal*/

lastPayPalPayment as (
  SELECT
    p.userID

  FROM
    `uptimerobot-001.ur.payments` p
  LEFT JOIN
    (SELECT
      userID,
      MAX(paymentDateTime) lastPaymentDateTime
    FROM
      `uptimerobot-001.ur.payments`
    WHERE
      paymentProcessor = 2
            AND paymentType = 2
    GROUP BY
      userID
    ) as lp

  ON lp.userID = p.userID AND p.paymentDateTime = lastPaymentDateTime

WHERE
    lp.userID IS NOT NULL

),

preparedData as (

/*Users with payment for SMS / call notification credits & not active SMS / call alert contact*/
SELECT
    p.userID

FROM
    `uptimerobot-001.ur.payments` p

LEFT JOIN
    `uptimerobot-001.ur.users` u

    ON p.userID = u.userID

WHERE
paymentPeriod = 0
AND (userSMSRefill = 0  OR userSMSRefill IS NULL)
AND u.userID IS NOT NULL
AND p.userID NOT IN (

/*Users with active SMS / Voice call alert contact*/

    SELECT
        userID
    FROM
        `uptimerobot-001.ur.alertcontacts`

    WHERE
        alertContactType IN (8,14)
        AND alertContactStatus = 2

    GROUP BY
    userID
)

/*filtering only customers with last subs payment via PayPal*/
AND p.userID IN (
SELECT
    userID
FROM
    lastPayPalPayment
)

GROUP BY
    p.userID)


 SELECT
    userID,
    userEmail,
    userNewsletter,
    userSMSRefill

FROM
  `uptimerobot-001.ur.users` u

WHERE
  userID IN (
    SELECT
      userID
    FROM
      preparedData
  )