FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

create temp table GameParti
	(GameParti_Lnk_ID int, CreationDate text, LeagueGames_ID int, CharacterID int, SupportTeam text, CreatedByAccountID int, CreatedByCharacterID int);
insert into GameParti values
	(19, '24/03/2025 9:54', 9, 20, 3, 11, 20),
	(12, '17/03/2025 14:39', 9, 61, 3, 11, 61),
	(13, '18/03/2025 21:42', 9, 56, 3, 11, 56),
	(14, '19/03/2025 12:15', 9, 62, 3, 11, 62),
	(15, '19/03/2025 12:40', 9, 64, 3, 23, 64),
	(16, '19/03/2025 12:42', 9, 63, 3, 11, 63),
	(17, '21/03/2025 12:56', 9, 59, 3, 11, 59),
	(11, '17/03/2025 14:36', 9, 20, 3, 11, 20),
	(20, '24/03/2025 16:36', 9, 25, 3, 23, 25),
	(21, '24/03/2025 22:12', 9, 31, 3, 11, 31),
	(22, '25/03/2025 9:54', 9, 12, 3, 11, 12),
	(23, '26/03/2025 17:49', 8, 12, 3, 11, 12);
create temp table LeagueGames
	(LeagueGames_ID int, CreationDate text, Score_HomeTeam text, Home_ClubID int, Home_ClubName text, Home_TeamID int, Home_TeamName text, Score_AwayTeam text, Away_ClubID int, Away_ClubName text, Away_TeamID int, Away_TeamName text, LeagueGame_Status text);
insert into LeagueGames values
	(8, '31/01/2025 16:51', 2, 2, 'Club Talo', 3, 'Team Gholo', 1, 14, 'Club Mipu', 38, 'Team Vruup', 2),
	(9, '13/03/2025 14:14', 0, 3, 'Club Froly', 11, 'Team Hiruo', 2, 2, 'Club Talo', 3, 'Team Gholo', 4);
create temp table Teams
	(TeamID int, Club_ID int, Club_Name text, Team_Name text);
insert into Teams values
	(3, 2, 'Club Talo', 'Team Gholo'),
	(11, 3, 'Club Froly', 'Team Hiruo'),
	(38, 14, 'Club Mipu', 'Team Vruup');
create temp table Clubs
	(ClubID int, Clubname text);
insert into Clubs values
	(2, 'Club Talo'),
	(3, 'Club Froly'),
	(14, 'Club Mipu');
create temp table TeamPlayerLnk
	(TeamPlayers_Lnk_ID int, CharacterID int, Teamplayer_ClubID int, Teamplayer_TeamID int, ActLinked text);
insert into TeamPlayerLnk values
	(2, 59, 2, 3, 1),
	(3, 60, 2, 3, 1),
	(4, 61, 2, 3, 1),
	(5, 63, 2, 3, 1),
	(6, 64, 3, 11, 1),
	(7, 65, 3, 11, 1),
	(8, 66, 2, 3, 1),
	(9, 67, 2, 3, 1),
	(10, 68, 2, 3, 1);
create temp table Characters
	(AccountID int, ID int, Firstname text, LastName text);
insert into Characters values
	(6, 11, 'FirstPlayer 1', 'LastPlayer1'),
	(11, 12, 'FirstPlayer 2', 'LastPlayer2'),
	(5, 13, 'FirstPlayer 3', 'LastPlayer3'),
	(11, 20, 'FirstPlayer 4', 'LastPlayer4'),
	(11, 62, 'FirstPlayer 5', 'LastPlayer5'),
	(0, 53, 'FirstPlayer 6', 'LastPlayer6'),
	(23, 25, 'FirstPlayer 7', 'LastPlayer7'),
	(22, 30, 'FirstPlayer 8', 'LastPlayer8'),
	(11, 31, 'FirstPlayer 9', 'LastPlayer9'),
	(0, 60, 'FirstPlayer 10', 'LastPlayer10'),
	(11, 61, 'FirstPlayer 11', 'LastPlayer11'),
	(0, 54, 'FirstPlayer 12', 'LastPlayer12'),
	(0, 52, 'FirstPlayer 13', 'LastPlayer13'),
	(0, 51, 'FirstPlayer 14', 'LastPlayer14'),
	(0, 55, 'FirstPlayer 15', 'LastPlayer15'),
	(11, 56, 'FirstPlayer 16', 'LastPlayer16'),
	(11, 59, 'FirstPlayer 17', 'LastPlayer17'),
	(11, 63, 'FirstPlayer 18', 'LastPlayer18'),
	(23, 64, 'FirstPlayer 19', 'LastPlayer19'),
	(0, 65, 'FirstPlayer 20', 'LastPlayer20'),
	(0, 66, 'FirstPlayer 21', 'LastPlayer21'),
	(0, 67, 'FirstPlayer 22', 'LastPlayer22'),
	(0, 69, 'FirstPlayer 23', 'LastPlayer23');
