Similarity SQL

like_me.sql

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