Question 114761 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – how to compare sales current week vs same week last months Postgres

I have the following table "sales"

date revenue
2022-06-01(Week 1) 100
2022-06-08(week 2) 200
2022-05-01(week 1) 800
2022-05-08(week 2) 900

and I want to compare sales current week vs the same week last month and get the following result:

date revenue June revenue May
2022-06-01(Week 1) 100 800
2022-06-08(week 2) 200 900
Tags:

2

Answers


  1. 0 
    select rn  as week_number
      ,[5] as revenue_may
      ,[6] as revenue_june
from  (
       select revenue
             ,row_number() over(partition by month(dte) order by dte) as rn
             ,month(dte) as mnth
      from t
      )tmp
pivot
(max(revenue) for mnth in([5], [6])) pvt
    week_number revenue_may revenue_june
    1 800 100
    2 900 200

    Fiddle

    Login or Signup to reply.
  2. 0

    Based on given data, following is for postgresql –

    with data as (
    select date1,substring(date1 , 1,10) as date_col,
    substring(date1 , '[[:alpha:]]+[[:digit:]]+') as week_col,
    revenue
    from revenue_tab
)
select 
    d1.date1, 
    d1.revenue as june_rev,
    d2.revenue as june_rev
from
    (select * from data 
     where extract(month from to_date(date_col,'yyyy-mm-dd'))=6) d1
join
    (select * from data 
     where extract(month from to_date(date_col,'yyyy-mm-dd'))=5) d2
ON d1.week_col = d2.week_col
AND extract(year from to_date(d1.date_col,'yyyy-mm-dd')) 
    = extract(year from to_date(d2.date_col,'yyyy-mm-dd'))
 ;

    DB fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search