Number Guessing Game Failing Test 8?

[devhippo]

Hi All,

Decided to ask for help, after researching and trying to solve this for a few days.

This concerns test 8 the number guessing game in the relational database curriculum.

To the best of my knowledge, the test seems to fail, despite correct data and text displayed.

Below the .sh and .sql code.

number_guess.sh

#!/bin/bash

RANDOM_NUMBER() {
  # Check 2 arguments are being passed
  if [[ -z $1 ]] || [[ -z $2 ]]
  then
    echo "Require 2 integers as range to generate random number from.\nFirst integer is minimum and second integer is maximum.\nBoth numbers are inclusive."
  else
    shuf -i $1-$2 -n1
  fi
}


NUMBER_GUESSING_GAME() {
  # SQL query template
  PSQL="psql --username=freecodecamp --dbname=number_guess -t --no-align -c"

  # Prompt for user name
  echo Enter your username:

  read USERNAME

  USER_DATA=$($PSQL "SELECT user_id, name, COUNT(guesses) AS games_played, MIN(guesses) AS best_guess FROM users LEFT JOIN games USING(user_id) WHERE name='$USERNAME' GROUP BY user_id, name;")
  IFS='|' read USER_ID NAME GAMES_PLAYED BEST_GUESS <<< $USER_DATA

  # Check, if existing user
  if [[ -z $USER_DATA ]]
  then
    # If new user, add them to database
    ADD_NEW_USER=$($PSQL "INSERT INTO users (name) VALUES ('$USERNAME');")

    # Reload new user's data
    USER_DATA=$($PSQL "SELECT user_id, name, COUNT(guesses) AS games_played, MIN(guesses) AS best_guess FROM users LEFT JOIN games USING(user_id) WHERE name='$USERNAME' GROUP BY user_id, name;")

    # Print: Welcome, <username>! It looks like this is your first time here.
    echo Welcome, $USERNAME! It looks like this is your first time here.

  else

    set best guess to 0, if it is empty
    if [[ -z $BEST_GUESS ]]
    then
      BEST_GUESS=0
    fi
    
    # If existing user, print:
    # "Welcome back, <username>! You have played <games_played> games, and your best game took <best_game> guesses."
    echo Welcome back, $NAME! You have played $GAMES_PLAYED games, and your best game took $BEST_GUESS guesses.

  fi

  # Generate secret number between 1 and 1000
  SECRET_NUMBER=$(RANDOM_NUMBER 1 1000)

  # ================================ TEST PRINT ================================
  # echo The secret number is: $SECRET_NUMBER

  # Print Guess the secret number between 1 and 1000:
  echo Guess the secret number between 1 and 1000:

  # Declare variable with number of guesses
  # Starts at 1, as the game starts with 1 guess
  GUESSES=1

  # Prompt user to input their guess
  read USER_GUESS

  while [ $USER_GUESS != $SECRET_NUMBER ]
  do
    # Check whether input was higher or lower than secret number
    if [[ $USER_GUESS =~ [^0-9] ]] 
    then 
      # If non-integer is input as guess, print "That is not an integer, guess again:"
      echo That is not an integer, guess again:
      read USER_GUESS

    elif [[ $USER_GUESS -gt $SECRET_NUMBER ]]
    then
      # If input was higher than secret number, print "It's lower than that, guess again:" and prompt for input
      echo It\'s lower than that, guess again:
      read USER_GUESS

    elif [[ $USER_GUESS -lt $SECRET_NUMBER ]]
    then
      # If input was lower than secret number, print "It's higher than that, guess again:" and prompt for input
      echo It\'s higher than that, guess again:
      read USER_GUESS
    
    fi

    GUESSES=$((GUESSES + 1))
  done

  # Upon guessing the secret number, insert game record into database
  INSERT_GAME_RECORD=$($PSQL "INSERT INTO games (user_id, guesses) VALUES ($USER_ID, $GUESSES);")

  # When secret number is guessed, print "You guessed it in <number_of_guesses> tries. The secret number was <secret_number>. Nice job!"
  echo You guessed it in $GUESSES tries. The secret number was $SECRET_NUMBER. Nice job!

}


NUMBER_GUESSING_GAME

