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.

boost_tag
tag_weighted_results.sql
Replay: real traced execution (multi-file project)
CREATE TABLE search_choice AS WITH params(boost_tag) AS (VALUES ('guide')) 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 ('intro')) 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;

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(boost_tag) AS (VALUES ('guide')) SELECT boost_tag FROM params;2CREATE TABLE resources (id INTEGER, title TEXT, tag TEXT, base_score INTEGER);
    values this step1 rowchoice

  2. resources ← 4 rows

    2CREATE TABLE resources (id INTEGER, title TEXT, tag TEXT, base_score INTEGER);3INSERT INTO resources VALUES (1, 'Intro to SQL', 'intro', 8), (2, 'Window guide', 'guide', 7), (3, 'Index checklist', 'guide', 6), (4, 'History overview', 'intro', 5);4SELECT 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;
    values this step4 rowsresources

  3. result ← 4 rows

    3INSERT INTO resources VALUES (1, 'Intro to SQL', 'intro', 8), (2, 'Window guide', 'guide', 7), (3, 'Index checklist', 'guide', 6), (4, 'History overview', 'intro', 5);4SELECT 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;
    values this step4 rowsresult

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(boost_tag) AS (VALUES ('intro')) SELECT boost_tag FROM params;2CREATE TABLE resources (id INTEGER, title TEXT, tag TEXT, base_score INTEGER);
    values this step1 rowchoice

  2. resources ← 4 rows

    2CREATE TABLE resources (id INTEGER, title TEXT, tag TEXT, base_score INTEGER);3INSERT INTO resources VALUES (1, 'Intro to SQL', 'intro', 8), (2, 'Window guide', 'guide', 7), (3, 'Index checklist', 'guide', 6), (4, 'History overview', 'intro', 5);4SELECT 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;
    values this step4 rowsresources

  3. result ← 4 rows

    3INSERT INTO resources VALUES (1, 'Intro to SQL', 'intro', 8), (2, 'Window guide', 'guide', 7), (3, 'Index checklist', 'guide', 6), (4, 'History overview', 'intro', 5);4SELECT 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;
    values this step4 rowsresult
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.