SQL Wordle Series - Part One: Cheating

Foreword

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. Back when Wordle lived on powerlanguage.co.uk, you could open the page source and find a link to its script file. That file baked two word lists right into the game.

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 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. Now we can create tables to hold them and import the data with BULK INSERT. I've put my lists in the D:\SQL-Wordle-Series\Lists\ 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 to play Wordle inside SQL Server.
After that, I'd hoped to tackle the hardest topic: finding an optimal (non-cheating) algorithm, and maybe even Hard mode.

Thank you for reading