MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `article_tags` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `article_id` bigint(20) DEFAULT NULL,
  `tag` varchar(20) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `article_tags` (`article_id`, `tag`, `weight`)
VALUES
  (1, 'fun', 50),
  (1, 'winter', 50),
  (2, 'fun', 10),
  (2, 'love', 10),
  (2, 'summer', 80),
  (3, 'fun', 40),
  (3, 'love', 50),
  (3, 'summer', 10)
INSERT INTO `article_tags` (`article_id`, `tag`, `weight`)
VALUES
  (4, 'fun', 75),
  (4, 'love', 25)
SELECT 
    at1.`article_id`, at1.`tag`, (at1.`weight` / 100 * at2. `weight` ) correlated_weight
FROM
    article_tags at1
        INNER JOIN
    (SELECT 
        tag ,`weight` 
    FROM
        article_tags
    WHERE `article_id` = 4) at2 ON at1.`tag` = at2.`tag`AND at1.`tag` = 'fun' AND at1.`article_id` = 1


article_id
tag
correlated_weight


1
fun
37.5000


SELECT 
    at1.`article_id`, at1.`tag`, (at1.`weight` / 100 * at2. `weight` ) correlated_weight
FROM
    article_tags at1
        INNER JOIN
    (SELECT 
        tag ,`weight` 
    FROM
        article_tags
    WHERE `article_id` = 4) at2 ON at1.`tag` = at2.`tag`
WHERE at1.`tag` = 'fun' AND at1.`article_id` = 1


article_id
tag
correlated_weight


1
fun
37.5000


SELECT 
    at1.`article_id`
    , at1.`tag`
    , (at1.`weight` / 100 * (SELECT `weight` FROM  article_tags
                             WHERE `article_id` = 4 AND tag = at1.tag) ) correlated_weight
FROM
    article_tags at1
WHERE at1.`tag` = 'fun' AND at1.`article_id` = 1


article_id
tag
correlated_weight


1
fun
37.5000