WITH

  reportingDateRange as (
    SELECT
    "2022-01-01 00:00:01" as fromDateTime,
    "2022-01-31 23:59:59" as toDateTime
  ),

/*New Customers for selected date range*/
 newCustomers as (
   SELECT
    userID,
    RAND() random
   FROM
    `uptimerobot-001.ur.payments`
  WHERE
    paymentPeriod IN (1,12)
    AND paymentStatus = 1
    AND paymentNewUpgradeRenewal = 1

    /*Set the date range for desired reporting period*/
    AND paymentDateTime > "2022-01-01 00:00:01"
  ORDER BY
    random
 ),

/*New users for selected date range with YT as reported source of acguisition*/
 reportedSourceOfAcquisition as (
   SELECT
    userID,
    userFirstLastName,
    userEmail,
    userRegisterDateTime,
    userReportedOrigin,
    userNewsletter,
    RAND() random
  FROM
    `uptimerobot-001.ur.users`

  WHERE
    /*Set the date range for desired reporting period*/
    userRegisterDateTime BETWEEN (SELECT fromDateTime from reportingDateRange) AND (SELECT toDateTime from reportingDateRange)
    AND userReportedOrigin = "youtube"
    AND userStatus = 1
  ORDER BY
    userRegisterDateTime
 )

/*Customers with registerDateTime withing the reporting and first payment */

SELECT
  *

FROM
  reportedSourceOfAcquisition

WHERE
  userID IN (SELECT userID FROM newCustomers)

ORDER BY
  random