21 lines
520 B
SQL
21 lines
520 B
SQL
with common_rows as (
|
|
select count(*) as match_count
|
|
from achievements as t1
|
|
join achievements as t2 on t1.id = t2.id
|
|
and t1.date = t2.date
|
|
where t1.name = $1
|
|
and t2.name = $2
|
|
),
|
|
total_rows as (
|
|
select count(distinct id) as total_count
|
|
from achievements
|
|
where name in ($1, $2)
|
|
),
|
|
similarity as (
|
|
select (match_count * 100.0 / total_count) as similarity_percentage
|
|
from common_rows,
|
|
total_rows
|
|
)
|
|
insert into cached (character1, character2, similarity)
|
|
select $1, $2, similarity_percentage
|
|
from similarity; |