Algorithm Performance

StrataScratch

Meta/Facebook is developing a search algorithm that will allow users to search through their post history. You have been assigned to evaluate the performance of this algorithm.

We have a table with the
user's search term, search result positions, and whether or not the user
clicked on the search result.

Write a query that assigns
ratings to the searches in the following way:

  • If the search was not clicked for any term, assign the search with rating=1
  •  If the search was clicked but the top position of clicked terms was outside the top 3 positions, assign the search a rating=2
  •  If the search was clicked and the top position of a clicked term was in the top 3 positions, assign the search a rating=3

As a search ID can contain more than one search term, select the highest rating for that search ID. Output the search ID and its highest rating.

Example: The search_id
1 was clicked (clicked = 1) and its position is outside of the top 3 positions
(search_results_position = 5), therefore its rating is 2.


table name: fb_search_events

Input Example:

Points to be noticed:

There are multiple search terms for the same search ID.

We need to calculate the ratings for each search_term separately.Which is basically based on the below condition

  • If not clicked (clicked column value is 0) = 1 rating

  • If clicked (clicked column value is 1) and position column value is >3 = 2 ratings

  • If clicked (clicked column value is 1) and position column value is <=3 = 3 ratings


Solution Explanation:          

We are going to apply case expression to calculate ratings for each search term.

((case when clicked=0 then 1 else 0 end)+
 (case when clicked=1 and search_results_position>3 then 2 else 0 end)+
 (case  when clicked=1 and search_results_position<=3 then 3 else 0 end)) as rating

select
search_id,search_term,
((case when clicked=0 then 1 else 0 end)+
 (case when clicked=1 and search_results_position>3 then 2 else 0 end)+
 (case when clicked=1 and search_results_position<=3 then 3 else 0 end)) as rating
from fb_search_events


Output:

As a search ID can contain more than one search term, select the highest rating for that search ID. Output the search ID and its highest rating.

As from the above output we can see that the same search id is repeated multiple times for different search terms. For example, search_id 3 is repeated 2 times for search term hotel and scandal with ratings 2 and 3 respectively.

But according to question we need to find highest rating for a particular search ID. Say in this example the output should be

Search ID

Rating

3

3

As, highest rating for search_id 3 is 3.

Hence, we are going to modify our previous query

  • Add group by clause to group the result on the basis of search_id.
  • Apply max function to calculate the maximum rating for that particular search_id.


Final Solution:

select
search_id,
max((case when clicked=0 then 1 else 0 end)+
 (case when clicked=1 and search_results_position>3 then 2 else 0 end)+
  (case when clicked=1 and search_results_position<=3 then 3 else 0 end)) as rating
from fb_search_events
group by search_id
order by search_id

Final Output:

SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[fb_search_events](
	[search_id] [int] NOT NULL,
	[search_term] [varchar](50) NULL,
	[clicked] [int] NULL,
	[search_results_position] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (1, N'rabbit', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (2, N'airline', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (2, N'quality', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (3, N'hotel', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (3, N'scandal', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (5, N'rabbit', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (6, N'politics', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (10, N'rabbit', 0, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (11, N'physical', 0, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (14, N'rabbit', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (14, N'cat', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (17, N'dog', 0, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (18, N'dog', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (38, N'dog', 0, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (40, N'approach', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (40, N'national', 0, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (50, N'rabbit', 0, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (51, N'dog', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (51, N'week', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (41, N'law', 0, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (42, N'cat', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (42, N'rabbit', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (43, N'church', 0, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (44, N'station', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (44, N'hotel', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (52, N'cat', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (53, N'cat', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (56, N'dog', 0, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (57, N'cat', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (57, N'dog', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (59, N'rabbit', 0, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (60, N'cat', 0, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (63, N'rabbit', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (63, N'cat', 0, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (65, N'rabbit', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (68, N'rabbit', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (68, N'cat', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (69, N'educaiton', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (70, N'cat', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (74, N'dog', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (76, N'cat', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (77, N'rabbit', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (80, N'career', 0, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (81, N'rabbit', 0, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (82, N'rabbit', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (83, N'cat', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (84, N'dog', 1, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (85, N'boy', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (87, N'rabbit', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (90, N'dog', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (91, N'cat', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (92, N'cat', 0, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (93, N'cat', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (93, N'rabbit', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (93, N'cat', 0, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (96, N'dog', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (96, N'dog', 0, 3)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (97, N'dog', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (97, N'rabbit', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (97, N'dog', 0, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (99, N'dog', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (120, N'rabbit', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (123, N'cat', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (182, N'education', 0, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (321, N'summer', 0, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (331, N'national', 1, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (368, N'rabbit', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (649, N'cat', 0, 5)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (670, N'cat', 1, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (696, N'employee', 0, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (720, N'police', 1, 1)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (738, N'sister', 1, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (739, N'dog', 0, 2)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (857, N'cat', 0, 4)
GO
INSERT [dbo].[fb_search_events] ([search_id], [search_term], [clicked], [search_results_position]) VALUES (953, N'fire', 1, 5)
GO

Comments (0)
Questions List