create table #temp
(
title nvarchar(2000),
id int not null,
cover_url nvarchar(255),
m_description nvarchar(MAX),
mdex_url nvarchar(255),
rating nvarchar(255),
views_count nvarchar(255)
)
-----------------------------------
-----COPY DATA WITH INCLUDE TAGS---
insert into #temp
select distinct m.title,m.id,m.cover_url,m.m_description,m.mdex_url,m.rating,m.views_count from MangaGenres mg INNER JOIN Manga m on mg.manga_ID=m.ID
INNER JOIN Genres g on mg.genre_ID=g.genreID
WHERE g.genreID IN (1,2,3)
and m.id not in
(
-----------------------------------
-------------EXCLUDE---------------
select distinct m.id from MangaGenres mg INNER JOIN Manga m on mg.manga_ID=m.ID
INNER JOIN Genres g on mg.genre_ID=g.genreID
WHERE g.genreID IN (4,5,6)
)
------------------------------------
-------------AGGREGATE--------------
select #temp.title,MangaGenres.genre_ID,#temp.ID,#temp.cover_url,#temp.m_description,#temp.mdex_url,#temp.rating,#temp.views_count into #temp1 from #temp
inner join MangaGenres on MangaGenres.manga_ID = #temp.ID
select #temp1.title,string_agg(genres.genre_name,',') as Genres,#temp1.cover_url,#temp1.m_description,#temp1.mdex_url,#temp1.rating,#temp1.views_count from #temp1
inner join genres on genres.genreID = #temp1.genre_ID
where not title = ''
group by #temp1.title,#temp1.cover_url,#temp1.m_description,#temp1.mdex_url,#temp1.rating,#temp1.views_count
---------DUMPING DATA INTO #temp2----
-------------------------------------
select #temp1.title,string_agg(genres.genre_name,',') as Genres,#temp1.cover_url,#temp1.m_description,#temp1.mdex_url,#temp1.rating,#temp1.views_count into #temp2 from #temp1
inner join genres on genres.genreID = #temp1.genre_ID
group by #temp1.title,#temp1.cover_url,#temp1.m_description,#temp1.mdex_url,#temp1.rating,#temp1.views_count;
---------------------------------
------------SORTING-----------------
with ResultGenreMappingCTE (ANIME_TITLE, GENRE_NAME)
as (
    select t1.title, t2.[value] as GENRE_NAME
    from #temp2 as t1
    cross apply STRING_SPLIT(t1.Genres, ',') as t2
),
TargetGenreCTE (GENRE_NAME)
as (
    select * from STRING_SPLIT('1,2,3', ',')
),
MatchMakeCTE (ANIME_TITLE, MATCH_SCORE)
as (
    select t1.ANIME_TITLE, count(t1.GENRE_NAME) as MATCH_SCORE
    from ResultGenreMappingCTE as t1
    inner join TargetGenreCTE as t2 on t1.GENRE_NAME = t2.GENRE_NAME
    group by t1.ANIME_TITLE
) select *
from #temp2 as t1
inner join MatchMakeCTE as t2 on t1.title = t2.ANIME_TITLE
order by t2.MATCH_SCORE desc
-----------------------------------
drop table #temp1
drop table #temp
drop table #temp2