API tools faq
paste
Advertisement
festinko

Untitled

festinko
Dec 5th, 2022
603
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 13.37 KB | None | 0 0
raw download clone embed print report
  1. /*Revenue cohorts of customers with last payment for 50+ monitors*/
  2.  
  3. WITH firstPayment as (
  4.     SELECT
  5.         userID,
  6.         MIN(FORMAT_DATE('%Y-%m',DATETIME(paymentDateTime))) as cohortYearMonth,
  7.         MIN(paymentDateTime) as firstPaymentDateTime
  8.     FROM `uptimerobot-001.ur.payments`
  9.     WHERE paymentStatus = 1 AND paymentType = 2 AND userID IS NOT NULL
  10.     GROUP BY userID
  11.     ),
  12.  
  13. /*user's last payment*/
  14.  LP AS (
  15.         SELECT
  16.             p1.userID,
  17.             p1.paymentDateTime AS LastPaymentDateTime,
  18.             p1.paymentPeriod,
  19.             p1.productID as lastPaymentProductID,
  20.             pr.productValue as lastPaymentProductValue
  21.  
  22.         FROM `uptimerobot-001.ur.payments` p1
  23.             LEFT JOIN `uptimerobot-001.ur.payments` p2
  24.                 ON (p1.userID = p2.userID AND p1.paymentDateTime < p2.paymentDateTime AND p2.paymentType = 2)
  25.             LEFT JOIN `uptimerobot-001.ur.products` pr
  26.                 ON p1.productID = pr.productID
  27.         WHERE
  28.         p2.paymentDateTime IS NULL
  29.         AND p1.userID IS NOT NULL
  30.         AND p1.paymentType = 2
  31.         AND p1.paymentStatus = 1
  32.        
  33.         ),
  34.  
  35. /*All payments of a customer with cohortYearMonth, paymentYearMonth of given payment, index of the month used to decide
  36. if he should be included in cohort and payment amount with cohort Revenue for specific cohort*/
  37.  
  38. preparedData as (
  39.     SELECT
  40.         p.userID,
  41.         fp.cohortYearMonth,
  42.         (CASE
  43.             WHEN LastPaymentDateTime IS NULL THEN 'FREE'
  44.             WHEN (DATE_ADD(DATETIME(LastPaymentDateTime), INTERVAL LP.paymentPeriod MONTH) >= CURRENT_DATE()) THEN 'CurrentCustomer'
  45.             ELSE 'ChurnedCustomer'
  46.         END) AS customerType,
  47.         FORMAT_DATE('%Y-%m',DATETIME(paymentDateTime)) paymentYearMonth,
  48.         (DATE_DIFF(DATETIME(p.paymentDateTime), DATETIME(fp.firstPaymentDateTime), MONTH)) as indexOfPaymentMonth,
  49.         p.paymentPeriod,
  50.         p.productID,
  51.         lastPaymentProductID,
  52.         lastPaymentProductValue,
  53.         p.paymentAmount,
  54.         SAFE_DIVIDE(p.paymentAmount,p.paymentPeriod) as cohortRevenue
  55.        
  56.     FROM `uptimerobot-001.ur.payments` p
  57.         LEFT JOIN firstPayment fp
  58.         ON p.userID = fp.userID
  59.  
  60.             LEFT JOIN LP
  61.             ON  (p.userID = LP.userID)
  62.  
  63.     WHERE
  64.         paymentStatus = 1
  65.         AND paymentType = 2
  66.     ORDER BY paymentYearMonth
  67.     ),
  68.        
  69. cohorts as (
  70.     SELECT DISTINCT cohortYearMonth as acquisitionYearMonth
  71.     FROM firstPayment
  72.     ORDER BY cohortYearMonth DESC
  73.         )
  74.        
  75. /*Customer Cohorts in Abs Numbers*/
  76.  
  77.  
  78. SELECT
  79.     cohortYearMonth,
  80.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 0 THEN paymentAmount END),0) Month0,
  81.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 1 THEN paymentAmount END),0) Month1,
  82.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 2 THEN paymentAmount END),0) Month2,
  83.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 3 THEN paymentAmount END),0) Month3,
  84.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 4 THEN paymentAmount END),0) Month4,
  85.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 5 THEN paymentAmount END),0) Month5,
  86.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 6 THEN paymentAmount END),0) Month6,
  87.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 7 THEN paymentAmount END),0) Month7,
  88.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 8 THEN paymentAmount END),0) Month8,
  89.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 9 THEN paymentAmount END),0) Month9,
  90.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 10 THEN paymentAmount END),0) Month10,
  91.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 11 THEN paymentAmount END),0) Month11,
  92.        
  93.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 12 THEN paymentAmount END),0) Month12,
  94.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 13 THEN paymentAmount END),0) Month13,
  95.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 14 THEN paymentAmount END),0) Month14,
  96.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 15 THEN paymentAmount END),0) Month15,
  97.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 16 THEN paymentAmount END),0) Month16,
  98.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 17 THEN paymentAmount END),0) Month17,
  99.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 18 THEN paymentAmount END),0) Month18,
  100.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 19 THEN paymentAmount END),0) Month19,
  101.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 20 THEN paymentAmount END),0) Month20,
  102.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 21 THEN paymentAmount END),0) Month21,
  103.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 22 THEN paymentAmount END),0) Month22,
  104.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 23 THEN paymentAmount END),0) Month23,
  105.        
  106.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 24 THEN paymentAmount END),0) Month24,
  107.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 25 THEN paymentAmount END),0) Month25,
  108.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 26 THEN paymentAmount END),0) Month26,
  109.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 27 THEN paymentAmount END),0) Month27,
  110.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 28 THEN paymentAmount END),0) Month28,
  111.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 29 THEN paymentAmount END),0) Month29,
  112.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 30 THEN paymentAmount END),0) Month30,
  113.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 31 THEN paymentAmount END),0) Month31,
  114.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 32 THEN paymentAmount END),0) Month32,
  115.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 33 THEN paymentAmount END),0) Month33,
  116.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 34 THEN paymentAmount END),0) Month34,
  117.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 35 THEN paymentAmount END),0) Month35,
  118.        
  119.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 36 THEN paymentAmount END),0) Month36,
  120.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 37 THEN paymentAmount END),0) Month37,
  121.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 38 THEN paymentAmount END),0) Month38,
  122.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 39 THEN paymentAmount END),0) Month39,
  123.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 40 THEN paymentAmount END),0) Month40,
  124.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 41 THEN paymentAmount END),0) Month41,
  125.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 42 THEN paymentAmount END),0) Month42,
  126.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 43 THEN paymentAmount END),0) Month43,
  127.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 44 THEN paymentAmount END),0) Month44,
  128.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 45 THEN paymentAmount END),0) Month45,
  129.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 46 THEN paymentAmount END),0) Month46,
  130.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 47 THEN paymentAmount END),0) Month47,
  131.        
  132.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 48 THEN paymentAmount END),0) Month48,
  133.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 49 THEN paymentAmount END),0) Month49,
  134.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 50 THEN paymentAmount END),0) Month50,
  135.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 51 THEN paymentAmount END),0) Month51,
  136.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 52 THEN paymentAmount END),0) Month52,
  137.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 53 THEN paymentAmount END),0) Month53,
  138.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 54 THEN paymentAmount END),0) Month54,
  139.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 55 THEN paymentAmount END),0) Month55,
  140.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 56 THEN paymentAmount END),0) Month56,
  141.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 57 THEN paymentAmount END),0) Month57,
  142.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 58 THEN paymentAmount END),0) Month58,
  143.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 59 THEN paymentAmount END),0) Month59,
  144.        
  145.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 60 THEN paymentAmount END),0) Month60,
  146.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 61 THEN paymentAmount END),0) Month61,
  147.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 62 THEN paymentAmount END),0) Month62,
  148.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 63 THEN paymentAmount END),0) Month63,
  149.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 64 THEN paymentAmount END),0) Month64,
  150.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 65 THEN paymentAmount END),0) Month65,
  151.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 66 THEN paymentAmount END),0) Month66,
  152.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 67 THEN paymentAmount END),0) Month67,
  153.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 68 THEN paymentAmount END),0) Month68,
  154.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 69 THEN paymentAmount END),0) Month69,
  155.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 70 THEN paymentAmount END),0) Month70,
  156.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 71 THEN paymentAmount END),0) Month71,
  157.        
  158.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 72 THEN paymentAmount END),0) Month72,
  159.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 73 THEN paymentAmount END),0) Month73,
  160.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 74 THEN paymentAmount END),0) Month74,
  161.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 75 THEN paymentAmount END),0) Month75,
  162.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 76 THEN paymentAmount END),0) Month76,
  163.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 77 THEN paymentAmount END),0) Month77,
  164.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 78 THEN paymentAmount END),0) Month78,
  165.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 79 THEN paymentAmount END),0) Month79,
  166.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 80 THEN paymentAmount END),0) Month80,
  167.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 81 THEN paymentAmount END),0) Month81,
  168.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 82 THEN paymentAmount END),0) Month82,
  169.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 83 THEN paymentAmount END),0) Month83,
  170.        
  171.         ROUND(SUM(CASE WHEN acquisitionYearMonth = cohortYearMonth AND indexOfPaymentMonth = 84 THEN paymentAmount END),0) Month84  
  172.  
  173. FROM cohorts c
  174.     LEFT JOIN
  175.         preparedData pd
  176.         ON c.acquisitionYearMonth = pd.cohortYearMonth
  177. GROUP BY cohortYearMonth
  178. ORDER BY cohortYearMonth
  179.  
  180.  
  181. /*SELECT *
  182. FROM preparedData
  183. WHERE userID IS NOT NULL
  184. ORDER BY userID, indexOfPaymentMonth*/
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!