/*Revenue / TotalCustomers / MRR / ARPU / ChurnRate / LTV by Country Calculation*/

SELECT arpu.paymentBillingCountry, AllTime2.AllTimeTotalRevenue, AllTime2.AllTimeTotalCustomers, arpu.Total_Current_Customers, arpu.CurrentMRR, AllTime2.AllTimeARPU, (arpu.CurrentMRR/arpu.Total_Current_Customers) AS CurrentAvgMRR_ARPU, ROUND(churn.ChurnedMRR) AS AlltimeChurnedMRR, churn.ChurnedCustomers as AllTimeChurnedCustomers, (churn.ChurnedCustomers / AllTime2.AllTimeTotalCustomers) AS ChurnRate, avgLT.LT AS LifeTime, ULT1.AvgLifeTimeByCountry AS LifeTime2, (avgLT.LT * (arpu.CurrentMRR/arpu.Total_Current_Customers)) AS LTV, (ULT1.AvgLifeTimeByCountry * (arpu.CurrentMRR/arpu.Total_Current_Customers)) AS LTV2, (AllTime2.AllTimeARPU / (churn.ChurnedCustomers / AllTime2.AllTimeTotalCustomers)) AS LTV3
FROM

/*Alltime ARPU by Country calculation*/
(SELECT paymentBillingCountry, count(DISTINCT userID) Total_Current_Customers, ROUND(SUM(paymentAmount / paymentPeriod)) AS CurrentMRR
FROM payments
WHERE paymentDateTime < CURDATE() AND DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) >= CURDATE() AND paymentStatus = 1 and paymentPeriod > 0 AND userID IS NOT NULL
GROUP BY paymentBillingCountry
ORDER BY CurrentMRR DESC) as arpu

LEFT JOIN

/*Alltime Churned MRR & Users by Country*/
(SELECT paymentBillingCountry, sum(paymentAmount/paymentPeriod) as ChurnedMRR, count(DISTINCT p1.userID) as ChurnedCustomers
FROM payments p1 LEFT JOIN

(select userID, MAX(DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH)) AS max
FROM payments
WHERE paymentStatus = 1 AND paymentPeriod > 0 AND userID IS NOT NULL
GROUP BY userID
HAVING max < CURDATE()) AS p2

ON (p1.userID = p2.userID and DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) = p2.max)
WHERE p2.userID IS NOT NULL

GROUP BY paymentBillingCountry) as churn

on arpu.paymentBillingCountry = churn.paymentBillingCountry

LEFT JOIN

/*AVG LifeTime by Country based on MAX - MIN Last Payment + PaymentPeriod)*/
(SELECT paymentBillingCountry, AVG(LT.LifeTime) AS LT
FROM
/*LifeTime last Last - First Payment Difference by User ID with PaymentBilling Country */

(SELECT MinP.userID, MinP.paymentBillingCountry, MIN(MinP.paymentDateTime), MAX(MinP.paymentDateTime), ((DATEDIFF(MAX(MinP.paymentDateTime), MIN(MinP.paymentDateTime))/ 30.5) + LP.paymentPeriod) AS LifeTime
    FROM payments MinP
    LEFT JOIN

/*last payment with Payment Period*/
(select p1.userID, p1.paymentBillingCountry, DATE_FORMAT(p1.paymentDateTime, '%d/%m/%Y') as paymentDateTime, p1.paymentPeriod
from payments p1
left join payments p2 on (p1.userID = p2.userID and p1.paymentDateTime < p2.paymentDateTime AND p2.paymentType = 2)
where p2.paymentDateTime IS NULL AND p1.paymentDateTime IS NOT NULL AND p1.paymentPeriod > 0
group by p1.userID
order by p1.paymentID DESC) LP

on MinP.userID = LP.userID

GROUP BY MinP.userID) LT

GROUP BY paymentBillingCountry
ORDER BY paymentBillingCountry) avgLT

on avgLT.paymentBillingCountry = arpu.paymentBillingCountry

LEFT JOIN

/*AVG LifeTime by Country based on SUM of PaymentPeriod)*/

(SELECT ULT.paymentBillingCountry, AVG(ULT.UserLifeTime) AvgLifeTimeByCountry
FROM
    (SELECT userID, SUM(paymentPeriod) UserLifeTime, paymentBillingCountry
    FROM payments
    GROUP BY userID
    ORDER BY userID) ULT
GROUP BY paymentBillingCountry) ULT1

on ULT1.paymentBillingCountry = arpu.paymentBillingCountry

LEFT JOIN

/* AllTime Total Revenue, Customers & ARPU by Country*/
(SELECT AllTime.paymentBillingCountry, AllTime.AllTimeTotalRevenue, AllTime.AllTimeTotalCustomers, (AllTime.AllTimeTotalRevenue / AllTime.AllTimeTotalCustomers) as AllTimeARPU
FROM(

SELECT TotalUserPaymentAmount.paymentBillingCountry, SUM(TotalUserPaymentAmount.paymentAmount) as AllTimeTotalRevenue, count(DISTINCT userID) AllTimeTotalCustomers
FROM
(SELECT userID, SUM(paymentAmount) as paymentAmount, paymentBillingCountry
FROM payments
GROUP BY userID) as TotalUserPaymentAmount

GROUP BY TotalUserPaymentAmount.paymentBillingCountry) AllTime) AllTime2

on AllTime2.paymentBillingCountry = arpu.paymentBillingCountry