0

Hey everyone I have two tables with output like this:

Month_Table

enter image description here

Transaction_Table enter image description here

I need to calculate the monthly revenue by channel and the previous month's revenue: I did this query but it is not completed

Select date_created, channel, sum(revenue) as monthly_revenue 
from transaction_table  
GROUP BY  date_created,channel

The result should be displaying monthly revenue and the month's revenue of previous month.

How can I do that?

AhlemMustapha
  • 405
  • 5
  • 12

3 Answers3

1

You could try uing a a join between you tables

Select a.month_index, a.year_month, b.channel, sum(b.revenue) as monthly_revenue 
from Month_Table a 
from transaction_table  b ON b.date_created between a.month_start_date and a.month_and_date
    amd month(b.date_created) = betwwen month(curdate()) -1 and month(curdate())
GROUP BY  a.month_index, a.year_month, b.channel
order by a.year_month desc
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • I did some changes like this and it helped me to get the monthly revenue correctly : Select M.month_index, M.year_month, T.channel, sum(t.revenue)as monthly_revenue from month_table M left join transaction_table T on T.date_created between M.month_start_date and M.month_end_date Group by M.month_index, M.year_month, T.channel Order by M.year_month desc You are amazing man thank you :) – AhlemMustapha Feb 22 '21 at 11:43
  • I have a question now how to edit that query to add the previous month revenue ? Another question : there are two months without any revenue so it gives me NULL values how can I exclude them ? I would appreciate your help , as I am still looking to improve my answer – AhlemMustapha Feb 22 '21 at 11:45
  • 1
    is not easy understand your request .. .. you should update your question and based on the sample provided you should add the expected result – ScaisEdge Feb 22 '21 at 12:36
1

try this code .

with resultTable as(
select RT.channel,RT.sumRevenue,LT.[month-start_date],LT.month_end_date,LT.year_month
from (select t.channel,sum(revenue) as sumRevenue,M.month_index from Month_Table M,Transaction_Table T
where t.date_created BETWEEN m.[month-start_date] AND m.month_end_date
group by m.month_index,t.channel) RT Join Month_Table LT on RT.month_index = LT.month_index
)
select * from resultTable

output:

enter image description here

OR use this query

with resultTable as(
select RT.channel,RT.sumRevenue,LT.[month-start_date],LT.month_end_date,LT.year_month
from (select t.channel,sum(revenue) as sumRevenue,M.month_index from Month_Table M,Transaction_Table T
where t.date_created BETWEEN m.[month-start_date] AND m.month_end_date
group by m.month_index,t.channel) RT Join Month_Table LT on RT.month_index = LT.month_index
)
select *,LAG(sumRevenue,1) OVER (PARTITION BY channel ORDER BY channel) previous_month_sales from resultTable

output:

enter image description here

Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
  • 1
    Very helpful too, thank you for providing me with another solution -> I did the following changes and it worked perfectly fine :) with resultTable as( select RT.channel,RT.sumRevenue,LT.month_start_date,LT.month_end_date,LT.year_month from (select t.channel,sum(revenue) as sumRevenue,M.month_index from month_table M,transaction_table T where t.date_created BETWEEN m.month_start_date AND m.month_end_date group by m.month_index,t.channel) RT Join Month_Table LT on RT.month_index = LT.month_index ) select * from resultTable !! I really appreciate your contribution – AhlemMustapha Feb 22 '21 at 11:56
  • 1
    Great Job , SQL hero – AhlemMustapha Feb 22 '21 at 19:34
0

Try this:

Select t1.date_created, t1.channel, sum(t1.revenue) as monthly_revenue ,sum(t2.revenue) prev_month_revenue
from transaction_table t1 left join transaction_table t2  on t1.channel = t2.channel and to_char(t1.date_created,'MM') = to_char(add_months(t2.date_created,-1),'MM')
GROUP BY  t1.date_created,t1.channel;
Rishabh Kumar
  • 2,342
  • 3
  • 13
  • 23
Learner Always
  • 1
  • 1