Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 69 - Find the most relevant advertisement based on keywords – Part 2

A website wants to display most relevant ads on each of its web pages based on the keywords associated with each page. Your job is to write a TSQL query that returns the advertisements most relevant to each web page given in the source table.

This challenge is Version 2 of the TSQL Challenge 66. This version needs to look at the position of the keyword to decide the importance of that keyword. So a page with keywords: "Backup, Compression, SQL Server" should assign a higher relevance to an advertisement having "Compression, Backup" than "SQL Server, Compression".

Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Sample Data

Web Pages Table

ID  URL                         Keywords                     Ads To Display
--- --------------------------- ---------------------------- --------------
  1 /section/pages/1/title.aspx SQL Server, Performance, DBA 2
  2 /section/pages/2/title.aspx Database, Backup, TSQL       2
  3 /section/pages/3/title.aspx Disaster Recovery, DBA       1

Advertisements Table

ID  Product   Keywords
--- --------- ----------------------------------
  1 Product 1 Backup, Disaster Recovery
  2 Product 2 Performance, DBA
  3 Product 3 SQL Server, Performance, DBA
  4 Product 4 SQL Server, DBA
  5 Product 5 SQL Server, Disaster Recovery
  6 Product 6 DBA, TSQL
  7 Product 7 Database, DBA, Backup

Relavance should be applied to both the web page keywords and the product keywords. Thus if a web page has 4 keywords then they are assumed to have relevance values of 4,3,2,1 respectively. Same for product keywords. When a web page keyword matches a product keyword then the "value of the match" is the web page keyword relevance value multiplied by the product keyword relevance value.

For example, the relavance for Web page 1 (/section/pages/1/title.aspx) for Product 4 will be 7. Becasue web page keywords "SQL Server, Performance, DBA" will be assigned weight 3, 2 and 1 respectively. and Product 4 keywords "SQL Server, DBA" will be assigned weight 2, 1 respectively. So Relavance = 3*2 + 1*1 = 7

Expected Results

ID  URL                         Advertisements  
--- --------------------------- ----------------------------    
1 /section/pages/1/title.aspx Product 3, Product 4    
2 /section/pages/2/title.aspx Product 7, Product 1    
3 /section/pages/3/title.aspx Product 1  

Rules

  1. Maximum number of keywords in a page or advertisement is limited to 24.
  2. Relevance should be calculated based on the position of the keyword to decide the importance of that keyword.
  3. If more than one advertisement matches with a page with same relevance rank, then the advertisement with a lower ID value should get preference.
  4. The column "Ads to display" controls the number of ads each page displays.
  5. The output should be ordered by ID. The order of Products list in Advertisements column should be by the the number of occurrences matches in decending order. If more than one advertisement matches with a page with same relevance rank, then the advertisement with a lower ID value should be listed first.
  6. There will be no duplicates in any keyword list.
  7. The separator in Keywords and Advertisements lists is comma+space.
  8. The Product will be unique in Advertisements Table.

Sample Script

Use the TSQL Script given below to generate the source tables and fill them with sample data.

IF OBJECT_ID('TC69_Webpages','U') IS NOT NULL BEGIN
	DROP TABLE TC69_Webpages
END
GO

CREATE TABLE TC69_Webpages(
	ID INT IDENTITY,
	URL VARCHAR(255),
	Keywords VARCHAR(MAX),
	AdsToDisplay INT
)
GO

INSERT INTO TC69_Webpages(URL,Keywords,AdsToDisplay)
SELECT '/section/pages/1/title.aspx','SQL Server, Performance, DBA',2 UNION ALL
SELECT '/section/pages/2/title.aspx','Database, Backup, TSQL',2 UNION ALL
SELECT '/section/pages/3/title.aspx','Disaster Recovery, DBA',1 


SELECT * FROM TC69_Webpages
GO

IF OBJECT_ID('TC69_Advertisements','U') IS NOT NULL BEGIN
	DROP TABLE TC69_Advertisements
END
GO

CREATE TABLE TC69_Advertisements(	
	ID INT IDENTITY,
	Product VARCHAR(MAX),
	Keywords VARCHAR(MAX)
)
GO
   
INSERT INTO TC69_Advertisements(Product,Keywords)
SELECT 'Product 1','Backup, Disaster Recovery' UNION ALL
SELECT 'Product 2','Performance, DBA' UNION ALL
SELECT 'Product 3','SQL Server, Performance, DBA' UNION ALL
SELECT 'Product 4','SQL Server, DBA' UNION ALL
SELECT 'Product 5','SQL Server, Disaster Recovery' UNION ALL
SELECT 'Product 6','DBA, TSQL' UNION ALL
SELECT 'Product 7','Database, DBA, Backup'

SELECT * FROM TC69_Advertisements
GO

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Jacob Sebastian
1 · 100% · 32220
Submit a Solution
Previous Challenge
Next Challenge
5
Liked
4
Comments
36
Solutions



Submit

TSQL Challenge 69 - Find the most relevant advertisement based on keywords – Part 2

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.