-- SCHEMA
CREATE TABLE users (
  id INTEGER PRIMARY KEY
);

CREATE TABLE posts (
  user_id INTEGER,
  kind VARCHAR(40) -- only 'article', 'promotion', 'link', 'image',
);

INSERT INTO users(id) VALUES (1);
INSERT INTO users(id) VALUES (2);

INSERT INTO posts(user_id, kind) VALUES (1, 'article');
INSERT INTO posts(user_id, kind) VALUES (1, 'article');
INSERT INTO posts(user_id, kind) VALUES (1, 'promotion');
INSERT INTO posts(user_id, kind) VALUES (1, 'image');
INSERT INTO posts(user_id, kind) VALUES (1, 'image');
INSERT INTO posts(user_id, kind) VALUES (1, 'link');

INSERT INTO posts(user_id, kind) VALUES (2, 'link');
INSERT INTO posts(user_id, kind) VALUES (2, 'link');

-- QUERY

SELECT u.id,
COUNT(pa.user_id) AS article_count,
COUNT(pp.user_id) AS promotion_count,
COUNT(pl.user_id) AS promotion_link,
COUNT(pi.user_id) AS promotion_image
FROM users u
LEFT JOIN posts pa ON (pa.user_id = u.id) AND pa.kind = 'article'
LEFT JOIN posts pp ON (pp.user_id = u.id) AND pp.kind = 'promotion'
LEFT JOIN posts pl ON (pp.user_id = u.id) AND pl.kind = 'link'
LEFT JOIN posts pi ON (pp.user_id = u.id) AND pi.kind = 'image'
GROUP BY u.id;