Search code examples
sqldatabasepgadminrevenue

Compose a SQL query that produces monthly revenue by channel and the previous month's revenue

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?

Solution

  • 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