Interview Query

Search Ratings

26
Have you seen this question before?

You’re given a table that represents search results from searches on Facebook. The query column is the search term, the position column represents each position the search result came in, and the rating column represents the human rating of the search result from 1 to 5 where 5 is high relevance and 1 is low relevance.

Example:

Input:

search_results table

Column Type
query VARCHAR
result_id INTEGER
position INTEGER
rating INTEGER
  1. Write a query to compute a metric to measure the quality of the search results for each query. 

  2. You want to be able to compute a metric that measures the precision of the ranking system based on position. For example, if the results for dog and cat are…

query result_id position rating notes
dog 1000 1 2 picture of hotdog
dog 998 2 4 dog walking
dog 342 3 1 zebra
cat 123 1 4 picture of cat
cat 435 2 2 cat memes
cat 545 3 1 pizza shops

…we would rank ‘cat’ as having a better search result ranking precision than ‘dog’ based on the correct sorting by rating.

Write a query to create a metric that can validate and rank the queries by their search result precision. Round the metric (avg_rating column) to 2 decimal places.

Output:

Column Type
query VARCHAR
avg_rating FLOAT
Next question: Creating Companies Table
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query