fifa_world_cup_code_snippets.sql

-- Code Snippet 1.1 Extracting time from Datetime column
to_timestamp(substring("Datetime", 15, 21), 'HH24:MI:SS')::time

             
-- Code Snippet 3.1  -  Draft of Pipe Transformation for matches.
SELECT
 to_date("Datetime", 'DD Mon yyyy') as EVENT_DATE,                             
 r1."MatchID" as "ID",                                  -- bigint
 r1."RoundID" as "RoundID",                             -- bigint
 r1."Home Team Initials" as "HomeTeam",                 -- bigint
 r1."Away Team Initials" as "AwayTeam",                 -- bigint
 null as "VenueID"                     
FROM
S_WORLDCUPMATCHES r1                -- replace by your table name
WHERE r1."Datetime" is not null and r1."MatchID" is not null
         
             
-- Code Snippet 3.2 - World Cup Pipe Transformation.
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",                                   -- bigint
 r1."Country" as "Name"                               -- text
FROM
S_WORLDCUPS r1
             
             
-- Code Snippet 3.4  - Players Pipe Transformation.
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
 r1."Position" as "Position",                          -- text
 r1."Event" as "Event"                                 -- text
FROM
S_WORLDCUPPLAYERS r1
            

-- Code Snippet 3.5 -  Coaches Pipe Transformation.    
SELECT
 '-infinity'::timestamptz as VALID_FROM
 ,'infinity'::timestamptz as VALID_TO
 ,"Team Initials"
 ,"Coach Name"
 ,"RoundID"
 ,"MatchID"
FROM S_WORLDCUPPLAYERS s
             
             
-- Code Snippet 3.6 - Round Pipe Transformation.
SELECT DISTINCT
  '-infinity'::timestamptz as VALID_FROM,
  'infinity'::timestamptz as VALID_TO,
  min(to_date(r1."Datetime", 'DD Mon yyyy')) as "Start",                             
  max(to_date(r1."Datetime", 'DD Mon yyyy')) as "End",                               
  r1."RoundID" as "ID",                                -- bigint
  r1."Year" as "WorldCup"                              -- bigint
FROM
  S_WORLDCUPMATCHES r1 
WHERE 
  r1."Year" is not null
  AND r1."RoundID" is not null
  AND r1."Datetime" != ''
GROUP BY "ID", "WorldCup"
ORDER BY "WorldCup"