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

1. Write a single TSQL Query to produce the required output
2. 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.
3. Send your entries to jacob@beyondrelational.com
4. Do not paste the code in the email body. Include it as an attachment (.sql file)
5. Mention ‘TSQL Challenge 5’ in the email subject
6. Last Date: 1 May 2009
7. There is a forum linked to 'TSQL Challenges' for general questions and discussions on the 'TSQL Challenges' series.
8. 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')

Related Posts

Comments