TSQL Challenge 5
The context
You have to do some reporting on blog posts on searches by keywords.
The blog engine stores the tags defining the post in one comma separated values column.
Sample Blog post data:
id name tags
----------- -------------------- --------------------
1 post 1 sql,profiler,table
2 post 2 sql,performance
3 post 3 profilter
4 post 4 view,table
Each time people make a search by keywords, you store it in a dedicated table of filtered searches. As for blog tags, the values are stored in only one comma separated column. This historical search values will be used to add an auto-complete search field on the blog.
Sample Filtered searches:
id data
----------- --------------------------------
1 sql,performance
2 profiler
3 table,performance,view
The Challenge
You have to find for each search the relevant posts. A relevant post is a post that was tagged with at least one of the keywords of the search.
You have also to provide a relevance index with the number of corresponding tags found on the post.
Here is the result table you should have to produce regarding the previous sample data.
Posts by Filtered searches with relevance:
ID data name tags RELEVANCE
-- ----------------------- -------- -------------------- -----------
1 sql,performance post 2 sql,performance 2
1 sql,performance post 1 sql,profiler,table 1
2 profiler post 1 sql,profiler,table 1
3 table,performance,view post 4 view,table 2
3 table,performance,view post 1 sql,profiler,table 1
3 table,performance,view post 2 sql,performance 1
- Write a single TSQL Query to produce the required output
- Use the sample code given below and make sure that your query runs with the given sample code. This will help us to speed up the review process.
- There is a forum linked to 'TSQL Challenges' for general questions and discussions on the 'TSQL Challenges' series.
- For any questions specific to 'TSQL Challenge 5', visit this thread.
Sample Data:
DECLARE @filter TABLE (id INT IDENTITY, data nvarchar(32))
insert into @filter (data) values ('sql,performance')
insert into @filter (data) values ('profiler')
insert into @filter (data) values ('table,performance,view')
DECLARE @blog TABLE (id INT IDENTITY, name nvarchar(20), tags nvarchar(20))
insert into @blog (name,tags) values ('post 1','sql,profiler,table')
insert into @blog (name,tags) values ('post 2','sql,performance')
insert into @blog (name,tags) values ('post 3','profilter')
insert into @blog (name,tags) values ('post 4','view,table')