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 66 - Find the most relevant advertisement based on keywords

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.

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

Expected Results

ID  URL                         Advertisements
--- --------------------------- ----------------------------
  1 /section/pages/1/title.aspx Product 3, Product 2
  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 total number of keywords that matches between the page and advertisement.
  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('TC66_Webpages','U') IS NOT NULL BEGIN
	DROP TABLE TC66_Webpages
END
GO

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

INSERT INTO TC66_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 TC66_Webpages
GO

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

CREATE TABLE TC66_Advertisements(	
	ID INT IDENTITY,
	Product VARCHAR(MAX),
	Keywords VARCHAR(MAX)
)
GO
   
INSERT INTO TC66_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 TC66_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% · 32225
Submit a Solution
Previous Challenge
Next Challenge
5
Liked
8
Comments
41
Solutions



Submit

TSQL Challenge 66 - Find the most relevant advertisement based on keywords

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.