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`, `tag`, (`weight` / 100 * proc_per_id) correlated_weight
FROM
    article_tags at1
        INNER JOIN
    (SELECT 
        `article_id`, SUM(`weight`) / COUNT(*) proc_per_id
    FROM
        article_tags
    GROUP BY `article_id`) at2 ON at1.`article_id` = at2.`article_id`


article_id
tag
correlated_weight


1
fun
25.00000000

1
winter
25.00000000

2
fun
3.33333000

2
love
3.33333000

2
summer
26.66664000

3
fun
13.33332000

3
love
16.66665000

3
summer
3.33333000

4
fun
37.50000000

4
love
12.50000000


SELECT `article_id`,  SUM(`weight`)/COUNT(*) proc_per_id FROM article_tags GROUP BY `article_id`


article_id
proc_per_id


1
50.0000

2
33.3333

3
33.3333

4
50.0000


SELECT `tag`,  SUM(`weight`)/COUNT(*) proc_per_tag, COUNT(*) count_tag FROM article_tags GROUP BY `tag`


tag
proc_per_tag
count_tag


fun
43.7500
4

winter
50.0000
1

love
28.3333
3

summer
45.0000
2


#All Articles that have Tag fun and a corrlated weight betweeen 20 aand 40
SELECT 
    `article_id`
FROM
    (SELECT 
        at1.`article_id`,
            `tag`,
            (`weight` / 100 * proc_per_id) correlated_weight
    FROM
        article_tags at1
    INNER JOIN (SELECT 
        `article_id`, SUM(`weight`) / COUNT(*) proc_per_id
    FROM
        article_tags
    GROUP BY `article_id`) at2 ON at1.`article_id` = at2.`article_id`) t1
WHERE
    `tag` = 'fun'
        AND correlated_weight BETWEEN 20 AND 40


article_id


1

4

article_id	tag	correlated_weight
1	fun	25.00000000
1	winter	25.00000000
2	fun	3.33333000
2	love	3.33333000
2	summer	26.66664000
3	fun	13.33332000
3	love	16.66665000
3	summer	3.33333000
4	fun	37.50000000
4	love	12.50000000