WITH newSubs as (

/*New Subsscriptions*/
SELECT
    DATE(paymentDateTime) firstSubsPaymentDate,
    DATE_FORMAT(paymentDateTime,'%x-%v') firstSubsPaymentWeekYear,
  COUNT(DISTINCT userID) firstSubscriptionUsersCount_G4,
    ROUND(SUM(paymentAmount)) firstSubscriptionRevenue,
    ROUND(SUM(paymentAmount/paymentPeriod)) firstSubscriptionMRR

FROM
  payments

WHERE
  paymentNewUpgradeRenewal = 1
  AND paymentPeriod IN (1,12)
    AND paymentStatus = 1
  AND userID IS NOT NULL

GROUP BY
    firstSubsPaymentDate


ORDER BY
    firstSubsPaymentDate DESC),

newSigunps as (
        SELECT
            DATE(userRegisterDateTime) registerDate,
            DATE_FORMAT(userRegisterDateTime,'%x-%v') registerDateWeekYear,
            count(DISTINCT userID) newSigunUpsVerified_G2

        FROM
            users

        WHERE
            userStatus = 1
            AND userID IS NOT NULL

        GROUP BY
            registerDate
    ),

    upgradedSubs as (
    /*Upgraded Subsscriptions*/

SELECT
    DATE(paymentDateTime) upgradedSubsPaymentDate,
  COUNT(DISTINCT userID) upgradedSubscriptionUsersCount,
    ROUND(SUM(paymentAmount)) upgradedSubscriptionRevenue,
    ROUND(SUM(paymentAmount/paymentPeriod)) upgradedSubscriptionMRR

FROM
  payments

WHERE
  paymentNewUpgradeRenewal = 2
  AND paymentPeriod IN (1,12)
    AND paymentStatus = 1
  AND userID IS NOT NULL

GROUP BY
    upgradedSubsPaymentDate


ORDER BY
    upgradedSubsPaymentDate DESC
    ),

    renewedSubs as (
    /*Renewed Subsscriptions*/

SELECT
    DATE(paymentDateTime) renewedSubsPaymentDate,
  COUNT(DISTINCT userID) renewedSubscriptionUsersCount,
    ROUND(SUM(paymentAmount)) renewedSubscriptionRevenue,
    ROUND(SUM(paymentAmount/paymentPeriod)) renewedSubscriptionMRR

FROM
  payments

WHERE
  paymentNewUpgradeRenewal = 3
  AND paymentPeriod IN (1,12)
    AND paymentStatus = 1
  AND userID IS NOT NULL

GROUP BY
    renewedSubsPaymentDate


ORDER BY
    renewedSubsPaymentDate DESC
    ),

    canceledSubs AS (
        SELECT
            DATE(cancelled) cancellDate,
            count(userID) canceledSubsCount

        FROM
            subscriptions

        GROUP BY
            cancellDate

        ORDER BY
            cancellDate DESC),


    subusersCreated as
    (SELECT
        DATE(created) subuserCreatedDate,
        COUNT(userID) subusersCreatedCount

    FROM
        organization_members

    GROUP BY
            subuserCreatedDate
    ORDER BY
        subuserCreatedDate DESC),

    usersFirstAddonPayment as (

/*First users payment for add on*/
SELECT
    userID,
    MIN(DATE(paymentDateTime)) firstSubuserPaymentDate
FROM
    payments

WHERE

  paymentType = 3
    AND productID = 2
    AND paymentDateTime > '2022-12-11'

GROUP BY
    userID

ORDER BY
  firstSubuserPaymentDate DESC),

firstAddonPayment as (
    /*Count of users with first payment for sub user by date*/
    SELECT
        firstSubuserPaymentDate,
        count(DISTINCT userID) firstSubuserPaymentUsersCount_G8

    FROM
        usersFirstAddonPayment

    GROUP BY
        firstSubuserPaymentDate

    ORDER BY
        firstSubuserPaymentDate DESC),

