Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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,
- (CASE
- WHEN MAX(paymentDateTime) IS NULL THEN 0
- ELSE 1
- END) isCustomer,
- (CASE
- WHEN MAX(lastPaymentMRR) IS NULL THEN 0
- ELSE 1
- END) isSubCustomer,
- SUM(paymentAmount) totalRevenue,
- MAX(lastPaymentMRR) lastPaymentMRR
- FROM
- referredUsers
- GROUP BY
- userID
- ORDER BY
- lastPaymentMRR DESC),
- totalReferredUsersCustomersRevenueMRR as (
- SELECT
- userReferrerUserID,
- count(DISTINCT userID) referredUsers,
- SUM(isCustomer) sumAllReferredCustomers,
- SUM(isSubCustomer) sumSubscriptionReferredCustomers,
- ROUND(SUM(totalRevenue)) sumTotalRevenue,
- SUM(lastPaymentMRR) sumLastPaymentMRR
- FROM
- referredUsersRevenueMRR
- GROUP BY
- userReferrerUserID
- ORDER BY
- sumLastPaymentMRR DESC)
- SELECT
- totalReferredUsersCustomersRevenueMRR.userReferrerUserID,
- u.userEmail,
- referredUsers,
- sumAllReferredCustomers,
- sumSubscriptionReferredCustomers,
- (CASE
- WHEN sumTotalRevenue IS NULL THEN 0
- ELSE sumTotalRevenue
- END) sumTotalRevenue,
- (CASE
- WHEN sumLastPaymentMRR IS NULL THEN 0
- ELSE sumLastPaymentMRR
- END) sumLastPaymentMRR
- FROM
- totalReferredUsersCustomersRevenueMRR
- LEFT JOIN
- `uptimerobot-001.ur.users` u
- ON totalReferredUsersCustomersRevenueMRR.userReferrerUserID = u.userID
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement