Sql Selecting Average Score Over Range Of Dates

I have 3 tables: doctors (id, name) -> has_many: patients (id, doctor_id, name) -> has_many: health_conditions (id, patient_id, note, created_at) Every day each

Solution 1:

Something like this assuming created_at is of type date

select p.name,
       hc.note as current_note,
       av.avg_note
from patients p
   join health_conditions hc on hc.patient_id = p.id
   join (select patient_id, 
             avg(note) as avg_note
      from health_conditions hc2
      where created_at between current_date - 30 and current_date - 1
      groupby patient_id
    ) avg on t.patient_id = hc.patient_id
where hc.created_at = current_date;

This is PostgreSQL syntax. I'm not sure if MySQL supports date arithmetics the same way.

Edit:

This should get you the most recent note for each patient, plus the average for the last 30 days:

select p.name,
       hc.created_at as last_note_date
       hc.note as current_note,
       t.avg_note
from patients p
   join health_conditions hc 
     on hc.patient_id = p.id
    and hc.created_at = (selectmax(created_at) 
                         from health_conditions hc2 
                         where hc2.patient_id = hc.patient_id)
   join (
      select patient_id, 
             avg(note) as avg_note
      from health_conditions hc3
      where created_at between current_date - 30and current_date - 1groupby patient_id
    ) t on t.patient_id = hc.patient_id

Solution 2:

SELECTSUM(delta <0) AS worsened,
       SUM(delta =0) AS no_change,
       SUM(delta >0) AS improved
FROM  (
  SELECT   patient_id,
           SUM(IF(DATE(created_at) = CURDATE(),note,NULL))
         -AVG(IF(DATE(created_at) < CURDATE(),note,NULL)) AS delta
  FROM     health_conditions
  WHEREDATE(created_at) BETWEEN CURDATE() -INTERVAL1MONTHAND CURDATE()
  GROUPBY patient_id
) t