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 (

SELECT
userID,
userEmail,
userNewsletter,
userSMSRefill


FROM
    `uptimerobot-001.ur.users`

WHERE userID IN (

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

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

            GROUP BY
                    userID
            ORDER BY
                userID)
    AND (userSMSRefill = 0  OR userSMSRefill IS NULL)
)

 SELECT
    userID,
    userEmail,
    userNewsletter,
    userSMSRefill

FROM
  `uptimerobot-001.ur.users` u

WHERE
  userID IN (
    SELECT
      userID
    FROM
      preparedData
  )
    AND userID IN (
            SELECT
                userID
            FROM
                lastPayPalPayment
            )

order by
  userID