How to combine 3 different tables into one table to create aggregated data?

Question

Hope you all are doing well! ：D

I need your help to complete the following tasks:

I need to create the following table:

date	Revenue from new transactions	Lost revenue due to churn	Revenue from upsells
January 1, 2022	1,000	-$500	1,000
January 2, 2022	$2000	-$200	$2000

What happens here is that in order to collect and aggregate this data, I need to get 3 different tables:

Transactions, Churn and Upsells

TransactionTable:

trade	expiration date	Earnings won
Transaction#1	January 1, 2022	$500
Transaction#2	January 1, 2022	$500
Transaction #3	January 2, 2022	$1500
Transaction#4	January 2, 2022	$500

LossTable:

churn	expiration date	Loss of income
churn#1	January 1, 2022	-$500
churn#2	January 2, 2022	-100 dollars
churn#3	January 2, 2022	-100 dollars

Upsell list:

Upsell	expiration date	Earnings won
Upsell#1	January 1, 2022	$2000
Upsell #2	January 1, 2022	-1,000
Upsell#3	January 2, 2022	$2000

The first question is: How can I create a SQL command to accomplish this?

Thanks in advance.

Answer 1

You can use subqueries to aggregate the churn table and the upsell table.

As follows:

select d.Closing_date,
       sum(d.Revenue_won) as 'Revenue gained from new deals', 
       c.`Revenue lost from churn`,
       u.`Revenue gained from upsell`
from deals d
inner join ( select Closing_date,
                    sum(Revenue_lost) as 'Revenue lost from churn'
            from churns 
            group by Closing_date 
            ) as c on c.Closing_date=d.Closing_date
inner join ( select Closing_date,
                    sum(Revenue_won) as 'Revenue gained from upsell'
             from upsells u 
             group by Closing_date 
             ) as u on  u.Closing_date=d.Closing_date          
group by d.Closing_date ;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0875563c9ab7f9385711dde21cd98b47 一个>

Please do not store dates as text, you will face a lot of difficulties. If you want to format the date you can use DATE_FORMAT

Notice. If another date exists in the transaction table but not in the other two tables, that date will be filtered out from the results. If you want it, use LEFT JOIN instead of INNER JOIN.

**edit

select d.Closing_date,
       d.`Revenue gained from new deals`,
       c.`Revenue lost from churn`,
       u.`Revenue gained from upsell`
from  ( select Closing_date,
               sum(Revenue_won) as 'Revenue gained from new deals'
        from deals 
        group by Closing_date ) as d 
inner join ( select Closing_date,
                    sum(Revenue_lost) as 'Revenue lost from churn'
            from churns 
            group by Closing_date 
            ) as c on c.Closing_date=d.Closing_date
inner join ( select Closing_date,
                    sum(Revenue_won) as 'Revenue gained from upsell'
             from upsells u 
             group by Closing_date 
             ) as u on  u.Closing_date=d.Closing_date           ;

https://dbfiddle.uk/S61QeLBX