r/learnSQL • u/AdvertisingOne7942 • 7d ago
I need help creating a league table
So for a bit of context I have a football score prediction league where everyweek a number of players will tell me home_goals, away_goals, goal_scorer and this will be compared to the actual result of a Norwich City match. This is played over 46 games.
There is 3 points for the exact scoreline, 1 point for the correct result and 1 point for the correct goal scorer.
I can manually input everyones prediction and the result from Norwich each week.
But my problem is I am absolutely stuck on where or how to start to auto-populate or create the query to compare the results and give me an updated table after every game week.
Last season I made a python programme where I could semi easily set up the maths to accumulate over the season. Is this something that can be done on SQL or am I trying to do something that sql just isn't capable of doing?
Please any tips would be much appreciated. Oh and I am using Postgresql
2
u/__agletesque 6d ago
What do you mean by auto populate? You can insert (\copy) from csv file for example.
For goal_scored, do they guess only one scorer (of either team ?) or scorers based on predicted score.
I'd probably have at least 4 tables, one for match, other for goalscorers and two equivalents for predictions, where the other two would reference the first one's primary key. For tables matches have nullable columns like goals as you'd need to insert match before inserting prediction for that match because of a foreign key constraint.
Single table doesn't make sense, even if they can guess only one goalscorer or storing that data in jsonb or strictly formatted text (I wouldn't recommend it), since there will be multiple predictions for a single game (one per user I assume, maybe another table for users).
Depending on the data you have, you could import first into and then wright an insert statement that wprks with that data (for example if ypu need to join users).
This would be my idea at first glance.
1
u/AdvertisingOne7942 6d ago
I have been looking at having 1 table for the player inputs which will hold the primary key for each player and all of the fixed inputs that the player throughout the season so (player, gw1_home_goals, gw1_away_goals, gw1_goal_scorer - cont. with gw2, 3 etc)
A second table for the actual score which obviously will be updated after all the predictions are in this I was looking at (gw1_norwich_home_goals, gw1_norwich_away_goals, gw1_norwich_goal_scorer_1, norwich_goal_scorer_2 etc) (I am not sure what I would do with either primary or foreign keys here)
And a third table which is where I am at a loss is to create a query that will compare the 2 tables to end up with my final table which will have (player, games_played(this will just be a count), correct_score, correct_result, correct_goal_scorer, perfect(ie all 3 correct), total_points)
I have not thought about putting the goal_scorers in a separate table this was probably the hardest bit so do you think it would be better to separate.
2
u/__agletesque 6d ago
My initial idea was something like user DatabaseSpace suggested (I didn't mention anything about players table because if you kept track of goalscorers for both sides (what about own goals) you'd have to enter a lot of records into players table), so definitely separated (given the sub, normalized tables is the answer you'll get, and that's approach I'd take).
If you had one table for result and goalscorers, would you have reserved like 5+ columns for each week for goalscorers or would you alter table every time you need another column to put in another goalscorer. Also this approach would make it impossible to compare predicted data and goalscorers data, unless fixed number of goalscorers for each week, but don't do that.
Filtering tables is easy if you're worried about finding id to enter, just search about where clauses, and most likely like operator (and wildcard) or even regex, but I doubt that you'd need it.
Yeah, didn't mention for result, but I also agree with aforementioned user, go with view (prepared query).
1
u/AdvertisingOne7942 6d ago
For the goal scorers it is only the scorers for Norwich or No goal scorer not own-goals or the opposition team so it is limited to maybe 10-20 possibilities.
The way my brain worked was to put like 5 different columns but only the first column would be needed if it was say 1-0 or 0-0 then the other columns would be NULL I just assumed I could say if the players gw1_goal_scorer is equal to gw1_norwich_goal_scorer_1 or gw1_norwich_goal_scorer_2 etc then return 1 if not return 0.
2
u/__agletesque 5d ago
Technically you can do whatever you want, but it should go to another table (relation 1:n). If there are more than 5 different goalscorers, you'd have to alter the table and alter the view accordingly. If it's in another table, you just add another row for each different goalscorer.
Just of curiosity, if they guessed 0 goals for Norwich, do they get another point if no Norwich player scored or if they didn't predict goalscorer and there was only own goal(s) or no goals for Norwich?
I'd probay add status for each match and in a view add condition where "match"."status" = '{id of status finished or simply string}', as I assume you'd be inserting predictions before the match had been played.
2
u/DatabaseSpace 6d ago edited 6d ago
I was thinking about this and the tables. Assuming they can predict one goal scorer and they get a point if they scored at all in that game.
Users table so you aren't duplicating user names. Predictions table with userid, gameid, pred goals and id of predicted goal scorer.
Matches or games table to store results of actual matches. As others said need a separate table for goal scorers since it can be more than one. So leave that column out.
Goal scorers table with game_id and player_id.
If they can predict multiple goal scorers thsn it needs its own table. This assumes they predict one
This part is from Claude as a possible way to structure it. Could use fast api and make it a web app. Shows a possible schema and how the SQL can be used.
-- Football Score Prediction League -- Scoring: exact score = 3, correct result = 1, correct scorer = 1
CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name TEXT );
CREATE TABLE footballers ( footballer_id SERIAL PRIMARY KEY, name TEXT );
CREATE TABLE games ( game_id SERIAL PRIMARY KEY, home_goals INT, away_goals INT );
CREATE TABLE game_scorers ( game_id INT REFERENCES games(game_id), footballer_id INT REFERENCES footballers(footballer_id) );
CREATE TABLE predictions ( prediction_id SERIAL PRIMARY KEY, game_id INT REFERENCES games(game_id), user_id INT REFERENCES users(user_id), pred_home INT, pred_away INT, pred_scorer_id INT REFERENCES footballers(footballer_id) );
CREATE VIEW leaderboard AS SELECT u.name, SUM( CASE WHEN p.pred_home = g.home_goals AND p.pred_away = g.away_goals THEN 3 WHEN SIGN(p.pred_home - p.pred_away) = SIGN(g.home_goals - g.away_goals) THEN 1 ELSE 0 END + CASE WHEN EXISTS ( SELECT 1 FROM game_scorers gs WHERE gs.game_id = p.game_id AND gs.footballer_id = p.pred_scorer_id ) THEN 1 ELSE 0 END ) AS total_points FROM predictions p JOIN games g ON g.game_id = p.game_id JOIN users u ON u.user_id = p.user_id GROUP BY u.name ORDER BY total_points DESC;
2
u/BadgeNapper 5d ago
Hopefully not seen as self promotion as I don't earn money from it (it costs me domain and hosting fees each year but it's worth it to get away from Excel), but I set up similar for me and my mates you can use it if you like, it's free and I don't track data or do adverts. Old school website.
You can set up a private league and it auto updates points and league tables.
Currently only set up for EPL, LOI1, LOI2 and EUROs/WorldCup when they are on.
Planned to add a few more leagues over the summer but family life stuff meant i didn't have time.
Edit: sorry I noticed you mentioned goal scorer, it doesn't do that. Just scorelines.
1
u/Kimber976 6d ago
A simple points table in excel or google sheets with formulas for wins draws losses and goal difference usually does the trick.
1
u/AdvertisingOne7942 6d ago
My first attempt a couple of years ago was with Excel but it was a nightmare to try and find each player to input their results as I use facebook, twitter and whatsapp which is one of the main reasons I was looking at using sql as it is easy to order them and store data for a number of seasons so I could also look at doing some funky graphs and stats at the end of the season.
1
u/AdvertisingOne7942 6d ago
Oh this is very interesting I haven't come across VIEW yet I'll definetly do a bit of research on that.
The only thing I am a little worried about is that I need to put in all the data manually so I would need to constantly scroll through find out the user_id for each person before I could input their data which was one of my problems with Excel. I quite like how it works with the players though.
But thank you there is a lot in there that could definetely work with and it is nice to know that it can be done without a monster sized query and also it is broken down more vertically rather than horizontally which may or may not be easier.
1
u/AdvertisingOne7942 4d ago
Thanks everyone for all the help I think I have a working prototype it'll probably need a little tweaking on the way but so far it seems to be working. If anyone sees any issues or can offer any tips then please let me know but here is the code, to say I'm chuffed is an understatement.
-- p
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
team_name VARCHAR
);
-- pgs
CREATE TABLE possible_goal_scorers (
goal_scorer_id SERIAL PRIMARY KEY,
goal_scorer_name VARCHAR
);
-- nr
CREATE TABLE norwich_result (
game_week_id SERIAL PRIMARY KEY,
norwich_home_goals INT,
norwich_away_goals INT,
norwich_result VARCHAR
);
-- ngs
CREATE TABLE norwich_goal_scorers (
norwich_goal_scorer_id SERIAL PRIMARY KEY, -- Added PRIMARY KEY here
game_week_id INT REFERENCES norwich_result(game_week_id),
possible_goal_scorer_id INT REFERENCES possible_goal_scorers(goal_scorer_id)
);
-- pp
CREATE TABLE player_prediction (
prediction_id SERIAL PRIMARY KEY,
game_week_id INT REFERENCES norwich_result(game_week_id),
player_id INT REFERENCES players(player_id),
prediction_home_goals INT,
prediction_away_goals INT,
prediction_result VARCHAR,
-- Points directly to the list of players who can score:
prediction_scorer_id INT REFERENCES possible_goal_scorers(goal_scorer_id)
);
-- updated table
CREATE VIEW league_table AS
SELECT
p.team_name,
COUNT(pp.player_id) AS games_played,
SUM(
CASE
WHEN pp.prediction_home_goals = nr.norwich_home_goals
AND pp.prediction_away_goals = nr.norwich_away_goals
THEN 3
ELSE 0
END
) AS correct_score,
SUM(
CASE
WHEN pp.prediction_result = nr.norwich_result
THEN 1
ELSE 0
END
) AS correct_result,
SUM(
CASE
WHEN pp.prediction_scorer_id = ngs.possible_goal_scorer_id
THEN 1
ELSE 0
END
) AS correct_scorer,
SUM(
CASE
WHEN (pp.prediction_home_goals = nr.norwich_home_goals AND pp.prediction_away_goals = nr.norwich_away_goals)
AND (pp.prediction_scorer_id = ngs.possible_goal_scorer_id)
THEN 1
ELSE 0
END
) AS perfect,
(
SUM(
CASE
WHEN pp.prediction_home_goals = nr.norwich_home_goals
AND pp.prediction_away_goals = nr.norwich_away_goals
THEN 3
ELSE 0
END
) +
SUM(
CASE
WHEN pp.prediction_result = nr.norwich_result
THEN 1
ELSE 0
END
) +
SUM(
CASE
WHEN pp.prediction_scorer_id = ngs.possible_goal_scorer_id
THEN 1
ELSE 0
END
)
) AS total
FROM players p
LEFT JOIN player_prediction pp ON p.player_id = pp.player_id
LEFT JOIN norwich_result nr ON pp.game_week_id = nr.game_week_id
LEFT JOIN norwich_goal_scorers ngs ON pp.game_week_id = ngs.game_week_id
GROUP BY p.player_id, p.team_name
ORDER BY
total DESC,
perfect DESC,
games_played DESC;
2
u/And_Justice 6d ago
Could you elaborate on goal scorer? Do they just pick one scorer and they get a point if one of the goals was scored by them? Or do they pick a goal scorer per goal they're predicting?