SQL Wordle Series 🔗︎
- Part One: Cheating
- Part Two: Playing
You’ve probably heard about Wordle. Even though I’m late to the party, I’d like to post my take on this extremely popular word guessing game.
While I’m not interested in playing the game myself, I’d like to find an optimal strategy to finish it in as few moves as possible. With this goal in mind, let’s get started.
Optimal strategy 🔗︎
As the title says - the first part of this would-be series is the optimal strategy - cheating. If you go to the official Wordle site and view the source code at the bottom there will be a link to a script file.
This script file contains two lists that the game uses. I’ll call the first list Solution and the second list Available. These lists don’t overlap, as we can see on this Euler diagram.
Now, the interesting thing about the Solution list is that it’s ordered chronologically. When you know the start date, you can easily add n days, find the n-th element from the list and get the solution for any given day.
Creating the database 🔗︎
I’ve extracted both lists and saved them into .txt files in this repo. Now we can simply create tables to hold our structures and import the data through the
BULK INSERT. I’ve put my lists in the
D:\Wordle\ path. Alter your path accordingly.
You can find the two lists and the up to date DB creation script in my GitHub repo. Here is just the creation script:
USE [master] ALTER DATABASE [Wordle] SET SINGLE_USER WITH ROLLBACK IMMEDIATE go DROP DATABASE [Wordle] GO CREATE DATABASE Wordle; GO USE Wordle GO DROP TABLE IF EXISTS #Stage CREATE TABLE #Stage ( Word char(5) null ) DROP TABLE IF EXISTS dbo.Solution DROP TABLE IF EXISTS dbo.Available DROP SEQUENCE IF EXISTS dbo.WordId GO CREATE SEQUENCE dbo.WordId AS smallint START WITH 0 INCREMENT BY 1 MINVALUE 0 NO CYCLE CACHE 1000; CREATE TABLE dbo.Solution ( Id smallint NOT NULL CONSTRAINT DF_Solution_Id DEFAULT NEXT VALUE FOR WordId , Word char(5) NOT NULL UNIQUE , SolveDate AS DATEADD(DAY,Id,DATEFROMPARTS(2021,06, 19)) , INDEX CX_Solution_Id CLUSTERED (Id) , CONSTRAINT PK_Solution_Word PRIMARY KEY NONCLUSTERED (Word) ) CREATE TABLE dbo.Available ( Id smallint NOT NULL CONSTRAINT DF_Available_Id DEFAULT NEXT VALUE FOR WordId , Word char(5) NOT NULL UNIQUE , INDEX CX_Available_Id CLUSTERED (Id) , CONSTRAINT PK_Available_Word PRIMARY KEY NONCLUSTERED (Word) ) BULK INSERT #Stage FROM 'D:\SQL-Wordle-Series\Lists\Solution.txt' WITH ( FIRSTROW = 1, FIELDTERMINATOR = '', --CSV field delimiter ROWTERMINATOR = '\n', --Use to shift the control to next row TABLOCK ) INSERT INTO dbo.Solution WITH (TABLOCKX) (Word) SELECT s.Word FROM #Stage AS s OPTION (MAXDOP 1) TRUNCATE TABLE #Stage BULK INSERT #Stage FROM 'D:\SQL-Wordle-Series\Lists\Available.txt' WITH ( FIRSTROW = 1, FIELDTERMINATOR = '', --CSV field delimiter ROWTERMINATOR = '\n', --Use to shift the control to next row TABLOCK ) INSERT INTO dbo.Available WITH (TABLOCKX) (Word) SELECT s.Word FROM #Stage AS s OPTION (MAXDOP 1) GO CREATE OR ALTER VIEW dbo.AllWords AS SELECT s.Id , s.Word , s.SolveDate FROM dbo.Solution AS s UNION ALL /* These two sets don't intersect */ SELECT a.Id , a.Word , NULL FROM dbo.Available AS a GO
Find the solution 🔗︎
Now we can easily query the Solution table either by the Id or by the SolveDate to find the previous and future solutions.
SELECT s.* FROM dbo.Solution AS s WHERE s.SolveDate = DATEFROMPARTS(2022,02,07) --OR s.Id = 233
If you don’t want to spoil your fair game streak, you can test it on the Wordle Archive website.
Looking forward 🔗︎
In the next post, I’ll come up with a way how to play Wordle inside the SQL Server.
Following that will be the hardest topic - finding an optimal (non-cheating) algorithm.
If I can manage that the last post should be about implementing the Hard mode.
Thank you for reading.