MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `poster` (
  `posterID` int(11) NOT NULL,
  `imageId` varchar(255) DEFAULT NULL,
  `title` varchar(64) NOT NULL,
  `user` int(11) NOT NULL

) ENGINE=InnoDB;

ALTER TABLE `poster`
  ADD PRIMARY KEY (`posterID`);

INSERT INTO `poster` (`posterID`, `imageId`, `title`, `user`) VALUES
(71, 'C44FB5C807FFC1768271D9988FF41270054D9B6F', 'Testarossa', 1),
(72, 'E009F63FD85E1EE98BE1EED77EA4F8A8E96BEA4F', 'Lambo', 1 ),
(73, 'E192493089403C89E9DAA8B64DDBED12FFF818E5', 'Ferrari', 1),
(74, '8AFFA17561689FCA48BB9000B81AE13CA46E9A05', 'Koenisegg', 1),
(167, 'E13CA46E98AFFA17561689FCA48BB9000B81AA05', 'egg', 1);

CREATE TABLE `albums` (
  `album_id` int(11) NOT NULL,
  `imageId` varchar(255) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `user_id` int(11) NOT NULL
  ) ENGINE=InnoDB;
  
  ALTER TABLE `albums`
  ADD PRIMARY KEY (`album_id`),
  ADD KEY `fk_user_id` (`user_id`);


INSERT INTO `albums` (`album_id`, `imageId`, `title`, `user_id`) VALUES
(45, '6FJIVC7L4OKNM8YAP1XGZ23DW5SUR0QEHB9', 'Cars', 3),
(46, 'FU6D7MVTZK85SRPLIE1CXGY2WO4QAN03JHB', 'Misc', 3),
(47, '5W498FRDAQGOZN2XUIP7VL3CKJTHSBM0E16', 'SCars', 3),
(48, 'SG8MVX1DLAUHENFOI5JB6W0C2RP379QYZ4K', 'MCars', 3);

 CREATE TABLE `link` (
      `link_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
        `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;
    
   ALTER TABLE `link`
  ADD PRIMARY KEY (`link_id`),
  ADD KEY `fk_link_user_id` (`user_id`);
  


INSERT INTO `link` (`link_id`, `user_id`, `timestamp`) VALUES


    (11, 6,  '2019-10-29 09:11:25'),   
    (12, 3,  '2019-10-29 07:57:24'),
    (13, 9,  '2019-10-29 07:59:55'),
    (14, 9,  '2019-10-29 09:10:25'),
    (15, 7,  '2019-11-03 11:05:51'),
    (16, 9,  '2019-11-03 12:05:51');

CREATE TABLE `album_links` (
  `link_id` int(11) NOT NULL,
  `album_id` int(11) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE `album_links`
  ADD KEY `fk_album_id_album_links` (`album_id`),
  ADD KEY `fk_link_id_album_links` (`link_id`);
  
  ALTER TABLE `album_links`
  ADD CONSTRAINT `fk_album_id_album_links` FOREIGN KEY (`album_id`) REFERENCES `albums` (`album_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_link_id_album_links` FOREIGN KEY (`link_id`) REFERENCES `link` (`link_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

INSERT INTO `album_links` (`link_id`, `album_id`) VALUES
(12, 45),
(13, 45),
(13, 46),
(14, 47),
(15, 48);

CREATE TABLE `poster_links` (
  `link_id` int(11) NOT NULL,
  `poster_id` int(11) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE `poster_links`
  ADD KEY `fk_link_id` (`link_id`),
  ADD KEY `fk_poster_id_poster_links` (`poster_id`);
  
  ALTER TABLE `poster_links`
  ADD CONSTRAINT `fk_link_id` FOREIGN KEY (`link_id`) REFERENCES `link` (`link_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_poster_id_poster_links` FOREIGN KEY (`poster_id`) REFERENCES `poster` (`posterID`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

INSERT INTO `poster_links` (`link_id`, `poster_id`) VALUES
(11, 167),
(11, 71),   
(12, 167),
(14, 167),
(12, 71),
(16, 167),
(16, 71), 
(14, 72);
SELECT  imageId, uid
        , count(*) over (partition by imageId) as cnt FROM (
                SELECT poster.imageId,  l.user_id as uid
                FROM `poster_links` t
                INNER JOIN poster ON poster.posterID = t.poster_id
                inner join link l on t.link_id = l.link_id
                WHERE t.poster_id != 167 and t.link_id in(
SELECT pl.link_id from poster_links as pl join link as l on l.link_id = pl.link_id where poster_id = 167)
    
    
UNION ALL
                SELECT albums.imageId, l.user_id as uid
                FROM `album_links` s
                INNER JOIN albums ON albums.album_id = s.album_id
                inner join link l on s.link_id = l.link_id 
                WHERE s.album_id != 'null' and s.link_id in(
SELECT pl.link_id from poster_links as pl join link as l on l.link_id = pl.link_id where poster_id = 167)
   
     
              	) T;




imageId
uid
cnt


5W498FRDAQGOZN2XUIP7VL3CKJTHSBM0E16
9
1

6FJIVC7L4OKNM8YAP1XGZ23DW5SUR0QEHB9
3
1

C44FB5C807FFC1768271D9988FF41270054D9B6F
6
3

C44FB5C807FFC1768271D9988FF41270054D9B6F
3
3

C44FB5C807FFC1768271D9988FF41270054D9B6F
9
3

E009F63FD85E1EE98BE1EED77EA4F8A8E96BEA4F
9
1


WITH tmp as (
    SELECT poster.imageId,  l.user_id as uid
    FROM poster_links t
    JOIN poster 
        ON poster.posterID = t.poster_id
    JOIN link l 
        ON t.link_id = l.link_id
    WHERE t.poster_id <> 167 
      AND t.link_id IN (
          SELECT pl.link_id 
          FROM poster_links as pl 
          JOIN link AS l 
              ON l.link_id = pl.link_id 
          WHERE poster_id = 167
      )
    UNION ALL
    SELECT albums.imageId, l.user_id as uid
    FROM `album_links` s
    JOIN albums 
        ON albums.album_id = s.album_id
    JOIN link l 
        ON s.link_id = l.link_id 
    WHERE s.album_id <> 'null' 
      AND s.link_id in (
           SELECT pl.link_id 
           FROM poster_links AS pl 
           JOIN link AS l 
               ON l.link_id = pl.link_id 
           WHERE poster_id = 167
       )
)
SELECT imageId, uid, cnt
FROM (
    SELECT imageId, uid
         , COUNT(*) OVER (PARTITION BY imageId) as cnt
    FROM tmp t1
) as t
WHERE uid = 9
UNION ALL
SELECT imageId, uid
     , COUNT(*) OVER (PARTITION BY imageId) as cnt 
FROM tmp t1
WHERE NOT EXISTS (
    SELECT 1 FROM tmp t2
    WHERE uid = 9
      AND t1.imageId = t2.imageId
);


imageId
uid
cnt


5W498FRDAQGOZN2XUIP7VL3CKJTHSBM0E16
9
1

C44FB5C807FFC1768271D9988FF41270054D9B6F
9
3

E009F63FD85E1EE98BE1EED77EA4F8A8E96BEA4F
9
1

6FJIVC7L4OKNM8YAP1XGZ23DW5SUR0QEHB9
3
1
imageId	uid	cnt
5W498FRDAQGOZN2XUIP7VL3CKJTHSBM0E16	9	1
6FJIVC7L4OKNM8YAP1XGZ23DW5SUR0QEHB9	3	1
C44FB5C807FFC1768271D9988FF41270054D9B6F	6	3
C44FB5C807FFC1768271D9988FF41270054D9B6F	3	3
C44FB5C807FFC1768271D9988FF41270054D9B6F	9	3
E009F63FD85E1EE98BE1EED77EA4F8A8E96BEA4F	9	1