Advertisement
festinko

Untitled

Dec 2nd, 2022
665
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 3.14 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.         paymentPeriod IN (1,12)
  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.   totalReferredUsersCustomersRevenueMRR.userReferrerUserID,
  124.   u.userEmail,
  125.   referredUsers,
  126.   sumAllReferredCustomers,
  127.   sumSubscriptionReferredCustomers,
  128.   (CASE
  129.   WHEN sumTotalRevenue IS NULL THEN 0
  130.   ELSE sumTotalRevenue
  131.   END) sumTotalRevenue,
  132.   (CASE
  133.   WHEN sumLastPaymentMRR IS NULL THEN 0
  134.   ELSE sumLastPaymentMRR
  135.   END) sumLastPaymentMRR
  136.  
  137.  
  138. FROM
  139.   totalReferredUsersCustomersRevenueMRR
  140.  
  141. LEFT JOIN
  142.     `uptimerobot-001.ur.users` u
  143.  
  144. ON totalReferredUsersCustomersRevenueMRR.userReferrerUserID = u.userID
  145.  
  146.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement