How to get difference of sales pre and post 12 weeks in SQL?

Question

I have data like below

category    date        sales
chocs       2022-09-02    20
biscuits    2022-09-02    90
popcorn     2022-09-02    45
popcorn     2021-09-02    85
chocs       2021-09-02    35
biscuits    2021-09-02    75

I'm trying to get the sales of 12 pre and post weeks for every category. For example sales of chocs category on 2022-09-02 is 20. I want to get 12 pre and post weeks sales for same. Expected output is

 category    date          sales   12_weeks_before_sales                  12_weeks_after_sales
 chocs       2022-09-02    20      sales of 12 weeks before date i.e. 2022-09-02
 biscuits    2022-09-02    90      sales of 12 weeks before date i.e. 2022-09-02
 popcorn     2022-09-02    45      sales of 12 weeks before date i.e. 2022-09-02
 popcorn     2021-09-02    85      sales of 12 weeks before date i.e. 2021-09-02
 chocs       2021-09-02    35      sales of 12 weeks before date i.e. 2021-09-02
 biscuits    2021-09-02    75      sales of 12 weeks before date i.e. 2021-09-02

I have the complete data before that date, but unable to write query. Can anyone help me with this?

Answer 1

Try this, hope it helps.

select t.category,
       t.saledate,
       sum(t.sales) current_sales,
       p.pastdate past_date,
       p.pastsales past_sales
  from sales t
  join (select t1.category,
               t1.saledate pastdate,
               sum(t1.sales) pastsales
          from sales t1 
         where t1.saledate = DATEADD(week, -12, t.saledate)
           and t1.category = t.category) p 
 group by 1,2,4,5