SQLite schema for Wordle solver

index.js

'use strict';

const sqlite3 = require('sqlite3');
const { open } = require('sqlite');
const _ = require('lodash');

const singleCharMapping = {
    'gray': '-',
    'green': 'g',
    'yellow': 'y',
};

// Result is an encoding of the word as letter:color,letter:color,letter:color,letter:color,letter:color
// eg: T:GRAY,A:YELLOW,R:YELLOW,E:GRAY,S:GRAY
// or: T:-,A:Y,R:Y,E:-,S:-
// It will return an appropriate where clause chunk to append
const result_to_filter = (result) => {
    const colorDupes = _(result)
                        .split(/,/) // For each letter
                        .map(x => _(x).split(/:/).invokeMap(String.prototype.toLowerCase).value()) // Split into [letter, color]
                        .filter(x => _.includes(['green', 'yellow', singleCharMapping.green, singleCharMapping.yellow], x[1])) // Only take non-grays
                        .map(x => _.head(x)) // Throw out color
                        .countBy() // Count how many times this letter appeared colored
                        .value();
    // console.log({ colorDupes });
    return _(result)
    .split(/,/) // For each letter
    .map((letter_group, i) => {
        const [letter, color] = _(letter_group).split(/:/).invokeMap(String.prototype.toLowerCase).value(); // Get the letter and color, both converted to lower case
        switch(color) {
        case 'gray':
        case 'grey':
        case '-':
            if(colorDupes[letter]) {
                return `l${i + 1}<>'${letter}'`; // The letter is in the word (at another position) but it's not here
            } else {
                return `word not like '%${letter}%'`; // The letter is not in the word at all
            }
        case 'yellow':
        case 'y':
            return `word like '%${_.times(colorDupes[letter], _.constant(letter)).join('%')}%' and l${i + 1}<>'${letter}'`;
                // The letter appears in the word at least as many times as it appears in color; it is NOT at this position though
        case 'green':
        case 'g':
            return `l${i + 1} = '${letter}'`; // The letter is at this position. Wildcard 'like' here would add nothing
        }
        return undefined; // Should never reach here
    })
    .join(' and\n');
};

const view_prefix = 'create view words as select * from master where 1=1\n';

(async () => {
    const db = await open({
        filename: 'unigram5_freq.sqlite3',
        driver: sqlite3.cached.Database,
    });
    await db.exec('drop view if exists words');

    const query = [
                    view_prefix,
                    result_to_filter('t:-,a:y,r:-,e:-,s:-'),
                    result_to_filter('p:-,l:-,a:y,i:-,n:g'),
                  ].join('\n\nand\n\n');
    // console.log({query});

    await db.exec(query);

    const result = await db.all('select * from next_guesses order by count desc');
    console.log(_.map(result, 'word'));
})();

unigram_freq URL

# This word list gets filtered for just 5-letter words, then imported into the SQLite schema above

https://www.kaggle.com/datasets/rtatman/english-word-frequency

Wordle Solver.sql

CREATE TABLE IF NOT EXISTS "master"(word TEST primary key,count integer not null, l1 char(1), l2 char(1), l3 char(1), l4 char(1), l5 char(1));
CREATE INDEX master_l1_idx on master(l1);
CREATE INDEX master_l2_idx on master(l2);
CREATE INDEX master_l3_idx on master(l3);
CREATE INDEX master_l4_idx on master(l4);
CREATE INDEX master_l5_idx on master(l5);
CREATE VIEW score1 as
    select l1 letter,sum(count) count from words group by 1
/* score1(letter,count) */;
CREATE VIEW score2 as
    select l2 letter,sum(count) count from words group by 1
/* score2(letter,count) */;
CREATE VIEW score3 as
    select l3 letter,sum(count) count from words group by 1
/* score3(letter,count) */;
CREATE VIEW score4 as
    select l4 letter,sum(count) count from words group by 1
/* score4(letter,count) */;
CREATE VIEW score5 as
    select l5 letter,sum(count) count from words group by 1
/* score5(letter,count) */;
CREATE VIEW scores as
    select letter,sum(count) count from (
        select * from score1 union
        select * from score2 union
        select * from score3 union
        select * from score4 union
        select * from score5)
    group by 1
/* scores(letter,count) */;
CREATE VIEW next_guesses as select word,sum(count) count from (select w.word,s.count/2 count from scores s, words w where s.letter = w.l1 union
    select w.word,s.count/2 count from scores s, words w where s.letter = w.l2 union
    select w.word,s.count/2 count from scores s, words w where s.letter = w.l3 union
    select w.word,s.count/2 count from scores s, words w where s.letter = w.l4 union
    select w.word,s.count/2 count from scores s, words w where s.letter = w.l5 union
    select w.word,s.count count from score1 s, words w where s.letter = w.l1 union
    select w.word,s.count count from score2 s, words w where s.letter = w.l2 union
    select w.word,s.count count from score3 s, words w where s.letter = w.l3 union
    select w.word,s.count count from score4 s, words w where s.letter = w.l4 union
    select w.word,s.count count from score5 s, words w where s.letter = w.l5
) group by 1 order by 2 desc limit 20
/* next_guesses(word,count) */;