How to get similar posts by tags through an intermediate table?

Question

MySQL

There is a Post table. There is also a Post_Tags and Tags table:
Post_Tags: Post_id, Tag_id
Tags: Tag_id, Name
For example, there is a record that has 5 tags: Tag1, Tag2, Tag3, Tag4, Tag5
Therefore, there are 5 records in the Post_Tags table (post ID, tag ID).
The task is to get all the posts that match the tags sorted by count.
That is, all posts that have any of the Tag1, Tag2, Tag3, Tag4, Tag5 tags.
I'm scratching my head, tell me how to do this?

Answer 1

as far as I understand, you need to select all posts whose tags correspond as closely as possible to the tags of the current post (and the most similar ones should be on top)

select p.*, count(pt.id) similar_tags_count
from posts p
inner join posts_tags pt on pt.post_id = p.id and pt.tag_id in (select tag_id from posts_tags where post_id = 1) #id текущего поста
where p.id != 1 #id текущего поста
group by p.id
order by count(pt.id) desc

1 is the id of the current post in the
similar_tags_qty request - the number of matching tags