fBrnm4wF

1CREATE TABLE IF NOT EXISTS `post` (
2`id` int(11) NOT NULL,
3`titulo` varchar(200) NOT NULL,
4`user_id` int(11) NOT NULL,
5`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE         CURRENT_TIMESTAMP
6 );
7
8INSERT INTO `post` (`id`, `titulo`, `user_id`, `created_at`) VALUES
9(1, 'group by mysql', 19, '2018-02-18 19:10:30');
10
11CREATE TABLE IF NOT EXISTS `comentarios` (
12`id` int(11) NOT NULL,
13`content` varchar(200) NOT NULL,
14`post_id` int(11) NOT NULL,
15`user_id` int(11) NOT NULL,
16`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
17 );
18
19INSERT INTO `comentarios` (`id`, `content`, `post_id`, `user_id`,    `created_at`) VALUES
20(1, 'comentario 1', 1, 19, '2018-02-19 13:12:09'),
21(2, 'comentario 2', 1, 20, '2018-02-20 23:42:09'),
22(3, 'comentario 3', 1, 19, '2018-02-21 19:12:30'),
23(4, 'comentario 4', 1, 21, '2018-02-26 11:38:34'),
24(5, 'comentario 5', 1, 22, '2018-02-28 19:13:15');
25
26CREATE TABLE IF NOT EXISTS `likes` (
27`id` int(11) NOT NULL,
28`post_id` int(11) NOT NULL,
29`user_id` int(11) NOT NULL,
30`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
31 );
32
33INSERT INTO `likes` (`id`, `post_id`, `user_id`, `created_at`) VALUES
34(1, 1, 19, '2018-02-28 19:16:12'),
35(2, 1, 21, '2018-02-28 19:16:12'),
36(3, 1, 22, '2018-02-22 19:16:21');
37	
38SELECT
39 p.id as post_id,
40 titulo,
41 MAX(DATE(p.created_at)) as fecha_post,
42 MAX(DATE(c.created_at)) as fecha_comentario,
43 COUNT(c.user_id) as count_personas,
44 MAX(DATE(l.created_at)) as fecha_likes,
45COUNT(l.user_id) as count_likes
46  FROM post as p
47  LEFT OUTER JOIN comentarios as c 
48   ON p.id=c.post_id 
49  LEFT OUTER JOIN likes as l
50    ON p.id=l.post_id and date(c.created_at)=date(l.created_at)
51  GROUP BY p.id, DATE(p.created_at), DATE(c.created_at), DATE(l.created_at);