SQL puzzle (Find the correct answer based on values given)

Question

The table T has the following 4 columns

Column 1    Guesses (Column2)  Correct (Column3) Almost Correct (Column4)
Game1             ABDC                 2                2
Game1             ABCF                 3                0
Game1             BAFD                 1                2
Game2             EFGD                 3                0
Game2             EFHG                 2                2
Game2             EFGI                 3                0

I want to write an SQL query to get the following results (Based on the correct and almost correct column, it should find the correct answer as shown below)

Game (Column1)    Results (Column2)
Game1              ABCD
Game2              EFGH

Note: The result column needs to take the guesses(incorrect),correct column and almost correct column into consideration and predict the right results as shown in the results column.

What have you tried so far?..share some code – Ankit Das Sep 16 '20 at 12:28 — Ankit Das, Sep 16 '20 at 12:28

score 0 · Answer 1 · answered Sep 16 '20 at 12:30

0

You seem to want one row per game, along with the guesses that have the largest accumulative of correct (or maybe correct/almost correct) answers.

If so, you can use distinct on with aggregation:

select distinct on (column1) column1, guess, sum(correct + almost_correct)
from t
group by column1, guess
order by column1, sum(correct + almost_correct) desc;

answered Sep 16 '20 at 12:30

Gordon Linoff

1,242,037
58
646
786

Yes, the results should be one row per game. However, the results needs to take the guesses(incorrect) into consideration and predict the right results as shown in the results column. – Datalearner Sep 16 '20 at 12:44
@Datalearner . . . You have no incorrect guesses in your question. In any case, this should be quite close to what you want and you should be able to tweak the solution for your particular rules -- which are not explained in the question. – Gordon Linoff Sep 16 '20 at 13:10
I have editied the question to make it more clear. Yes the guesses are not correct. Based on the incorrect guesses and the correct column which shows how many letters are right and the almost correct column which shows how many letters were almost right. An Sql query needs to be written to show the correct answer as shown in results results. – Datalearner Sep 16 '20 at 13:20

seanb · Answer 2 · 2020-09-18T02:27:22.527

a) I saw this and thought it was an interesting problem. Another interesting problem is that I use SQL Server and I haven't ever used Postgres. So I tried to solve this in t-sql then convert it. The result is probably bad Postgres SQL but it does work on DB_fiddle.

b) As I knew I was going to convert it and wanted to keep it as simple as possible, I went for a 'viable' answer rather than the best possibly answer. It takes a few shortcuts (e.g., it can only do one game at a time, although will use all guesses for that game; also it only uses A through J as possible letters). Therefore your solution will need to improve upon this.

c) Logic is relatively simple - it's a brute force approach

Get all possible 4-character combinations of letters (e.g., AAAA, AAAB, AAAC etc)
Take the first guess and evaluate it against every one of those combinations, getting the number correct and number almost correct
Exclude any rows where the number correct and number close do not match your original values

d) I'm making the assumption that the number 'almost correct' are those where the letters are not in the correct spot, but are next to the correct slot (e.g., if the answer is ABCD, and you guess ABDE, then there is one almost correct - the D is one spot away). If this is ... um... not correct but almost correct, then you'll need to adjust the WHERE clause in the delete statement.

e) There are some big efficiency improvements that can be made here e.g., for first run, only insert possibilities that match the first guess's results. But this is clearer to read.

DO $$

DECLARE CurrentGuessNum integer;
DECLARE CurrentGuess varchar(4);
DECLARE CurrentNumCorrect integer;
DECLARE CurrentNumClose integer;
DECLARE Guess1 varchar(1);
DECLARE Guess2 varchar(1);
DECLARE Guess3 varchar(1);
DECLARE Guess4 varchar(1);

BEGIN

CREATE TEMPORARY TABLE PuzzleGuesses (GuessNum integer, Guess varchar(4), NumCorrect integer, NumClose integer);
INSERT INTO PuzzleGuesses (GuessNum, Guess, NumCorrect, NumClose) VALUES
(1, 'ABDC', 2, 2),
(2, 'ABCF', 3, 0),
(3, 'BAFD', 1, 2);



