Skip to content

Instantly share code, notes, and snippets.

Embed
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
You can’t perform that action at this time.