title: Getting previous year aggregated values in column in SQL
body: <p>I have data at sale_date grain and i want to bring the aggregated sale number for previous two year corresponding to the current date. I am maintaining a date dimension table which will have previous year dates across each calendar day.</p>
  <p>The solution i have trying is using cross join with the main table while joining with the date dimension. But since the table will grow in future, the cross join will cause performance issues. Looking for some improvement suggestions</p>
  <pre><code>    with date_dim as (
      select '2022-08-25'::date as calendar_day,
             '2021-08-01'::date as prev_yr_date,
             '2020-07-01'::date as prev_two_yr_date
      union all
      select '2021-08-01'::date as calendar_day,
             '2020-08-01'::date as prev_yr_date,
             '2019-07-01'::date as prev_two_yr_date
      union all
      select '2020-07-01'::date as calendar_day,
             '2019-08-01'::date as prev_yr_date,
             '2018-07-01'::date as prev_two_yr_date
  ),
       main_data as (
           select '2022-08-25'::date as sale_date, 10 as sale
           union all
           select '2021-08-01'::date as sale_date, 20 as sale
           union all
           select '2020-07-01'::date as sale_date, 30 as sale
       )
  select a.sale_date,
         sum(case when b.sale_date = d.calendar_day then b.sale else 0 end)     as current_Sale,
         sum(case when b.sale_date = d.prev_yr_date then b.sale else 0 end)     as prev_yr_sale,
         sum(case when b.sale_date = d.prev_two_yr_date then b.sale else 0 end) as prev_two_yr_sale
  
  from main_data a
           cross join main_data b
           inner join date_dim d on a.sale_date = d.calendar_day
  group by 1
  </code></pre>
  <p>Input data:</p>
  <p><a href="https://i.stack.imgur.com/pPXMz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pPXMz.png" alt="enter image description here" /></a></p>
  <p>Output from my query:</p>
  <p><a href="https://i.stack.imgur.com/M92U6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M92U6.png" alt="enter image description here" /></a></p>