title: How to get average value in the last 6 months
body: <p>I have a problem with calculation of a variable. Lets imagine that we have a table with ID, Date and Salary. And now my task is to find out average salary in the last 6 months for each Date. Is there any other possibility how to get this value instead of Cartesian product?</p>
  
  <p>My proposed resolution:</p>
  
  <pre><code>select ID, Date, AVG(Salary2)
  from
  (
  select tab1.ID as ID, tab1.Date as Date, tab2.Date as Date2, tab2.Salary as Salary2
  from table tab1
  **JOIN**
  (select ID, Date, Salary from table) tab2
  on tab1.ID = tab2.ID
  and tab1.Date &gt;= tab2.Date
  and add_months(tab2.Date, 5) &gt;= tab1.Date
  ) group by ID, Date;
  </code></pre>
  
  <p>I assume that this approach is not effective. I think it is too time-consuming in terms of big tables. </p>
  
  <p>Is there any other approach? Or how should I optimize this script?</p>
  
  <p>Thank you</p>