public
Last active

Similarity SQL

  • Download Gist
like_me.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
WITH
target as (
select * from users where id = 12345
),
similarities as (
select
users.id as user_id,
10 * array_upper(users.condition_ids & target.condition_ids,1) / array_upper(users.condition_ids | target.condition_ids,1) as comorbidity_match,
15 * case when users.condition_ids[1] = target.condition_ids[1] then 1 else 0 end as primary_condition_match,
5 * case when users.birth_date is null then 0 else float8larger(float8(0), 40 - (abs(extract(years from age(users.birth_date, target.birth_date))))) / 40 end as age_match,
5 * case when users.sex = target.sex and users.sex is not null then 1 else 0 end as sex_match,
10 * (greatest(0,90 - date_part('days', (now() - users.last_activity_time)))) / 90 as activity_match,
2 * case when users.lat is null then 0 else .001 * float8larger(float8(0), 1000 - sqrt(pow(69.1 * (users.lat - target.lat),2) + pow(53.0 * (users.lng - target.lng),2))) end as distance_match
from users, target
where
users.verified is true and users.deleted is false
and users.role_id = 1
and users.deceased_date is null
and users.id <> 12345
),
matches as (
select *,
(0 + comorbidity_match+primary_condition_match+age_match+sex_match+activity_match+distance_match) as total_sum
from similarities
)
select * from matches
order by total_sum desc
limit 100
offset 0

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.