Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment