yn5hmaCi

1create table object ( id INT, name VARCHAR(50) );
2
3 insert into object (id, name) values (1, 'Thoughtstorm'); 
4 insert into object (id, name) values (2, 'Flipopia');
5 insert into object (id, name) values (3, 'Skyndu'); 
6 insert into object (id, name) values (4, 'Browsedrive');
7 insert into object (id, name) values (5, 'Realbridge');
8 
9create table object_history (
10	id INT,
11	created_at DATE,
12	active VARCHAR(50),
13	object_id INT
14);
15insert into object_history (id, created_at, active, object_id) values (1, '2018-06-08 21:36:21', false, 5);
16insert into object_history (id, created_at, active, object_id) values (2, '2018-06-10 19:01:26', false, 1);
17insert into object_history (id, created_at, active, object_id) values (3, '2018-06-03 23:02:10', true, 3);
18insert into object_history (id, created_at, active, object_id) values (4, '2018-06-04 22:20:10', false, 1);
19insert into object_history (id, created_at, active, object_id) values (5, '2018-06-08 03:40:29', false, 5);
20insert into object_history (id, created_at, active, object_id) values (6, '2018-06-01 05:18:09', true, 2);
21insert into object_history (id, created_at, active, object_id) values (7, '2018-06-08 12:45:47', false, 1);
22insert into object_history (id, created_at, active, object_id) values (8, '2018-06-06 21:13:48', false, 3);
23insert into object_history (id, created_at, active, object_id) values (9, '2018-06-07 05:04:39', false, 1);
24insert into object_history (id, created_at, active, object_id) values (10, '2018-06-09 04:06:16', false, 2);
25insert into object_history (id, created_at, active, object_id) values (11, '2018-06-01 19:36:10', false, 2);
26insert into object_history (id, created_at, active, object_id) values (12, '2018-06-05 19:33:36', false, 3);
27insert into object_history (id, created_at, active, object_id) values (13, '2018-06-06 10:36:25', false, 4);
28insert into object_history (id, created_at, active, object_id) values (14, '2018-06-06 09:04:43', true, 5);
29insert into object_history (id, created_at, active, object_id) values (15, '2018-06-10 19:57:34', true, 2);
30insert into object_history (id, created_at, active, object_id) values (16, '2018-06-09 19:46:30', false, 1);
31insert into object_history (id, created_at, active, object_id) values (17, '2018-06-06 02:26:16', true, 1);
32insert into object_history (id, created_at, active, object_id) values (18, '2018-06-01 19:26:55', false, 2);
33insert into object_history (id, created_at, active, object_id) values (19, '2018-06-07 05:52:32', false, 2);
34insert into object_history (id, created_at, active, object_id) values (20, '2018-06-02 13:22:06', false, 5);
35insert into object_history (id, created_at, active, object_id) values (21, '2018-06-04 22:00:45', false, 4);
36insert into object_history (id, created_at, active, object_id) values (22, '2018-06-02 10:35:36', true, 5);
37insert into object_history (id, created_at, active, object_id) values (23, '2018-06-10 17:13:11', false, 5);
38insert into object_history (id, created_at, active, object_id) values (24, '2018-06-10 05:49:26', true, 1);
39insert into object_history (id, created_at, active, object_id) values (25, '2018-06-01 11:22:41', true, 1);
40insert into object_history (id, created_at, active, object_id) values (26, '2018-06-08 04:34:52', false, 2);
41insert into object_history (id, created_at, active, object_id) values (27, '2018-06-10 20:49:56', true, 5);
42insert into object_history (id, created_at, active, object_id) values (28, '2018-06-06 20:10:52', true, 4);
43insert into object_history (id, created_at, active, object_id) values (29, '2018-06-04 18:13:06', false, 2);
44insert into object_history (id, created_at, active, object_id) values (30, '2018-06-03 15:25:57', false, 3);
45insert into object_history (id, created_at, active, object_id) values (31, '2018-06-09 12:37:03', true, 1);
46insert into object_history (id, created_at, active, object_id) values (32, '2018-06-10 04:10:00', false, 4);
47insert into object_history (id, created_at, active, object_id) values (33, '2018-06-04 02:51:14', true, 5);
48insert into object_history (id, created_at, active, object_id) values (34, '2018-06-01 02:36:44', true, 3);
49insert into object_history (id, created_at, active, object_id) values (35, '2018-06-07 04:32:49', true, 2);
50insert into object_history (id, created_at, active, object_id) values (36, '2018-06-07 10:44:12', false, 5);
51insert into object_history (id, created_at, active, object_id) values (37, '2018-06-01 11:34:36', true, 4);
52insert into object_history (id, created_at, active, object_id) values (38, '2018-06-10 11:25:03', false, 2);
53insert into object_history (id, created_at, active, object_id) values (39, '2018-06-02 01:52:25', true, 1);
54insert into object_history (id, created_at, active, object_id) values (40, '2018-06-06 14:59:20', false, 3);
55insert into object_history (id, created_at, active, object_id) values (41, '2018-06-05 06:36:06', false, 3);
56insert into object_history (id, created_at, active, object_id) values (42, '2018-06-08 07:06:34', false, 3);
57insert into object_history (id, created_at, active, object_id) values (43, '2018-06-02 00:44:29', true, 5);
58insert into object_history (id, created_at, active, object_id) values (44, '2018-06-02 05:50:01', false, 1);
59insert into object_history (id, created_at, active, object_id) values (45, '2018-06-04 10:11:44', false, 2);
60insert into object_history (id, created_at, active, object_id) values (46, '2018-06-09 23:50:40', false, 2);
61insert into object_history (id, created_at, active, object_id) values (47, '2018-06-06 09:11:55', false, 2);
62insert into object_history (id, created_at, active, object_id) values (48, '2018-06-08 17:24:03', false, 2);
63insert into object_history (id, created_at, active, object_id) values (49, '2018-06-04 06:25:37', true, 1);
64insert into object_history (id, created_at, active, object_id) values (50, '2018-06-06 15:40:07', true, 4);
65
66---------------
67
68Object history keeps the info of "active" column change for the object.
69
70Example:
71
72when new object is inserted with active=true,
73object history will have one record with active=true
74
75when we update object and change "active" column value, new object_history row will be added
76
77In other words - object history keeps track of when "active" column was changed and new value. For each object.
78
79The goal is to get active time per day per object. How much each object was in "active" state in average per day.
80
81Result should look like this:
82
83Object ID - Object Name - Day - Seconds Active
841 - Thoughtstorm - 2018-06-04 - 2184		
851 - Thoughtstorm - 2018-06-03 - 1184
86
87Notes:
88
891) If Object_history does not exists for particular day - do not include it into the result
90
912) If object does not have history change for the day, take its last state. E.g if last record is active=true, then object remains active for next day till today,
92or till active=false is found.
93
943) Consider following data for single object:
95
96Date - Active 
972018-06-06 15:40:00  - true  (first record for this object)
982018-06-06 15:41:00  - false
992018-06-06 16:40:00  - true
1002018-06-06 16:40:30  - false
101
102this means that total active seconds is 90
103
1044) The resulting SQL view or procedure must work in PostgreSQL