Natural key for countries

create table countries ( 
  country_iso_code varchar2(2) 
    not null 
    primary key,  
  country_name varchar2(256) 
    not null, 
  home_timezone varchar2(256) 
    not null 
)

begin 
  insert into countries ( country_iso_code, country_name, home_timezone )  
     values ( 'GB', 'United Kingdom', 'Europe/London' ); 
  insert into countries ( country_iso_code, country_name, home_timezone )  
    values ( 'US', 'USA', 'America/Los_Angeles' ); 
  insert into countries ( country_iso_code, country_name, home_timezone )  
     values ( 'DE', 'Germany', 'Europe/Berlin' ); 
  insert into countries ( country_iso_code, country_name, home_timezone )  
     values ( 'IN', 'India', 'Asia/Kolkata' ); 
end; 

commit

UTC for match dates

create table games ( 
  game_id integer 
    generated by default as identity 
    not null 
    primary key 
, game_time timestamp  
    not null 
, game_status varchar2(10) 
    constraint game_status_c check (  
      game_status in ( 'SCHEDULED', 'COMPLETED', 'CANCELLED' )  
    )  
    not null 
)

Rows for home/away countries

create table game_countries ( 
  game_id  
    references games 
    not null 
, country_id 
    references countries 
    not null 
, home_away varchar2(4) 
    check ( home_away in ( 'HOME', 'AWAY' ) ) 
    not null 
, score     integer 
    default on null 0 
    not null 
,  points    integer 
    default on null 0 
    not null 
, unique ( game_id, country_id ) 
, unique ( game_id, home_away ) 
)

create or replace function get_country_id ( iso_code varchar2 )  
  return varchar2 as 
  country_id varchar2(2); 
begin 
  select country_id into country_id  
  from   countries 
  where  country_iso_code = iso_code; 
  return nvl ( country_id, iso_code ); 
end; 

Add games

declare 
  use_columns  boolean := false; 
  store_points boolean := true; 
  use_utc      boolean := true; 
  procedure ins_game (  
    game_status  varchar2, game_time timestamp with time zone, 
    home_country varchar2, away_country varchar2,  
    home_score   int,      away_score int,  
    home_points  int,      away_points int 
  ) as 
    stmt clob; 
  begin 
    stmt := 'insert into games ( game_status, game_time ' || 
    case when use_columns then  
    ', home_country, away_country, home_score, away_score ' 
    end || 
    case when use_columns and store_points then  
    ', home_points, away_points ' 
    end || 
    ') values ( :game_status, :game_time ' || 
    case when use_utc then 
    q'[ at time zone 'UTC' ]' 
    end ||  
    case when use_columns then  
    ', get_country_id ( :home_country ), get_country_id ( :away_country )  
    , :home_score, :away_score ' 
    end || 
    case when use_columns and store_points then  
    ', :home_points, :away_points ' 
    end ||  
    ')'; 
    dbms_output.put_line ( stmt ); 
    if use_columns and store_points then 
      execute immediate stmt 
      using game_status, game_time 
        , home_country, away_country, home_score, away_score  
        , home_points, away_points; 
    elsif use_columns then  
      execute immediate stmt 
      using game_status, game_time 
        , home_country, away_country, home_score, away_score; 
    else 
      execute immediate stmt 
      using game_status, game_time; 
    end if; 
  end ins_game; 
 
begin 
  ins_game (  
    'COMPLETED', timestamp'2022-11-05 15:00:00 Europe/London', 'GB', 'US', 3, 1, 3, 0  
  ); 
  ins_game (  
    'COMPLETED', timestamp'2022-11-05 15:00:00 Europe/Berlin', 'DE', 'IN', 2, 2, 1, 1 
  ); 
  ins_game (  
    'COMPLETED', timestamp'2022-11-12 15:00:00 Asia/Kolkata', 'IN', 'GB', 0, 0, 1, 1 
  ); 
  ins_game (  
    'COMPLETED', timestamp'2022-11-12 15:00:00 America/Los_Angeles', 'US', 'DE', 1, 4, 0, 3 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-19 15:00:00 America/Los_Angeles', 'US', 'IN', null, null, null, null 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-19 15:00:00 Europe/Berlin', 'DE', 'GB', null, null, null, null 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-26 15:00:00 America/Los_Angeles', 'US', 'GB', null, null, null, null 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-26 15:00:00 Asia/Kolkata', 'IN', 'DE', null, null, null, null 
  ); 
end; 

Add countries per game