preparedData as (

SELECT
    registerDateWeekYear,
    SUM(newSigunUpsVerified_G2) as newSigunUpsVerified_G2WeeklySum,
    SUM(firstSubscriptionUsersCount_G4) as firstSubscriptionUsersCount_G4WeeklySum,
    SUM(firstSubscriptionRevenue)as firstSubscriptionRevenueWeeklySum,
    SUM(firstSubscriptionMRR) as firstSubscriptionMRRWeeklySum,
    SUM(upgradedSubscriptionUsersCount) as upgradedSubscriptionUsersCountWeeklySum,
    SUM(upgradedSubscriptionRevenue) as upgradedSubscriptionRevenueWeeklySum,
    SUM(upgradedSubscriptionMRR) as upgradedSubscriptionMRRWeeklySum,
    SUM(renewedSubscriptionUsersCount) as renewedSubscriptionUsersCountWeeklySum,
    SUM(renewedSubscriptionRevenue) as renewedSubscriptionRevenueWeeklySum,
    SUM(renewedSubscriptionMRR) as renewedSubscriptionMRRWeeklySum,
    SUM(canceledSubsCount) as canceledSubsCountWeeklySum,
    SUM(subusersCreatedCount) as subusersCreatedCountWeeklySum,
    SUM(firstSubuserPaymentUsersCount_G8) as firstSubuserPaymentUsersCount_G8WeeklySum,

    ROUND(AVG(newSigunUpsVerified_G2)) as newSigunUpsVerified_G2WeeklyAVG,
    ROUND(AVG(firstSubscriptionUsersCount_G4)) as firstSubscriptionUsersCount_G4WeeklyAVG,
    ROUND(AVG(firstSubscriptionRevenue)) as firstSubscriptionRevenueWeeklyAVG,
    ROUND(AVG(firstSubscriptionMRR)) as firstSubscriptionMRRWeeklyAVG,
    ROUND(AVG(upgradedSubscriptionUsersCount)) as upgradedSubscriptionUsersCountWeeklyAVG,
    ROUND(AVG(upgradedSubscriptionRevenue)) as upgradedSubscriptionRevenueWeeklyAVG,
    ROUND(AVG(upgradedSubscriptionMRR)) as upgradedSubscriptionMRRWeeklyAVG,
    ROUND(AVG(renewedSubscriptionUsersCount)) as renewedSubscriptionUsersCountWeeklyAVG,
    ROUND(AVG(renewedSubscriptionRevenue)) as renewedSubscriptionRevenueWeeklyAVG,
    ROUND(AVG(renewedSubscriptionMRR)) as renewedSubscriptionMRRWeeklyAVG,
    ROUND(AVG(canceledSubsCount)) as canceledSubsCountWeeklyAVG,
    ROUND(AVG(subusersCreatedCount)) as subusersCreatedCountWeeklyAVG,
    ROUND(AVG(firstSubuserPaymentUsersCount_G8)) as firstSubuserPaymentUsersCount_G8WeeklyAVG


FROM
    newSigunps

LEFT JOIN
    newSubs

ON newSigunps.registerDate = newSubs.firstSubsPaymentDate

LEFT JOIN
    upgradedSubs

ON newSigunps.registerDate = upgradedSubs.upgradedSubsPaymentDate

LEFT JOIN
    renewedSubs

ON newSigunps.registerDate = renewedSubs.renewedSubsPaymentDate

LEFT JOIN
    canceledSubs

ON
    newSigunps.registerDate = canceledSubs.cancellDate

LEFT JOIN
    subusersCreated

ON
    newSigunps.registerDate = subusersCreated.subuserCreatedDate

LEFT JOIN
    firstAddonPayment

ON
    newSigunps.registerDate = firstAddonPayment.firstSubuserPaymentDate

GROUP BY
    registerDateWeekYear

ORDER BY
    registerDateWeekYear DESC)

SELECT
    registerDateWeekYear,
    newSigunUpsVerified_G2WeeklySum,
    firstSubscriptionUsersCount_G4WeeklySum,
    ROUND((firstSubscriptionUsersCount_G4WeeklySum / newSigunUpsVerified_G2WeeklySum),6) as crG2toG4WeeklySUM,
    firstSubscriptionRevenueWeeklySum,
    firstSubscriptionMRRWeeklySum,
    upgradedSubscriptionUsersCountWeeklySum,
    upgradedSubscriptionRevenueWeeklySum,
    upgradedSubscriptionMRRWeeklySum,
    renewedSubscriptionUsersCountWeeklySum,
    renewedSubscriptionRevenueWeeklySum,
    renewedSubscriptionMRRWeeklySum,
    canceledSubsCountWeeklySum,
    subusersCreatedCountWeeklySum,
    firstSubuserPaymentUsersCount_G8WeeklySum,

    newSigunUpsVerified_G2WeeklyAVG,
    firstSubscriptionUsersCount_G4WeeklyAVG,
    ROUND((firstSubscriptionUsersCount_G4WeeklyAVG / newSigunUpsVerified_G2WeeklyAVG),6) as crG2toG4WeeklyAVG,
    firstSubscriptionRevenueWeeklyAVG,
    firstSubscriptionMRRWeeklyAVG,
    upgradedSubscriptionUsersCountWeeklyAVG,
    upgradedSubscriptionRevenueWeeklyAVG,
    upgradedSubscriptionMRRWeeklyAVG,
    renewedSubscriptionUsersCountWeeklyAVG,
    renewedSubscriptionRevenueWeeklyAVG,
    renewedSubscriptionMRRWeeklyAVG
    canceledSubsCountWeeklyAVG,
    subusersCreatedCountWeeklyAVG,
    firstSubuserPaymentUsersCount_G8WeeklyAVG

FROM
    preparedData