Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
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 =
where lss.user_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( OVER (PARTITION BY ss.user_id) as count
from latest_symptom_surveys ss
join symptom_reports sr on sr.symptom_survey_id =
left join target on target.symptom_id = sr.symptom_id
join users u on = ss.user_id
where u.disease_id = #{self.disease_id}
and <> #{}
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