begin 
  insert into game_countries ( game_id, country_id, home_away, score )  
    values ( 1, get_country_id ( 'GB' ), 'HOME', 3 ); 
  insert into game_countries ( game_id, country_id, home_away, score )  
    values ( 1, get_country_id ( 'US' ), 'AWAY', 1 ); 
   
  insert into game_countries ( game_id, country_id, home_away, score )  
    values ( 2, get_country_id ( 'DE' ), 'HOME', 2 ); 
  insert into game_countries ( game_id, country_id, home_away, score )  
    values ( 2, get_country_id ( 'IN' ), 'AWAY', 2 ); 
   
  insert into game_countries ( game_id, country_id, home_away, score )  
    values ( 3, get_country_id ( 'IN' ), 'HOME', 0 ); 
  insert into game_countries ( game_id, country_id, home_away, score)  
    values ( 3, get_country_id ( 'GB' ), 'AWAY', 0 ); 
   
  insert into game_countries ( game_id, country_id, home_away, score 
  ) values ( 4, get_country_id ( 'US' ), 'HOME', 1 ); 
  insert into game_countries ( game_id, country_id, home_away, score )  
    values ( 4, get_country_id ( 'DE' ), 'AWAY', 4 ); 
   
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 5, get_country_id ( 'US' ), 'HOME' ); 
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 5, get_country_id ( 'IN' ), 'AWAY' ); 
   
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 6, get_country_id ( 'DE' ), 'HOME' ); 
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 6, get_country_id ( 'GB' ), 'AWAY' ); 
   
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 7, get_country_id ( 'US' ), 'HOME' ); 
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 7, get_country_id ( 'GB' ), 'AWAY' ); 
   
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 8, get_country_id ( 'IN' ), 'HOME' ); 
  insert into game_countries ( game_id, country_id, home_away ) 
    values ( 8, get_country_id ( 'DE' ), 'AWAY' ); 
end; 

Set points for completed games

update game_countries g 
set    points = (  
  select case 
      when home_score = away_score then 1 
      when home_away = 'HOME' and home_score > away_score then 3 
      when home_away = 'AWAY' and home_score < away_score then 3 
      else 0 
    end p 
  from ( 
    select * 
    from   game_countries c 
    where  g.game_id = c.game_id 
  ) 
  pivot ( 
    max ( country_id ) country, max ( score ) score 
    for home_away in ( 'HOME' home, 'AWAY' away ) 
  ) 
) 
where  game_id in ( 1, 2, 3, 4 )

alter session set nls_timestamp_format = ' DD Mon HH24:MI '

alter session set nls_timestamp_tz_format = ' DD Mon HH24:MI TZR'

select * from countries

select * from games

select * from game_countries

commit

All games for a team

select * from ( 
  select  
         max ( -- get the local time for the country of interest 
           case when 'US' = country_iso_code then 
             from_tz ( game_time, 'UTC' ) at time zone home_timezone  
           end  
         ) over ( partition by game_id ) game_time,  
         home_away, country_iso_code, score, game_status, game_id 
  from   games 
  join   game_countries 
  using  ( game_id ) 
  join   countries 
  on     country_id = country_iso_code 
) 
pivot ( 
  max ( country_iso_code ) country, max ( score ) score 
  for home_away in ( 'HOME' home, 'AWAY' away ) 
) m 
where  'US' in ( home_country, away_country ) 

SQL macro for country games

create or replace function country_games (  
  country varchar2 
) return clob sql_macro as 
begin 
  return q'! 
select * from ( 
  select  
         max ( -- get the local time for the country of interest 
           case when country = country_iso_code then 
             from_tz ( game_time, 'UTC' ) at time zone home_timezone  
           end  
         ) over ( partition by game_id ) game_time,  
         home_away, country_iso_code, score, game_status, game_id 
  from   games 
  join   game_countries 
  using  ( game_id ) 
  join   countries 
  on     country_id = country_iso_code 
) 
pivot ( 
  max ( country_iso_code ) country, max ( score ) score 
  for home_away in ( 'HOME' home, 'AWAY' away ) 
) m 
where  country in ( home_country, away_country )    
  !'; 
end country_games; 

select * from country_games ( 'IN' )

select * from country_games ( 'DE' )

All games for GB including outcome for completed games

select  
       game_time 
     , home_country, away_country 
     , home_score, away_score 
     , case  
         when game_status <> 'COMPLETED' then 'TBC' 
         when home_score = away_score then 'D' 
         when home_score > away_score and home_country = 'GB' then 'W' 
         when home_score < away_score and away_country = 'GB' then 'W' 
         else 'L' 
       end result 
