pgtips.sql

WITH
      latest_symptom_surveys as (
        select * from (select *, rank() over (PARTITION BY user_id order by date desc, id desc) from symptom_surveys ss) ranked
        where rank = 1
      ),
      target as (
        select symptom_id, symptom_severity from latest_symptom_surveys lss
        join symptom_reports sr on sr.symptom_survey_id = lss.id
        where lss.user_id = #{self.id}
      ),
      similarities as (
        select distinct
        ss.user_id as id,
        sum((sr.symptom_severity - coalesce(target.symptom_severity,0)) ^ 2) OVER (PARTITION BY ss.user_id),
        count(sr.id) OVER (PARTITION BY ss.user_id) as count

        from latest_symptom_surveys ss
        join symptom_reports sr on sr.symptom_survey_id = ss.id
        left join target on target.symptom_id = sr.symptom_id
        join users u on u.id = ss.user_id
        where u.disease_id = #{self.disease_id}
              and u.id <> #{self.id}
      )
  select id, sum from similarities
    order by 2,1
    limit 10