-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path2-1.sql
161 lines (160 loc) · 11.4 KB
/
2-1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
with
input as (
select 'Game 1: 7 red, 8 blue; 6 blue, 6 red, 2 green; 2 red, 6 green, 8 blue; 9 green, 2 red, 4 blue; 6 blue, 4 green
Game 2: 2 green, 8 blue, 9 red; 5 blue, 11 red; 5 blue, 3 green, 4 red; 7 blue, 8 red; 5 red, 3 green, 3 blue; 1 red, 5 blue, 2 green
Game 3: 4 red, 8 green, 2 blue; 6 green, 2 red, 2 blue; 12 red, 12 green, 2 blue; 10 green, 6 red
Game 4: 12 red, 3 green; 1 green, 2 blue, 2 red; 16 red, 4 green; 14 red, 3 green; 2 blue, 5 red; 11 red, 4 green
Game 5: 19 red, 1 green; 7 red, 1 green, 1 blue; 7 red; 13 red, 2 green
Game 6: 8 green, 2 blue, 2 red; 1 red, 7 green; 6 green, 8 blue, 3 red; 14 green, 2 red, 9 blue; 13 green; 10 blue, 2 red, 4 green
Game 7: 2 red, 7 green; 9 red, 5 blue, 18 green; 16 blue, 10 green, 6 red; 13 blue, 12 green, 6 red; 19 blue, 12 red, 11 green
Game 8: 5 blue, 3 red, 1 green; 8 red, 1 blue, 3 green; 8 green, 9 blue, 8 red; 5 blue, 11 green, 3 red; 2 red, 3 blue, 1 green; 3 green, 7 red
Game 9: 3 red, 3 blue; 7 green, 9 blue, 14 red; 12 blue, 9 green, 3 red
Game 10: 6 blue, 1 green, 1 red; 17 red, 8 blue, 1 green; 4 blue, 10 red
Game 11: 2 green, 1 blue, 8 red; 14 red, 5 blue, 10 green; 14 blue, 6 red, 9 green; 7 red, 1 blue, 1 green
Game 12: 16 green, 1 blue; 1 green, 4 blue; 1 red, 2 blue, 16 green; 17 green, 3 blue; 14 green, 4 blue, 3 red; 7 green, 8 blue
Game 13: 13 red, 10 blue, 19 green; 2 blue, 12 red, 8 green; 8 blue, 11 red, 13 green
Game 14: 16 red, 7 blue, 7 green; 8 green, 10 red, 6 blue; 6 blue, 11 red, 4 green
Game 15: 6 red, 10 blue, 2 green; 10 red, 1 blue, 1 green; 5 blue, 3 red, 2 green; 16 blue, 10 red, 2 green; 13 blue
Game 16: 8 green, 10 red, 1 blue; 6 red, 9 green, 12 blue; 7 blue, 3 green, 1 red; 5 red, 6 blue, 3 green; 1 blue, 6 green, 8 red; 6 green, 8 red
Game 17: 4 green, 4 blue; 2 green, 2 red; 1 red, 8 green, 7 blue
Game 18: 6 blue, 8 green, 9 red; 4 green, 3 blue, 8 red; 8 blue, 12 red, 2 green; 4 blue, 8 red, 2 green
Game 19: 12 blue, 3 green; 2 blue, 2 green, 4 red; 3 green, 6 red, 14 blue; 3 green, 1 red, 17 blue; 10 green, 10 red, 1 blue; 8 green, 8 blue, 4 red
Game 20: 4 green, 3 blue, 1 red; 9 red, 14 blue, 9 green; 1 blue, 17 red, 2 green; 8 red, 13 blue, 8 green; 7 red, 2 green, 20 blue; 6 green, 13 red, 5 blue
Game 21: 7 red, 1 blue; 1 blue, 5 red, 4 green; 5 green, 5 red; 7 red, 2 green; 4 green, 2 red, 1 blue
Game 22: 6 red, 8 green, 18 blue; 2 green, 7 blue, 2 red; 18 blue, 8 green, 1 red; 10 red, 7 green, 20 blue; 5 blue, 10 green, 4 red
Game 23: 2 green, 2 red, 15 blue; 2 red, 6 green, 4 blue; 8 red, 5 green
Game 24: 9 blue, 7 green; 3 green, 9 blue, 1 red; 2 red, 6 blue; 3 green, 11 blue, 4 red
Game 25: 2 green, 3 red, 5 blue; 2 red, 5 green; 3 red, 2 blue, 5 green; 1 blue, 4 green, 3 red
Game 26: 1 green, 7 red; 6 green, 2 blue, 7 red; 11 green, 3 blue, 1 red; 6 red, 5 blue; 1 blue, 5 green; 8 green
Game 27: 2 green, 10 red, 3 blue; 5 red, 5 green; 2 green, 4 blue, 10 red; 4 red, 1 blue; 3 green, 3 blue, 2 red; 8 blue, 2 green
Game 28: 9 red, 7 blue; 6 blue, 11 red; 10 red, 10 blue, 3 green
Game 29: 15 blue, 10 green, 1 red; 1 blue, 10 green; 2 red, 19 green, 14 blue; 4 blue, 18 green, 7 red; 8 blue, 10 green, 5 red
Game 30: 7 blue, 3 green, 2 red; 12 blue, 5 green; 1 red, 5 green, 5 blue
Game 31: 6 green, 5 red; 7 red, 7 green, 5 blue; 2 red, 7 blue, 7 green; 2 red, 8 blue, 7 green
Game 32: 9 red, 2 green, 2 blue; 3 blue, 7 green, 3 red; 2 blue, 1 red
Game 33: 13 blue, 7 red, 1 green; 5 red, 17 blue, 1 green; 2 green, 14 red, 19 blue; 2 blue, 1 red; 13 blue, 8 red; 3 blue, 15 red
Game 34: 3 blue, 4 red, 1 green; 7 green, 2 blue; 17 green, 1 red; 7 green, 3 blue; 2 red, 1 green; 2 blue, 1 green, 2 red
Game 35: 5 red, 2 green, 4 blue; 5 blue; 2 blue, 4 green, 4 red; 8 green, 1 blue, 5 red; 3 green, 3 blue, 5 red
Game 36: 4 blue, 4 red, 4 green; 8 red, 8 blue, 4 green; 3 green, 9 red, 5 blue; 1 red, 4 green, 13 blue; 7 blue, 9 green, 9 red; 13 blue, 8 red
Game 37: 6 green, 10 red, 5 blue; 5 red, 9 green, 2 blue; 4 red, 4 blue, 1 green; 12 green, 3 red, 2 blue; 4 red, 11 green, 1 blue
Game 38: 3 red; 1 blue, 3 red; 2 red, 2 green; 2 blue, 3 green; 4 green, 2 blue, 2 red; 3 red, 2 blue
Game 39: 6 red, 4 blue, 9 green; 17 blue, 2 green, 3 red; 5 red, 15 green, 12 blue; 3 red, 5 green, 8 blue
Game 40: 20 red, 10 blue, 8 green; 7 red, 3 green, 10 blue; 14 green, 8 blue, 14 red; 4 red, 9 blue, 8 green; 11 red, 4 blue, 8 green
Game 41: 2 blue; 4 red, 2 blue, 2 green; 5 green, 9 red, 3 blue; 8 red, 4 green
Game 42: 3 red, 13 blue; 2 blue, 1 green; 6 green, 16 blue, 10 red
Game 43: 9 red, 1 green; 1 green, 7 blue, 8 red; 8 blue, 2 green, 5 red
Game 44: 15 blue, 5 green; 5 green, 1 red, 6 blue; 1 red, 4 blue
Game 45: 3 green; 2 blue, 1 green; 4 green, 4 blue; 1 red, 5 blue, 11 green
Game 46: 20 green, 10 blue, 10 red; 4 red, 13 green, 1 blue; 2 red, 2 green, 10 blue
Game 47: 5 blue, 2 red, 3 green; 5 blue, 2 red, 1 green; 2 green, 3 red, 5 blue; 1 red, 4 green, 4 blue; 2 blue
Game 48: 2 red, 10 blue, 1 green; 6 blue, 6 green, 6 red; 11 blue, 7 red, 5 green; 1 red, 6 green, 14 blue; 1 green, 9 blue, 3 red
Game 49: 3 blue, 16 green, 6 red; 14 red, 3 green, 5 blue; 12 green, 6 red, 4 blue; 4 red, 6 blue, 15 green; 6 green, 11 blue, 9 red
Game 50: 1 green, 7 red, 2 blue; 3 green, 2 blue, 5 red; 4 red, 2 blue, 4 green
Game 51: 3 green, 7 red; 2 red, 4 green, 2 blue; 10 green, 3 blue, 6 red; 1 blue, 11 green; 1 green, 3 blue; 2 blue, 7 green
Game 52: 2 green, 1 blue, 2 red; 7 red, 3 green; 2 blue, 8 green; 1 blue, 13 green, 6 red
Game 53: 7 red, 2 green; 1 blue, 16 red, 7 green; 12 red, 7 green; 1 blue, 11 red, 5 green
Game 54: 1 green, 1 blue, 16 red; 4 blue, 1 green, 6 red; 4 red, 1 blue, 3 green; 1 green, 4 red, 4 blue; 1 red, 2 green
Game 55: 2 blue, 2 green, 5 red; 1 blue, 4 red, 5 green; 2 blue, 2 green, 6 red; 2 blue, 5 red, 6 green; 6 green, 1 red
Game 56: 3 red, 14 green, 14 blue; 7 green, 14 red, 13 blue; 12 red, 13 blue, 6 green
Game 57: 6 red, 11 green, 5 blue; 6 blue, 2 green, 4 red; 4 red, 6 green; 4 green, 1 red, 2 blue; 1 green, 5 blue
Game 58: 8 red, 6 green, 10 blue; 8 blue, 9 red; 1 red, 12 blue, 1 green; 5 green, 3 blue, 8 red; 9 blue, 2 green, 4 red
Game 59: 11 green, 1 red; 5 red, 10 blue, 12 green; 6 red, 6 blue, 2 green; 1 blue, 10 green, 4 red; 8 green, 11 blue; 6 blue, 1 red, 11 green
Game 60: 16 green, 14 red, 1 blue; 2 blue, 8 green; 14 green; 1 green, 11 red, 2 blue; 1 blue, 11 green
Game 61: 11 blue, 3 green, 1 red; 9 red, 18 green; 7 red, 15 green, 5 blue; 19 blue, 2 green
Game 62: 2 green, 2 blue; 1 green, 3 red; 1 blue, 4 red, 8 green; 2 blue, 11 green; 5 red, 9 green; 7 green, 1 blue, 8 red
Game 63: 14 blue, 6 red, 5 green; 3 blue, 10 red; 8 blue, 1 green, 8 red; 9 blue, 14 red, 4 green
Game 64: 8 blue, 3 green, 1 red; 1 green, 13 blue; 4 green, 15 blue, 1 red; 9 blue, 3 green
Game 65: 5 red, 5 green, 3 blue; 7 red, 2 blue, 1 green; 2 blue, 7 red, 2 green; 6 green, 12 red, 2 blue
Game 66: 9 blue, 1 green, 1 red; 8 blue, 2 red, 2 green; 1 red, 1 green; 2 green, 1 red, 5 blue; 14 blue, 2 red; 2 red, 13 blue, 2 green
Game 67: 7 green, 2 blue, 3 red; 16 red, 15 blue, 4 green; 6 green, 3 blue, 1 red; 1 red, 5 green, 9 blue
Game 68: 11 blue, 4 red; 6 red, 1 blue; 1 green, 8 red, 11 blue; 10 blue, 7 red; 7 blue, 2 red; 9 blue, 1 green, 11 red
Game 69: 9 red, 8 green, 6 blue; 2 blue, 3 red; 4 green, 9 blue, 9 red; 3 blue, 5 green, 5 red; 1 green, 7 red, 5 blue; 1 blue, 3 green, 9 red
Game 70: 5 blue, 1 green, 3 red; 1 red, 5 green, 3 blue; 4 green, 3 blue; 2 green, 8 blue; 10 blue, 12 green
Game 71: 1 green, 5 blue, 14 red; 6 red, 2 green, 5 blue; 7 blue, 5 green, 2 red; 2 green, 2 red, 1 blue; 6 red, 5 green; 6 red
Game 72: 12 red, 6 green; 11 red, 2 green; 3 green, 1 red; 14 red, 5 green, 1 blue
Game 73: 3 green, 3 red; 4 green, 4 red, 5 blue; 2 red, 5 blue, 2 green; 4 green, 8 red, 6 blue
Game 74: 12 red, 1 blue; 3 blue, 17 red, 1 green; 5 red, 1 blue
Game 75: 14 green, 1 red; 3 green, 5 red, 16 blue; 10 blue, 20 green, 5 red
Game 76: 4 green, 9 blue, 12 red; 4 blue, 6 red, 3 green; 10 red, 8 green, 1 blue; 9 red, 6 green, 1 blue; 8 blue, 1 green, 6 red; 8 blue, 3 red
Game 77: 7 red, 12 blue, 10 green; 12 green; 8 red, 8 blue, 4 green; 11 green, 9 red, 9 blue
Game 78: 9 green, 14 blue, 16 red; 7 blue, 8 green, 1 red; 5 green, 13 red, 7 blue; 9 green, 6 red
Game 79: 2 red, 3 blue, 8 green; 1 green; 1 blue, 2 green, 1 red; 1 green; 4 blue, 2 green, 1 red
Game 80: 8 red, 3 blue, 10 green; 12 red; 13 red, 4 green, 6 blue; 2 red, 14 blue, 9 green; 13 blue, 1 red, 5 green; 13 blue, 9 red, 2 green
Game 81: 2 green, 4 blue, 9 red; 1 red, 6 blue, 14 green; 4 green, 1 blue, 5 red; 3 blue, 8 red, 9 green; 6 blue, 5 green, 11 red
Game 82: 6 blue, 6 red, 13 green; 5 blue, 4 green, 7 red; 1 green, 1 blue, 2 red
Game 83: 2 red, 8 green; 7 red, 7 green; 11 red, 6 green; 14 red, 2 green, 2 blue; 2 green, 1 blue; 8 green, 11 red, 2 blue
Game 84: 3 blue, 4 green, 9 red; 7 blue, 13 green, 17 red; 14 blue, 16 green, 14 red; 7 blue, 16 green, 3 red; 9 red, 2 blue, 14 green; 13 blue, 3 green, 7 red
Game 85: 1 green, 6 red, 2 blue; 1 blue, 8 green, 5 red; 11 green, 1 red
Game 86: 2 green, 6 blue, 2 red; 4 green, 3 blue, 3 red; 3 blue, 5 green, 8 red; 1 red; 9 red, 4 blue, 2 green; 4 green, 8 blue, 9 red
Game 87: 1 red, 3 blue, 2 green; 14 red, 8 blue, 2 green; 9 blue, 13 red, 5 green; 5 green, 2 blue; 1 green, 10 blue, 14 red; 6 blue, 12 red, 15 green
Game 88: 3 red, 7 green, 1 blue; 2 blue, 4 red, 4 green; 13 green, 3 red, 4 blue
Game 89: 2 red, 14 green, 1 blue; 6 green, 2 red; 8 red, 8 green; 1 blue; 4 red, 1 blue, 15 green; 1 blue, 1 green, 8 red
Game 90: 4 red, 4 blue; 9 red, 7 blue, 2 green; 9 blue, 8 red; 3 red, 1 blue; 15 blue, 9 red; 4 red, 4 blue, 2 green
Game 91: 14 green, 4 red; 10 red, 13 green, 5 blue; 1 green, 7 red, 20 blue
Game 92: 10 green, 7 blue; 12 green, 4 blue; 12 green, 4 red
Game 93: 1 blue, 6 red; 2 blue, 10 red, 2 green; 2 green, 2 blue; 4 red, 2 blue; 4 red, 3 green
Game 94: 2 red, 1 blue; 7 red, 1 green, 1 blue; 1 blue, 5 red; 1 green, 10 red
Game 95: 1 red, 10 blue; 6 red, 4 blue; 2 blue, 4 red; 1 red, 2 green, 7 blue; 1 red, 6 blue
Game 96: 1 green, 16 blue, 1 red; 7 red, 10 blue, 1 green; 18 blue, 12 red
Game 97: 5 blue, 3 red, 8 green; 7 red, 14 green, 1 blue; 11 green
Game 98: 1 green, 12 blue, 7 red; 6 red, 7 blue, 1 green; 5 red, 11 blue, 1 green; 2 green, 8 red, 8 blue
Game 99: 11 red, 7 blue, 15 green; 3 red, 11 green, 5 blue; 19 green, 5 red, 1 blue; 7 green, 4 red
Game 100: 10 red; 11 blue, 12 red; 1 green, 7 blue, 6 red' as input
),
lines as (
select
regexp_split_to_table(input, '\n') as line
from
input
),
games as (
select
regexp_match(line, 'Game (\d+): (.*)') as match
from
lines
),
draws as (
select
match[1]::int as game_id,
row_number() over (partition by match[1]) as draw_id,
regexp_split_to_table(match[2], '; ') as draw
from
games
),
color_strs as (
select
game_id,
draw_id,
regexp_split_to_table(draw, ', ') as color
from
draws
),
colors as (
select
game_id,
draw_id,
sum(regexp_match(color, '(\d+) red')[1]::int) as red,
sum(regexp_match(color, '(\d+) green')[1]::int) as green,
sum(regexp_match(color, '(\d+) blue')[1]::int) as blue
from
color_strs
group by game_id, draw_id
),
max_colors as (
select
game_id,
max(red) as red,
max(green) as green,
max(blue) as blue
from
colors
group by game_id
)
select
sum(game_id) as answer
from
max_colors
where
red <= 12 and
green <= 13 and
blue <= 14