number_guess.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.17 (Ubuntu 12.17-1.pgdg22.04+1)
-- Dumped by pg_dump version 12.17 (Ubuntu 12.17-1.pgdg22.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE number_guess;
--
-- Name: number_guess; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE number_guess WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE number_guess OWNER TO freecodecamp;

\connect number_guess

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: games; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.games (
    game_id integer NOT NULL,
    guesses integer NOT NULL,
    user_id integer NOT NULL
);


ALTER TABLE public.games OWNER TO freecodecamp;

--
-- Name: games_game_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.games_game_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.games_game_id_seq OWNER TO freecodecamp;

--
-- Name: games_game_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.games_game_id_seq OWNED BY public.games.game_id;


--
-- Name: users; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.users (
    user_id integer NOT NULL,
    name character varying(22) NOT NULL
);


ALTER TABLE public.users OWNER TO freecodecamp;

--
-- Name: users_user_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.users_user_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.users_user_id_seq OWNER TO freecodecamp;

--
-- Name: users_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.users_user_id_seq OWNED BY public.users.user_id;


--
-- Name: games game_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games ALTER COLUMN game_id SET DEFAULT nextval('public.games_game_id_seq'::regclass);


--
-- Name: users user_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.users ALTER COLUMN user_id SET DEFAULT nextval('public.users_user_id_seq'::regclass);


--
-- Data for Name: games; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--



--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--



--
-- Name: games_game_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.games_game_id_seq', 1, false);


--
-- Name: users_user_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.users_user_id_seq', 1, false);


--
-- Name: games games_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_pkey PRIMARY KEY (game_id);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);


--
-- Name: games fk_user; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES public.users(user_id);


--
-- PostgreSQL database dump complete
--

Also, if anyone knows, where the challenge test files are located in GitHub, I’d like to have a look at it, to see what is breaking test 8.

Any help is greatly appreciated!

[Teller]

Hi @devhippo

Please let the forum know the requirements for test 8.

Happy coding

[devhippo]

The requirement for test 8 is:

“If that username has been used before, it should print Welcome back, <username>! You have played <games_played> games, and your best game took <best_game> guesses., with <username> being a users name from the database, <games_played> being the total number of games that user has played, and <best_game> being the fewest number of guesses it took that user to win the game”

[Teller]

If the player played a game previously, why won’t a best game number exist?

@devhippo

    set best guess to 0, if it is empty
    if [[ -z $BEST_GUESS ]]
    then
      BEST_GUESS=0
    fi

[devhippo]

The test seems to fail with or without this section.

That statement is there purely for cosmetic reasons. It prints “… your best game took 0 guesses.”, instead of “… your best game took guesses.”. This is because the data is pulled by joining 2 tables (users & games), using SQL COUNT and -MIN. MIN returns an empty value, if a returning player hasn’t played a game yet.

[Teller]

If the player has not played a game before, then they should receive the new player welcome.

[devhippo]

We’re veering off track from addressing the original question of passing test 8.

As already mentioned, this code addresses some sort of edge case. If a player exits the game right after entering their name and returns to it, they will receive the existing player message with 0 games played.

As far as I’m aware it has no effect on any of the tests.

[Teller]

I think that the tests check if the player exists in the database.
If they exist in the database, then:

1. the player entered their details
2. they played a game

Therefore the saved player contains a best game score.
It is impossible to have a best score of 0 for the number of guesses.

[ILM]

@devhippo

    set best guess to 0, if it is empty
    if [[ -z $BEST_GUESS ]]

this line should be a comment I guess, is it maybe causing some issues the fact that it is not a comment? or was it a copy-paste error?

[devhippo]

Hi,

Yes, I noticed this was uncommented, when the snippet was mentioned.
The entire snippet was commented out since, to narrow things down.

Apologies, I mentioned that the test results remained the same with or without the snippet, however, didn’t mention that it was commented out since.

[ILM]

I mean specificically the line with “set best guess to 0, if it is empty”, not the if statement

can you show the output of your program if user “Pierino” makes a game for the first time, and then later does a game for the second time?

[devhippo]

The output showed an error when inserting a new game record.
The user ID was missing, as per screenshot below:

The issue seems to originate from querying the new user’s data and not storing the user ID in a variable for further use:

  # Check, if existing user
  if [[ -z $USER_DATA ]]
  then
    # If new user, add them to database
    ADD_NEW_USER=$($PSQL "INSERT INTO users (name) VALUES ('$USERNAME');")

    # Reload new user's data
    USER_DATA=$($PSQL "SELECT user_id, name, COUNT(guesses) AS games_played, MIN(guesses) AS best_guess FROM users LEFT JOIN games USING(user_id) WHERE name='$USERNAME' GROUP BY user_id, name;")

Fixed it with this:

  # Check, if existing user
  if [[ -z $USER_DATA ]]
  then
    # If new user, add them to database
    ADD_NEW_USER=$($PSQL "INSERT INTO users (name) VALUES ('$USERNAME');")

    # Pull new user's id for game record insertion later
    USER_ID=$($PSQL "SELECT user_id FROM users WHERE name='$USERNAME';")

The tests passed and the challenge is complete.

Really appreciate the support!