-- Initial setup for brute force table

CREATE TEMPORARY TABLE GuessPossibilities (GuessChar1 varchar(1), GuessChar2 varchar(1), GuessChar3 varchar(1), GuessChar4 varchar(1));

WITH PossibleChars AS
        (SELECT 'A' AS GuessChar UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D' UNION SELECT 'E' UNION SELECT 'F' UNION SELECT 'G' UNION SELECT 'H' UNION SELECT 'I' UNION SELECT 'J')
    INSERT INTO GuessPossibilities (GuessChar1, GuessChar2, GuessChar3, GuessChar4)
        SELECT  PC1.GuessChar, PC2.GuessChar, PC3.GuessChar, PC4.GuessChar
        FROM    PossibleChars AS PC1
                CROSS JOIN PossibleChars AS PC2
                CROSS JOIN PossibleChars AS PC3
                CROSS JOIN PossibleChars AS PC4;


-- Try each guess. Delete rows that don't get the same result.

CurrentGuessNum := 1;

WHILE EXISTS (SELECT * FROM PuzzleGuesses AS PG WHERE GuessNum = CurrentGuessNum)
    LOOP

    SELECT PG.Guess INTO CurrentGuess FROM PuzzleGuesses AS PG WHERE PG.GuessNum = CurrentGuessNum;
    SELECT PG.NumCorrect INTO CurrentNumCorrect FROM PuzzleGuesses AS PG WHERE PG.GuessNum = CurrentGuessNum;
    SELECT PG.NumClose INTO CurrentNumClose FROM PuzzleGuesses AS PG WHERE PG.GuessNum = CurrentGuessNum;


    -- Get each character from Guess to do single-character comparisons.
    -- Using LEFT(RIGHT()) because not sure how Postgres does SUBSTRING
    SELECT LEFT(CurrentGuess, 1)           INTO Guess1;
    SELECT LEFT(RIGHT(CurrentGuess, 3), 1) INTO Guess2;
    SELECT LEFT(RIGHT(CurrentGuess, 2), 1) INTO Guess3;
    SELECT LEFT(RIGHT(CurrentGuess, 1), 1) INTO Guess4;

    CurrentGuessNum := CurrentGuessNum + 1;


    -- Delete any rows that, when compared with current guess, don't have same result

    DELETE FROM GuessPossibilities AS A
        WHERE   CASE WHEN Guess1 = A.GuessChar1 THEN 1 ELSE 0 END 
                + CASE WHEN Guess2 = A.GuessChar2 THEN 1 ELSE 0 END
                + CASE WHEN Guess3 = A.GuessChar3 THEN 1 ELSE 0 END
                + CASE WHEN Guess4 = A.GuessChar4 THEN 1 ELSE 0 END
                <> CurrentNumCorrect
                OR
                CASE WHEN Guess1 <> A.GuessChar1 AND Guess1 = A.GuessChar2 THEN 1 ELSE 0 END
                + CASE WHEN Guess2 <> A.GuessChar2 AND (Guess2 = A.GuessChar1 OR Guess2 = A.GuessChar3) THEN 1 ELSE 0 END
                + CASE WHEN Guess3 <> A.GuessChar3 AND (Guess3 = A.GuessChar2 OR Guess3 = A.GuessChar4) THEN 1 ELSE 0 END
                + CASE WHEN Guess4 <> A.GuessChar4 AND Guess4 = A.GuessChar3 THEN 1 ELSE 0 END
                <> CurrentNumClose;
    END LOOP;

END $$;

SELECT      CONCAT(GuessChar1, GuessChar2, GuessChar3, GuessChar4) AS Possible_Correct_Answers
    FROM    GuessPossibilities;

DROP TABLE GuessPossibilities;
DROP TABLE PuzzleGuesses;

Here are DB_fiddles for Game 1 and Game 2

SQL puzzle (Find the correct answer based on values given)

2 Answers2