Search and Ranking

Tag Weights

Boost Preferred Categories

A ranked result can boost rows that match a user-selected tag while still showing the original base score.

Program

Play the script to choose the boosted tag and compare the adjusted ranking.

tag_weighted_results.sql 
CREATE TABLE search_choice AS WITH params(boost_tag) AS (VALUES ()) SELECT boost_tag FROM params;
CREATE TABLE resources (id INTEGER, title TEXT, tag TEXT, base_score INTEGER);
INSERT INTO resources VALUES (1, 'Intro to SQL', 'intro', 8), (2, 'Window guide', 'guide', 7), (3, 'Index checklist', 'guide', 6), (4, 'History overview', 'intro', 5);
SELECT id, title, tag, base_score, base_score + CASE WHEN tag = (SELECT boost_tag FROM search_choice) THEN 3 ELSE 0 END AS adjusted_score FROM resources ORDER BY adjusted_score DESC, id;

CREATE TABLE search_choice AS WITH params(boost_tag) AS (VALUES ()) SELECT boost_tag FROM params;
CREATE TABLE resources (id INTEGER, title TEXT, tag TEXT, base_score INTEGER);
INSERT INTO resources VALUES (1, 'Intro to SQL', 'intro', 8), (2, 'Window guide', 'guide', 7), (3, 'Index checklist', 'guide', 6), (4, 'History overview', 'intro', 5);
SELECT id, title, tag, base_score, base_score + CASE WHEN tag = (SELECT boost_tag FROM search_choice) THEN 3 ELSE 0 END AS adjusted_score FROM resources ORDER BY adjusted_score DESC, id;
boost The selected tag receives a fixed score boost.
base score Keeping `base_score` visible makes the adjustment easy to inspect.
deterministic tie-break `ORDER BY adjusted_score DESC, id` makes equal scores stable.