How would you design a search algorithm that returns weighted search results?
Assume each candidate result has a text relevance score and a popularity score. Write a SQL query that returns active results for one search phrase, calculating a weighted score using 70% text relevance and 30% popularity. Treat a missing popularity score as zero.
result_id, title, and weighted_scorehvac repairweighted_score descending, then result_id ascending for ties| Column | Type | Description |
|---|---|---|
| result_idPK | INT | Unique identifier for a search result |
| search_query | VARCHAR(100) | Search phrase associated with the candidate result |
| title | VARCHAR(255) | Title displayed for the result |
| result_type | VARCHAR(50) | Type of searchable result |
| relevance_score | DECIMAL(5,2) | Text matching score from 0.00 to 1.00 |
| popularity_score | DECIMAL(5,2) | Popularity score from 0.00 to 1.00 |
| is_active | BOOLEAN | Whether the result is eligible for display |