title: SQL Calculating Moving Average Crossover of variable lengths
body: <p>I am trying to calculate moving averages crossover with variable dates.</p>
  
  <p>My database is structured:</p>
  
  <pre><code>id
  stock_id
  date
  closing_price
  </code></pre>
  
  <p>And:</p>
  
  <pre><code>stock_id
  symbol
  </code></pre>
  
  <p>For example, I'd like to find out if the average price going back X days ever gets greater than the average price going back Y days within the past Z days. Each of those time periods is variable. This needs to be run for every stock in the database (about 3000 stocks with prices going back 100 years). </p>
  
  <p>I'm a bit stuck on this, what I currently have is a mess of SQL subqueries that don't work because they cant account for the fact that X, Y, and Z can all be any value (0-N). That is, in the past 5 days I could be looking for a stock where the 40 day average is > than 5, or the 5 > 40. Or I could be looking over the past 40 days to find stocks where the 10 day moving average is > 30 day moving average. </p>
  
  <p>This question is different from the other questions as there is variable short and long dates as well as a variable term. </p>