Postgres 12

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

CREATE TABLE activity (game_id INT, category TEXT, date DATE, amount INT);

INSERT INTO activity (game_id, category, date, amount) VALUES 
  (1, 'Up', '2015-12-01', 9)
, (1, 'Down', '2015-12-01', 12)
-- Left Missing for '2015-12-01
-- Right Missing for '2015-12-01
, (1, 'Up', '2016-01-01', 12)
,  (1, 'Down', '2016-01-01', 4)
,  (1, 'Left', '2016-01-01', 7)
,  (1, 'Right', '2016-01-01', 3)
,  (1, 'Up', '2016-02-01', 3)
,  (1, 'Down', '2016-02-01', 11)
,  (1, 'Left', '2016-02-01', 4)
,  (1, 'Right', '2016-02-01', 8)
,  (1, 'Up', '2016-03-01', 3)
,  (1, 'Down', '2016-03-01', 11)
-- Left Missing for '2016-03-01'
,  (1, 'Right', '2016-03-01', 8)
,  (1, 'Up', '2016-04-01', 3)
,  (1, 'Down', '2016-04-01', 11)
,  (1, 'Left', '2016-04-01', 4)
-- Right Missing for '2016-04-01'
,  (2, 'Up', '2020-12-01', 9)
, (2, 'Down', '2020-12-01', 12)
-- Left Missing for '2020-12-01'
-- Right Missing for '2020-12-01'
, (2, 'Up', '2020-01-01', 12)
,  (2, 'Down', '2020-01-01', 4)
,  (2, 'Left', '2020-01-01', 7)
-- Right Missing for '2020-01-01'
;

CREATE TABLE



INSERT 0 21


SELECT d.game_id, c.category, d.date, COALESCE(a.amount, 0) amount
FROM (SELECT DISTINCT game_id, date FROM activity) d
CROSS JOIN (SELECT DISTINCT category FROM activity) c
LEFT JOIN activity a 
ON a.game_id = d.game_id AND a.date = d.date AND a.category = c.category
ORDER BY d.game_id, d.date


game_id
category
date
amount


1
Up
2015-12-01
9

1
Down
2015-12-01
12

1
Left
2015-12-01
0

1
Right
2015-12-01
0

1
Up
2016-01-01
12

1
Down
2016-01-01
4

1
Left
2016-01-01
7

1
Right
2016-01-01
3

1
Up
2016-02-01
3

1
Down
2016-02-01
11

1
Left
2016-02-01
4

1
Right
2016-02-01
8

1
Up
2016-03-01
3

1
Down
2016-03-01
11

1
Left
2016-03-01
0

1
Right
2016-03-01
8

1
Up
2016-04-01
3

1
Down
2016-04-01
11

1
Left
2016-04-01
4

1
Right
2016-04-01
0

2
Up
2020-01-01
12

2
Down
2020-01-01
4

2
Left
2020-01-01
7

2
Right
2020-01-01
0

2
Up
2020-12-01
9

2
Down
2020-12-01
12

2
Left
2020-12-01
0

2
Right
2020-12-01
0



SELECT 28


INSERT INTO activity (game_id, category, date, amount)
SELECT d.game_id, c.category, d.date, 0
FROM (SELECT DISTINCT game_id, date FROM activity) d
CROSS JOIN (SELECT DISTINCT category FROM activity) c
LEFT JOIN activity a 
ON a.game_id = d.game_id AND a.date = d.date AND a.category = c.category
WHERE a.game_id IS NULL

INSERT 0 7


SELECT * 
FROM activity
ORDER BY game_id, date


game_id
category
date
amount


1
Up
2015-12-01
9

1
Down
2015-12-01
12

1
Right
2015-12-01
0

1
Left
2015-12-01
0

1
Left
2016-01-01
7

1
Down
2016-01-01
4

1
Up
2016-01-01
12

1
Right
2016-01-01
3

1
Left
2016-02-01
4

1
Up
2016-02-01
3

1
Down
2016-02-01
11

1
Right
2016-02-01
8

1
Right
2016-03-01
8

1
Down
2016-03-01
11

1
Up
2016-03-01
3

1
Left
2016-03-01
0

1
Down
2016-04-01
11

1
Up
2016-04-01
3

1
Left
2016-04-01
4

1
Right
2016-04-01
0

2
Left
2020-01-01
7

2
Up
2020-01-01
12

2
Right
2020-01-01
0

2
Down
2020-01-01
4

2
Right
2020-12-01
0

2
Left
2020-12-01
0

2
Down
2020-12-01
12

2
Up
2020-12-01
9



SELECT 28

game_id	category	date	amount
1	Up	2015-12-01	9
1	Down	2015-12-01	12
1	Left	2015-12-01	0
1	Right	2015-12-01	0
1	Up	2016-01-01	12
1	Down	2016-01-01	4
1	Left	2016-01-01	7
1	Right	2016-01-01	3
1	Up	2016-02-01	3
1	Down	2016-02-01	11
1	Left	2016-02-01	4
1	Right	2016-02-01	8
1	Up	2016-03-01	3
1	Down	2016-03-01	11
1	Left	2016-03-01	0
1	Right	2016-03-01	8
1	Up	2016-04-01	3
1	Down	2016-04-01	11
1	Left	2016-04-01	4
1	Right	2016-04-01	0
2	Up	2020-01-01	12
2	Down	2020-01-01	4
2	Left	2020-01-01	7
2	Right	2020-01-01	0
2	Up	2020-12-01	9
2	Down	2020-12-01	12
2	Left	2020-12-01	0
2	Right	2020-12-01	0