Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (
  id INTEGER,
  color VARCHAR(6),
  time VARCHAR(5),
  question INTEGER,
  suggestion BOOLEAN,
  proposal BOOLEAN
);

INSERT INTO tablename(id, color, time, question, suggestion, proposal) VALUES
  ('1', 'pink', '14:00', '0', True, False),
  ('1', 'red', '15:00', '0', False, False),
  ('1', 'blue', '13:00', '0', False, False),
  ('2', 'green', '11:00', '0', True, False),
  ('2', 'orange', '15:00', '1', False, False);

CREATE TABLE



INSERT 0 5


SELECT * FROM tablename ORDER BY id, time;


id
color
time
question
suggestion
proposal


1
blue
13:00
0
f
f

1
pink
14:00
0
t
f

1
red
15:00
0
f
f

2
green
11:00
0
t
f

2
orange
15:00
1
f
f



SELECT 5


SELECT id, color, time,
       SUM((question = 1)::int + suggestion::int + proposal::int) OVER (PARTITION BY id ORDER BY time) s
FROM tablename
ORDER BY id, time;


id
color
time
s


1
blue
13:00
0

1
pink
14:00
1

1
red
15:00
1

2
green
11:00
1

2
orange
15:00
2



SELECT 5

id	color	time	question	suggestion	proposal
1	blue	13:00	0	f	f
1	pink	14:00	0	t	f
1	red	15:00	0	f	f
2	green	11:00	0	t	f
2	orange	15:00	1	f	f