title: SQL selecting average score over range of dates
body: <p>I have 3 tables:</p>
  
  <pre><code>doctors (id, name) -&gt; has_many:
      patients (id, doctor_id, name) -&gt; has_many:
          health_conditions (id, patient_id, note, created_at)
  </code></pre>
  
  <p>Every day each patient gets added a health condition with a note from 1 to 10 where 10 is a good health (full recovery if you may).</p>
  
  <p>What I want to extract is the following 3 statistics for the last 30 days (month):
   - how many patients got better
   - how many patients got worst
   - how many patients remained the same</p>
  
  <p>These statistics are global so I don't care right now of statistics per doctor which I could extract given the right query.</p>
  
  <p>The trick is that the query needs to extract the current health_condition note and compare with the average of past days (this month without today) so one needs to extract today's note and an average of the other days excluding this one.</p>
  
  <p>I don't think the query needs to define who went up/down/same since I can loop and decide that. Just today vs. rest of the month will be sufficient I guess.</p>
  
  <p>Here's what I have so far which obv. doesn't work because it only returns one result due to the limit applied:</p>
  
  <pre><code>SELECT
      p.id,
      p.name,
      hc.latest,
      hcc.average
  FROM
      pacients p
  INNER JOIN (
          SELECT
              id,
              pacient_id,
              note as LATEST
          FROM
              health_conditions
          GROUP BY pacient_id, id
          ORDER BY created_at DESC
          LIMIT 1
      ) hc ON(hc.pacient_id=p.id)
  INNER JOIN (
          SELECT
              id,
              pacient_id,
              avg(note) AS average
          FROM
              health_conditions
          GROUP BY pacient_id, id
      ) hcc ON(hcc.pacient_id=p.id AND hcc.id!=hc.id)
  WHERE
      date_part('epoch',date_trunc('day', hcc.created_at))
      BETWEEN
          (date_part('epoch',date_trunc('day', hc.created_at)) - (30 * 86400))
      AND
          date_part('epoch',date_trunc('day', hc.created_at))
  </code></pre>
  
  <p>The query has all the logic it needs to distinguish between what is latest and average but that limit kills everything. I need that limit to extract the latest result which is used to compare with past results.</p>