SQL Wordle Series - Part One: Cheating

Tom

2022-02-07

3 minutes

Page content

SQL Wordle Series 🔗︎

Part One: Cheating
Part Two: Playing

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. 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.

Euler diagram of word sets and their element count

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.

TSQL
Wordle