create temp table PlayerReady
	(PlayerReady_ID int, CreatedByCharacterID int);
insert into PlayerReady values
	(61, 20),
	(60, 20),
	(59, 20),
	(59, 31),
	(61, 25),
	(65, 20),
	(64, 25),
	(65, 25),
	(60, 25),
	(64, 20),
	(67, 25),
	(59, 12);
alter table PlayerReady add ActLinked int default 1;
create temp table CollectedPoints (LeagueGames_ID int, TeamID int, EarnedPoints int);
insert into CollectedPoints values (9, 3, 14);

WITH
-- Protect against duplicates (20 that participated twice to the same game under IDs 11 and 19);
-- if we do not, our results will be multiplied by 2.
parti AS (SELECT DISTINCT LeagueGames_ID, CharacterID FROM GameParti),
points AS
(
	SELECT
	c.Clubname,
	t.TeamID,
	t.Team_Name,
	e.LeagueGames_ID,
	e.CharacterID CreatedByCharacterID,
	sum(CASE    WHEN z.LeagueGames_ID IS NULL AND p.PlayerReady_ID IS NULL THEN '1'
				WHEN z.LeagueGames_ID IS NOT NULL AND p.PlayerReady_ID IS NULL THEN '5'
				WHEN z.LeagueGames_ID IS NULL AND p.PlayerReady_ID IS NOT NULL THEN '10'
				WHEN z.LeagueGames_ID IS NOT NULL AND p.PlayerReady_ID IS NOT NULL THEN '15' ELSE 0 END) AS TotalPts
	FROM        parti e
	JOIN        LeagueGames s    ON s.LeagueGames_ID = e.LeagueGames_ID
	JOIN        Teams t          ON t.TeamID IN (s.Home_TeamID, s.Away_TeamID)
	JOIN        Clubs c          ON c.ClubID = t.Club_ID
	JOIN        TeamPlayerLnk g  ON g.Teamplayer_TeamID = t.TeamID AND g.ActLinked = '1'
	JOIN        Characters ch    ON ch.ID = g.CharacterID
	LEFT JOIN   GameParti z      ON z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID
	LEFT JOIN   PlayerReady p    ON p.PlayerReady_ID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = e.CharacterID
	GROUP BY 1, 2, 3, 4, 5
),
points2 AS
(
	SELECT p.*, COALESCE(EarnedPoints, 0) AlreadyCollectedPts
  	FROM points p LEFT JOIN CollectedPoints cp ON cp.LeagueGames_ID = p.LeagueGames_ID AND cp.TeamID = p.TeamID
)
SELECT
e.CharacterID,
	s.CreationDate          AS sCreationDate,
	s.Score_HomeTeam        AS hScore,
	ht.Clubname             AS hClubName,
	ht.Team_Name            AS hTeamName,
	s.Score_AwayTeam        AS aScore,
	at.Clubname             AS aClubName,
	at.Team_Name            AS aTeamName,
	s.LeagueGame_Status     AS sGameStatus,
	ht.TotalPts             AS HomeTotalPts,
	at.TotalPts             AS AwayTotalPts,
	ht.TotalPts - ht.AlreadyCollectedPts AS HomePtsToCollect,
	at.TotalPts - at.AlreadyCollectedPts AS AwayPtsToCollect
FROM        parti e
LEFT JOIN   LeagueGames s           ON s.LeagueGames_ID = e.LeagueGames_ID
LEFT JOIN   points2 ht     ON ht.TeamID = s.Home_TeamID AND ht.LeagueGames_ID = e.LeagueGames_ID AND ht.CreatedByCharacterID = e.CharacterID
LEFT JOIN   points2 at     ON at.TeamID = s.Away_TeamID AND at.LeagueGames_ID = e.LeagueGames_ID AND at.CreatedByCharacterID = e.CharacterID
WHERE e.CharacterID in (12, 20) -- For our test.
ORDER BY e.CharacterID, s.CreationDate;

characterid	screationdate	hscore	hclubname	hteamname	ascore	aclubname	ateamname	sgamestatus	hometotalpts	awaytotalpts	homeptstocollect	awayptstocollect
12	13/03/2025 14:14	0	Club Froly	Team Hiruo	2	Club Talo	Team Gholo	4	6	28	6	14
12	31/01/2025 16:51	2	Club Talo	Team Gholo	1			2	15		15
20	13/03/2025 14:14	0	Club Froly	Team Hiruo	2	Club Talo	Team Gholo	4	25	47	25	33

CREATE TABLE
INSERT 0 12
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 9
CREATE TABLE
INSERT 0 23
CREATE TABLE
INSERT 0 12
ALTER TABLE
CREATE TABLE
INSERT 0 1