Recommend system evolution

evolution.sql

-- 1: Select Top-3 tags by user
SELECT
	pt.tag_id,
	count(pt.tag_id) as c
FROM users_views uv
LEFT JOIN publications_tags pt ON pt.publication_id = uv.publication_id
WHERE uv.user_id = 1
GROUP BY pt.tag_id
ORDER BY c DESC
LIMIT 3
;

-- 2: Select Top-3 tags by user by last 30 days
SELECT
    pt.tag_id,
    count(pt.tag_id) AS c
FROM users_views uv
LEFT JOIN publications_tags pt ON pt.publication_id = uv.publication_id
WHERE uv.user_id = 1
  AND uv.view_date >= NOW() - INTERVAL '30 days'
GROUP BY pt.tag_id
ORDER BY c DESC
LIMIT 3
;

-- 3: Select Top-3 tags among all users by each user
CREATE OR REPLACE VIEW v_user_tag_rank AS 
SELECT * FROM (
	SELECT
		uv.user_id,
		pt.tag_id,
		ROW_NUMBER() over (
			PARTITION BY uv.user_id
			ORDER BY count(pt.tag_id) DESC
		) as rank
	FROM users_views uv
	LEFT JOIN publications_tags pt ON pt.publication_id = uv.publication_id
	WHERE uv.view_date >= now() - INTERVAL '30 days'
	GROUP BY 1, 2
) q
WHERE q.rank <= 3
;

SELECT * FROM v_user_tag_rank;

fill-data.sql

INSERT INTO users (name) VALUES 
('John Smith'),
('Emily Johnson'),
('Michael Brown'),
('Sarah Williams'),
('David Jones');

-- Publications
INSERT INTO publications (title) VALUES
('Introduction to SQL'),
('Advanced Database Design'),
('Web Development Basics'),
('Data Science Fundamentals'),
('Machine Learning Concepts'),
('Cloud Computing Explained'),
('Cybersecurity Essentials'),
('Cybersecurity Essentials part 2')
;

-- Tags
INSERT INTO tags (name) VALUES
('Database'),
('Programming'),
('Web'),
('Data'),
('AI'),
('Cloud'),
('Security'),
('Beginner'),
('Advanced');

-- Publication-Tag relationships
INSERT INTO publications_tags (publication_id, tag_id) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 3),
(6, 3),
(7, 4),
(8, 2);

-- User views
INSERT INTO users_views (user_id, publication_id, view_date) VALUES
(1, 1, '2025-03-10 09:15:00'),
(1, 2, '2025-06-05 10:45:00'),
(1, 3, '2025-06-12 14:30:00'),
(1, 4, '2025-06-05 10:45:00'),
(1, 5, '2025-06-06 10:45:00'),
(1, 6, '2025-06-06 10:45:00'),
(1, 7, '2025-06-06 10:45:00'),

(2, 2, '2025-01-15 11:20:00'),
(2, 5, '2025-06-18 16:45:00'),

(3, 4, '2025-02-20 10:00:00'),
(3, 7, '2025-06-15 13:10:00'),
(3, 3, '2025-06-19 10:00:00'),
(3, 6, '2025-06-15 13:10:00'),
(3, 8, '2025-06-19 10:00:00')
;

table-creation.sql

CREATE TABLE publications (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE publications_tags (
    publication_id INTEGER REFERENCES publications(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (publication_id, tag_id)
);

CREATE TABLE users_views (
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    view_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    publication_id INTEGER REFERENCES publications(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, publication_id, view_date)
);