from   country_games ( 'GB' ) 
order  by game_time

List all fixtures

with rws as (   
  select game_time, game_id, home_away, country_id 
  from   games 
  join   game_countries 
  using  ( game_id ) 
) 
  select  
       game_time at local game_time 
     , game_id, home_country, away_country   
  from   rws 
  pivot ( 
    max ( country_id ) country 
    for home_away in ( 'HOME' home, 'AWAY' away ) 
  )

create or replace function fixtures  
  return clob sql_macro as 
begin 
  return q'! 
with rws as (   
  select game_time, game_id, home_away, country_id 
  from   games 
  join   game_countries 
  using  ( game_id ) 
) 
  select  
       game_time at local game_time 
     , game_id, home_country, away_country   
  from   rws 
  pivot ( 
    max ( country_id ) country 
    for home_away in ( 'HOME' home, 'AWAY' away ) 
  )!'; 
end fixtures; 

Fixtures for next 7 days in session time zone

select game_time 
     , home_country, away_country 
from   table ( fixtures ) 
where  game_time >= timestamp'2022-11-16 00:00:00 UTC' 
and    game_time < timestamp'2022-11-23 00:00:00 UTC' 
order  by game_time

alter session set time_zone = 'Europe/Berlin'

alter session set time_zone = 'Asia/Kolkata'

alter session set time_zone = 'Europe/London'

Summary of results per country

with rws as (   
  select team.country_id country 
    , ( team.score - oppo.score ) as score_diff 
    , case  
        when team.score > oppo.score then 'W'  
        when team.score = oppo.score then 'D'  
        when team.score < oppo.score then 'L' 
      end wdl 
    , team.points 
  from   games g 
  join   game_countries team 
  on     g.game_id = team.game_id 
  join   game_countries oppo 
  on     g.game_id = oppo.game_id 
  and    oppo.country_id <> team.country_id 
  where  game_status = 'COMPLETED' 
) 
  select country 
       , sum ( points ) points, listagg ( wdl ) games, sum ( score_diff ) score_diff 
  from   rws 
  group  by country

SQL macro of country summary

create or replace function country_results  
  return clob sql_macro as 
begin 
  return q'!  
with rws as (   
  select team.country_id country 
    , ( team.score - oppo.score ) as score_diff 
    , case  
        when team.score > oppo.score then 'W'  
        when team.score = oppo.score then 'D'  
        when team.score < oppo.score then 'L' 
      end wdl 
/* Uncomment as appropriate */ 
    , team.points 
  from   games g 
  join   game_countries team 
  on     g.game_id = team.game_id 
  join   game_countries oppo 
  on     g.game_id = oppo.game_id 
  and    oppo.country_id <> team.country_id 
  where  game_status = 'COMPLETED' 
) 
  select country 
       , points, wdl, score_diff 
  from   rws!'; 
end country_results; 

select * from table ( country_results )

select * from table ( country_results ) 
pivot ( 
  count(*) c 
, sum ( score_diff ) diff 
, sum ( points ) points  
  for wdl in ( 'W' W, 'D' D, 'L' L ) 
)

create or replace function league  
  return clob sql_macro as 
begin 
  return q'!  
select * from table ( country_results ) 
pivot ( 
  count(*) c 
, sum ( score_diff ) diff 
, sum ( points ) points  
  for wdl in ( 'W' W, 'D' D, 'L' L ) 
)   
!'; 
end league; 

Final league

select country_name, w_c w, d_c d, l_c l,  
       nvl ( w_points, 0 ) + nvl ( d_points, 0 ) points, 
       nvl ( w_diff, 0 ) + nvl ( d_diff, 0 ) + nvl ( l_diff, 0 ) score_diff 
from   table ( league ) 
join   countries  
on     country = country_iso_code 
order  by points desc, score_diff desc

update games 
set    game_status = 'ABANDONED' 
where  game_id = 4

Add new constraint to allow new status

alter table games 
  add constraint game_statuses_c 
  check (  
    game_status in ( 'SCHEDULED', 'COMPLETED', 'CANCELLED', 'ABANDONED' )  
  )

Drop old constraint to allow new status

alter table games 
  drop constraint game_status_c

update games 
set    game_status = 'ABANDONED' 
where  game_id = 4

select /*+ gather_plan_statistics */* from games 
where  game_status = 'JUNK'

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST'))