Advertisement
festinko

Untitled

Dec 2nd, 2022
807
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 2.66 KB | None | 0 0
  1. WITH
  2.     lastPayment as(
  3.       SELECT
  4.         p1.userID,
  5.         MAX(p1.paymentDateTime) paymentDateTime,
  6.         MAX(p1.paymentAmount) paymentAmount,
  7.         MAX(p1.paymentPeriod) paymentPeriod
  8.  
  9.       FROM
  10.         `uptimerobot-001.ur.payments` p1
  11.      
  12.       LEFT JOIN (
  13.         SELECT
  14.           userID,
  15.           MAX(paymentDatetime) lastPaymentDateTime
  16.         FROM
  17.           `uptimerobot-001.ur.payments`
  18.        
  19.         WHERE
  20.         paymentStatus = 1 AND
  21.         paymentType = 2
  22.  
  23.         GROUP BY
  24.           userID
  25.        ) lp
  26.  
  27.  
  28.  
  29.       ON p1.paymentDateTime = lp.lastPaymentDateTime
  30.  
  31.       WHERE
  32.       lastPaymentDateTime IS NOT NULL
  33.      
  34.      
  35.       GROUP BY
  36.         userID
  37.     ),
  38.  
  39.     lastMRR as(
  40.       SELECT
  41.         userID,
  42.         paymentDateTime,
  43.         ROUND(SAFE_DIVIDE(paymentAmount, paymentPeriod)) lastPaymentMRR
  44.       FROM
  45.         lastPayment
  46.     ),
  47.  
  48.     referredUsers as(
  49.         SELECT
  50.             userReferrerUserID,
  51.             u.userID,
  52.             p.paymentDateTime,
  53.             paymentAmount,
  54.             paymentPeriod,
  55.             lastPaymentMRR
  56.  
  57.        
  58.         FROM
  59.             `uptimerobot-001.ur.users` u
  60.         LEFT JOIN
  61.           `uptimerobot-001.ur.payments` p
  62.        
  63.         ON u.userID = p.userID
  64.        
  65.         LEFT JOIN
  66.           lastMRR
  67.         ON u.userID = lastMRR.userID
  68.  
  69.         WHERE
  70.             userReferrerUserID IS NOT NULL
  71.             AND userReferrerUserID <> 999999999
  72.        
  73.         ORDER BY
  74.             userReferrerUserID DESC,
  75.             u.userID DESC,
  76.             paymentDateTime DESC),
  77.  
  78. referredUsersRevenueMRR as (
  79.         SELECT
  80.           MAX(userReferrerUserID) userReferrerUserID,
  81.           userID,
  82.           (CASE
  83.             WHEN MAX(paymentDateTime) IS NULL THEN 0
  84.             ELSE 1
  85.           END) isCustomer,
  86.           (CASE
  87.             WHEN MAX(lastPaymentMRR) IS NULL THEN 0
  88.             ELSE 1
  89.           END) isSubCustomer,
  90.           SUM(paymentAmount) totalRevenue,
  91.           MAX(lastPaymentMRR) lastPaymentMRR
  92.  
  93.         FROM
  94.           referredUsers
  95.  
  96.         GROUP BY
  97.           userID
  98.  
  99.         ORDER BY
  100.           lastPaymentMRR DESC),
  101.  
  102. totalReferredUsersCustomersRevenueMRR as (
  103.   SELECT
  104.     userReferrerUserID,
  105.     count(DISTINCT userID) referredUsers,
  106.     SUM(isCustomer) sumAllReferredCustomers,
  107.     SUM(isSubCustomer) sumSubscriptionReferredCustomers,
  108.     ROUND(SUM(totalRevenue)) sumTotalRevenue,
  109.     SUM(lastPaymentMRR) sumLastPaymentMRR
  110.  
  111.  
  112.   FROM
  113.     referredUsersRevenueMRR
  114.  
  115.   GROUP BY
  116.     userReferrerUserID
  117.  
  118.   ORDER BY
  119.     sumLastPaymentMRR DESC)
  120.  
  121.  
  122. SELECT
  123.   *
  124.  
  125. FROM
  126.   totalReferredUsersCustomersRevenueMRR
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement