fifa_world_cup_updating_code_snippets.sql

-- Code Snippet 3.7 - Updating World Cup Pipe Transformation to include Country Codes
SELECT
    to_date(r1."Year" || '-1-1', 'yyyy-mm-dd') as VALID_FROM,                             
    to_date(r1."Year" || '-12-31', 'yyyy-mm-dd') as VALID_TO,                               
    r1."Year" as "ID",
    r1."Country" as "Name",
    country."ID" as "Country",
    r1."Winner" as "Winner",
    r1."Runners-Up" as "Second",
    r1."Third" as "Third",
    r1."Fourth" as "Fourth",
    r1."GoalsScored" as "Goals Scored",
    r1."QualifiedTeams" as "Qualified Teams",
    r1."MatchesPlayed" as "Matches Played",
    r1."Attendance" as "Attendance"
FROM S_WORLDCUPS r1
LEFT JOIN T_COUNTRY country
ON r1."Country" = country."Name"

WHERE r1."Country" IS NOT NULL
ORDER BY r1."Year"

-- Code Snippet 3.8 - Team Pipe Transformation from Countries and Players.
SELECT
  '-infinity'::timestamptz as VALID_FROM,                             
  'infinity'::timestamptz as VALID_TO,                               
  r1."Team Initials" as "ID",                               -- text
  country."Name" as "Name"                                  -- text
FROM
  S_WORLDCUPPLAYERS r1 
INNER JOIN
  T_COUNTRY country
ON 
  r1."Team Initials" = country."ID"
WHERE 
  r1."Team Initials" is not null


-- Code Snippet 3.10 - Updating Players Positions.
SELECT
 '-infinity'::timestamptz as VALID_FROM,                             
 'infinity'::timestamptz as VALID_TO,                               
 r1."Player Name" as "Name",                                 -- text
 r1."MatchID" as "MatchID",                              -- bigint
 r1."Team Initials" as "Team",                                 -- text
 r1."Line-up" as "Line-up",                              -- text
 r1."Shirt Number" as "Shirt Number",                         -- numeric
 case when r1."Position" is NULL then 'N/A' else r1."Position" end as "Position",
 r1."Event" as "Event"                                 -- text
FROM
S_WORLDCUPPLAYERS r1

-- Code Snippet 3.11 - Updating Matches.
SELECT DISTINCT
     to_date(r1."Datetime", 'DD Mon yyyy') as EVENT_DATE,
     to_timestamp(substring(r1."Datetime",15,21),'HH24:MI:SS')::time as "Time", -- time 
     r1."MatchID" as "ID",                                                      -- bigint
     r1."RoundID" as "RoundID",                                                 -- bigint
     r1."Home Team Initials" as "HomeTeam",                                     -- bigint
     r1."Away Team Initials" as "AwayTeam",                                     -- bigint
     r1."Home Team Goals" as "Home Team Goals",                                 -- numeric
     r1."Away Team Goals" as "Away Team Goals",                                 -- numeric
     r1."Half-time Home Goals" as "Half-time Home Goals",                       -- numeric
     r1."Half-time Away Goals" as "Half-time Away Goals",                       -- numeric
     r1."Stage" as "Stage",                                                     -- text
     r1."Win conditions" as "Win Conditions",                                   -- text
     r1."Attendance" as "Attendance",                                           -- text
     NULL as "VenueID"                                                          -- numeric                    
FROM
     S_WORLDCUPMATCHES r1
WHERE r1."MatchID" is not null and r1."Datetime" is not null  
ORDER BY EVENT_DATE