-
Notifications
You must be signed in to change notification settings - Fork 1
/
best_first_word_by_word_matches.sql
179 lines (127 loc) · 2.52 KB
/
best_first_word_by_word_matches.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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
WITH oneletter as (
SELECT
CONCAT('[',word,']') as regex,
WORD as ogword
FROM public.wordle
), twoletter as (
SELECT
CONCAT('[',word,']','[',word,']') as regex,
WORD as ogword
FROM public.wordle
), threeletter as (
SELECT
CONCAT('[',word,']','[',word,']','[',word,']') as regex,
WORD as ogword
FROM public.wordle
), fourletter as (
SELECT
CONCAT('[',word,']','[',word,']','[',word,']','[',word,']') as regex,
WORD as ogword
FROM public.wordle
), fiveletter as (
SELECT
CONCAT('[',word,']','[',word,']','[',word,']','[',word,']','[',word,']') as regex,
WORD as ogword
FROM public.wordle
), oneletter_crossjoin as (
select
*
from oneletter
cross join public.wordle wordle
where wordle.word ~* oneletter.regex
), twoletter_crossjoin as (
select
*
from twoletter
cross join public.wordle wordle
where wordle.word ~* twoletter.regex
), threeletter_crossjoin as (
select
*
from threeletter
cross join public.wordle wordle
where wordle.word ~* threeletter.regex
), fourletter_crossjoin as (
select
*
from fourletter
cross join public.wordle wordle
where wordle.word ~* fourletter.regex
), fiveletter_crossjoin as (
select
*
from fiveletter
cross join public.wordle wordle
where wordle.word ~* fiveletter.regex
), oneletter_matcher as (
SELECT
'oneletter_matcher',
ogword,
COUNT(*) word_matches,
(select count(*) from public.wordle) as total_words
FROM oneletter_crossjoin
GROUP BY OGWORD
ORDER BY count(*) desc
limit 5
), twoletter_matcher as (
SELECT
'twoletter_matcher',
ogword,
COUNT(*) word_matches,
(select count(*) from public.wordle) as total_words
FROM twoletter_crossjoin
GROUP BY OGWORD
ORDER BY count(*) desc
limit 5
), threeletter_matcher as (
SELECT
'threeletter_matcher',
ogword,
COUNT(*) word_matches,
(select count(*) from public.wordle) as total_words
FROM threeletter_crossjoin
GROUP BY OGWORD
ORDER BY count(*) desc
limit 5
), fourletter_matcher as (
SELECT
'fourletter_matcher',
ogword,
COUNT(*) word_matches,
(select count(*) from public.wordle) as total_words
FROM fourletter_crossjoin
GROUP BY OGWORD
ORDER BY count(*) desc
limit 5
), fiveletter_matcher as (
SELECT
'fiveletter_matcher',
ogword,
COUNT(*) word_matches,
(select count(*) from public.wordle) as total_words
FROM fiveletter_crossjoin
GROUP BY OGWORD
ORDER BY count(*) desc
limit 5
), final as (
select
*
from oneletter_matcher
union all
select
*
from twoletter_matcher
union all
select
*
from threeletter_matcher
union all
select
*
from fourletter_matcher
union all
select
*
from fiveletter_matcher
)
select * from final