title: using LAG to compare the data from today and 7 days ago (not between)
body: <p>I am currently trying to compare aggregated numbers from today and exactly 7 days ago (not between today and 7 days ago, but instead simply comparing these two <strong>discrete</strong> dates).
  I already have a way of doing it using a lot of subqueries, but the performance is bad, and I am now trying to optimize.</p>
  <p>This is what I have come up with so far (sample query, not with real table names and columns due to confidentiality):</p>
  <pre><code>Select current_date, previous_date, current_sum, previous_sum, percentage
  From   (Select date as current_date, sum(numbers) as current_sum, 
               lag (sum(numbers)) over (partition by date order by date) as previous_sum,
               (Select max(date)-7 From t1 ) as previous_date,
               (current_sum - previous_sum)*100/current_sum as percentage
          From t1 where date&gt;=sysdate-7 group by date,previous_date)
  </code></pre>
  <p>But I am definitely doing something wrong since in the output the previous_sum appears null, and naturally the percentage too.</p>
  <p>Any ideas on what I am doing wrong? I haven't used LAG before so it must be something there.
  Thanks!</p>