--------------------------------------------------------
--  File created - Sunday-June-22-2014   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table CITIES
--------------------------------------------------------

  CREATE TABLE "CITIES" 
   (	"ID" NUMBER(2,0), 
	"NAME" VARCHAR2(50), 
	"LATTITUDE" NUMBER(5,2), 
	"LONGITUDE" NUMBER(5,2)
   ) ;
--------------------------------------------------------
--  DDL for Table FOOTBALL_REGIONS
--------------------------------------------------------

  CREATE TABLE "FOOTBALL_REGIONS" 
   (	"ID" NUMBER(2,0), 
	"NAME" VARCHAR2(30), 
	"CONTINENT" VARCHAR2(2)
   ) ;
--------------------------------------------------------
--  DDL for Table MATCH_RESULTS
--------------------------------------------------------

  CREATE TABLE "MATCH_RESULTS" 
   (	"GROUP1" VARCHAR2(1), 
	"HOME" NUMBER(1,0), 
	"AWAY" NUMBER(1,0), 
	"HOME_GOALS" NUMBER(2,0), 
	"AWAY_GOALS" NUMBER(2,0), 
	"SDM_ID" NUMBER(2,0), 
	"LOCAL_START_TIME" DATE, 
	"SCORING_PROCESS" VARCHAR2(20), 
	"WEATHER_CATEGORY" VARCHAR2(20), 
	"ID" NUMBER(2,0)
   ) ;
--------------------------------------------------------
--  DDL for Table MATCH_TAGS
--------------------------------------------------------

  CREATE TABLE "MATCH_TAGS" 
   (	"TAG_ID" NUMBER(4,0), 
	"MATCH_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table STADIUMS
--------------------------------------------------------

  CREATE TABLE "STADIUMS" 
   (	"ID" NUMBER(2,0), 
	"CTY_ID" NUMBER(2,0), 
	"NAME" VARCHAR2(50)
   ) ;
--------------------------------------------------------
--  DDL for Table TAGS
--------------------------------------------------------

  CREATE TABLE "TAGS" 
   (	"ID" NUMBER(4,0), 
	"TAG" VARCHAR2(200)
   ) ;
--------------------------------------------------------
--  DDL for Table TEAMS
--------------------------------------------------------

  CREATE TABLE "TEAMS" 
   (	"COUNTRY" VARCHAR2(3), 
	"GROUP1" VARCHAR2(1), 
	"SEQ_IN_GROUP" NUMBER(1,0), 
	"FIFA_RANK" NUMBER(3,0), 
	"RGN_ID" NUMBER(2,0)
   ) ;
REM INSERTING into CITIES
SET DEFINE OFF;
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (1,'Rio de Janeiro',23,43);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (2,'Manaus',3,60);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (3,'Natal',5,35);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (4,'Brasilia',15,48);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (5,'Sao Paulo',23,46);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (6,'Fortaleza',3,38);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (7,'Cuiaba',15,56);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (8,'Belo Horizonte',20,44);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (9,'Curitiba',25,49);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (10,'Porto Alegre',30,51);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (11,'Recife',8,35);
Insert into CITIES (ID,NAME,LATTITUDE,LONGITUDE) values (12,'Salvador',13,38);
REM INSERTING into FOOTBALL_REGIONS
SET DEFINE OFF;
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (1,'Middle East','AS');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (2,'Africa','AF');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (3,'Far East','AS');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (4,'South America','AM');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (5,'North America','AM');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (6,'Central America','AM');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (7,'North Western Europe','EU');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (8,'Eastern Europe','EU');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (9,'Southern Europe','EU');
Insert into FOOTBALL_REGIONS (ID,NAME,CONTINENT) values (10,'Pacific','PC');
REM INSERTING into MATCH_RESULTS
SET DEFINE OFF;
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',1,2,3,1,5,to_timestamp('12-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'1000',null,1);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',3,4,1,0,3,to_timestamp('13-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'0',null,2);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',1,3,0,0,6,to_timestamp('17-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'0',null,17);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('A',4,2,0,4,2,to_timestamp('18-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'1111',null,18);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',1,2,1,5,12,to_timestamp('13-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'011111',null,3);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',3,4,3,1,7,to_timestamp('13-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'0010',null,4);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',1,3,0,2,1,to_timestamp('18-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'11',null,19);
Insert into MATCH_RESULTS (GROUP1,HOME,AWAY,HOME_GOALS,AWAY_GOALS,SDM_ID,LOCAL_START_TIME,SCORING_PROCESS,WEATHER_CATEGORY,ID) values ('B',4,2,2,3,10,to_timestamp('18-JUN-14','DD-MON-RR HH.MI.SSXFF AM'),'10011',null,20);
REM INSERTING into MATCH_TAGS
SET DEFINE OFF;
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (1,1);
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (1,3);
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (3,3);
Insert into MATCH_TAGS (TAG_ID,MATCH_ID) values (3,20);
REM INSERTING into STADIUMS
SET DEFINE OFF;
Insert into STADIUMS (ID,CTY_ID,NAME) values (1,1,'Estádio do Maracanã');
Insert into STADIUMS (ID,CTY_ID,NAME) values (4,4,'Estádio Nacional Mané Garrincha');
Insert into STADIUMS (ID,CTY_ID,NAME) values (5,5,'Arena de São Paulo');
Insert into STADIUMS (ID,CTY_ID,NAME) values (6,6,'Estádio Castelão');
Insert into STADIUMS (ID,CTY_ID,NAME) values (8,8,'Estádio Mineirão');
Insert into STADIUMS (ID,CTY_ID,NAME) values (10,10,'Estádio Beira-Rio');
Insert into STADIUMS (ID,CTY_ID,NAME) values (2,2,'Arena da Amazônia');
Insert into STADIUMS (ID,CTY_ID,NAME) values (12,12,'Arena Fonte Nova');
Insert into STADIUMS (ID,CTY_ID,NAME) values (7,7,'Arena Pantanal');
Insert into STADIUMS (ID,CTY_ID,NAME) values (3,3,'Arena das Dunas');
Insert into STADIUMS (ID,CTY_ID,NAME) values (9,9,'Arena da Baixada');
Insert into STADIUMS (ID,CTY_ID,NAME) values (11,11,'Arena Pernambuco');
REM INSERTING into TAGS
SET DEFINE OFF;
Insert into TAGS (ID,TAG) values (1,'referential integrity');
Insert into TAGS (ID,TAG) values (2,'hattrick');
Insert into TAGS (ID,TAG) values (3,'super goal');
REM INSERTING into TEAMS
SET DEFINE OFF;
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('BRA','A',1,3,4);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('CRO','A',2,18,8);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('MEX','A',3,20,5);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('CMR','A',4,56,2);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('ESP','B',1,1,9);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('NLD','B',2,15,7);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('CHL','B',3,14,4);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('AUS','B',4,62,10);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('COL','C',1,8,4);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('GRC','C',2,12,9);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('CIV','C',3,23,2);
Insert into TEAMS (COUNTRY,GROUP1,SEQ_IN_GROUP,FIFA_RANK,RGN_ID) values ('JPN','C',4,46,3);

create table football_regions
( id number(2) not null primary key
, name varchar2(30)
, continent varchar2(2) -- AF AS PC EU AM
);


create table teams
( country varchar2(3)
, rgn_id number(2)
, group1 varchar2(1)   
, seq_in_group number(1)
, fifa_rank number(3)
);

create table cities
( id number(2) not null primary key
, name varchar2(50)
, lattitude number(5,2)
, longitude number(5,2)
);

create table stadiums
( id number(2) not null primary key
, cty_id number(2) not null
, name varchar2(50)
);


create table match_results
( id number(2)
, group1 varchar2(1)
, home   number(1)
, away   number(1)
, home_goals number(2)
, away_goals number(2)
, sdm_id number(2)
, local_start_time date
, scoring_process varchar2(20) -- '10110' means 1-0, 1-1, 2-1, 3-1, 3-2
, weather_category varchar2(20) -- mild, hot_dry, hot_humid, rain 
);

create table tags
( id number(4) not null primary key
, tag varchar2(200)
);

create table match_tags
( tag_id number(4)
, match_id number
);

------------------------------------  TAGS

with matches as
( select mr.id 
, mr.home_goals
,        mr.away_goals
, t1.country home_country
,      t2.country away_country
, t1.fifa_rank home_fifa_rank
,      t2.fifa_rank away_fifa_rank
, r1.name home_region
, r1.continent home_continent
, r2.name away_region
, r2.continent away_continent
, mr.local_start_time
, mr.scoring_process
, cty.lattitude
from   match_results mr
       join
       stadiums sdm
       on (mr.sdm_id = sdm.id)
       join
       cities cty
       on
       (sdm.cty_id = cty.id)
       join
       teams t1
       on (mr.home = t1.seq_in_group
           and 
           mr.group1 = t1.group1)
       join
       teams t2
       on ( mr.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
        join 
        football_regions r1
        on (t1.rgn_id = r1.id)
        join 
        football_regions r2
        on (t2.rgn_id = r2.id)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,        m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or 
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or 
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,        m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,        m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24') 
         when '13' then 'early' 
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.* 
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all      
 select  'recovery' tag
 ,        m.id match_id
 from     matches m
 where    scoring_process = '101'
) 
select substr(tag, 1, 25) tag
,      home_country
,      away_country
from   tagged
       join
       matches m
       on (m.id = tagged.match_id)
 

TAG                       HOME_COUNTRY AWAY_COUNTRY
------------------------- ------------ ------------
referential integrity     BRA          CRO          
referential integrity     ESP          NLD          
super goal                ESP          NLD          
super goal                AUS          NLD          
Surprise                  ESP          CHL          
Surprise                  BRA          MEX          
Surprise                  ESP          NLD          
Intercontinental          MEX          CMR          
Intercontinental          ESP          CHL          
Intercontinental          AUS          NLD          
Intercontinental          CMR          CRO          
Intercontinental          BRA          CRO          
Intercontinental          CHL          AUS          
north                     MEX          CMR          
central                   ESP          CHL          
north                     BRA          MEX          
central                   ESP          NLD          
south                     AUS          NLD          
north                     CMR          CRO          
central                   BRA          CRO          
central                   CHL          AUS          
early                     MEX          CMR          
early                     AUS          NLD          
goalless tie              BRA          MEX          
recent                    CMR          CRO          
recent                    ESP          CHL          
recent                    AUS          NLD          
recent                    BRA          MEX          
recent                    CHL          AUS          
exciting                  ESP          NLD          
exciting                  AUS          NLD 



with matches as
( select mr.id 
, mr.home_goals
,        mr.away_goals
, t1.country home_country
,      t2.country away_country
, t1.fifa_rank home_fifa_rank
,      t2.fifa_rank away_fifa_rank
, r1.name home_region
, r1.continent home_continent
, r2.name away_region
, r2.continent away_continent
, mr.local_start_time
, mr.scoring_process
, cty.lattitude
from   match_results mr
       join
       stadiums sdm
       on (mr.sdm_id = sdm.id)
       join
       cities cty
       on
       (sdm.cty_id = cty.id)
       join
       teams t1
       on (mr.home = t1.seq_in_group
           and 
           mr.group1 = t1.group1)
       join
       teams t2
       on ( mr.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
        join 
        football_regions r1
        on (t1.rgn_id = r1.id)
        join 
        football_regions r2
        on (t2.rgn_id = r2.id)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,        m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or 
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or 
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,        m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,        m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24') 
         when '13' then 'early' 
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.* 
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all      
 select  'comeback' tag
 ,        m.id match_id
 from     matches m
 where    home_goals != away_goals
 and      exists ( select 'x' from (select substr(m.scoring_process , 1, level) intermediate_score 
                   from dual 
                   connect by level < length( m.scoring_process)
                   )
                   where  length(translate(intermediate_score, '10','1')) > length(translate(intermediate_score, '01','0'))
                   )
) 
select substr(tag, 1, 25) tag
,      home_country
,      away_country
from   tagged
       join
       matches m
       on (m.id = tagged.match_id)
 
with matches as
( select mr.id 
  ,      mr.home_goals
  ,      mr.away_goals
  ,      t1.country home_country
  ,      t2.country away_country
  ,      t1.fifa_rank home_fifa_rank
  ,      t2.fifa_rank away_fifa_rank
  ,      r1.name home_region
  ,      r1.continent home_continent
  ,      r2.name away_region
  ,      r2.continent away_continent
  ,      mr.local_start_time
  ,      mr.scoring_process
  ,      cty.lattitude
  from   match_results mr
         join
         stadiums sdm
         on (mr.sdm_id = sdm.id)
         join
         cities cty
         on (sdm.cty_id = cty.id)
         join
         teams t1
         on ( mr.home = t1.seq_in_group
              and 
              mr.group1 = t1.group1
            )
         join
         teams t2
         on ( mr.away= t2.seq_in_group
              and
              t1.group1 = t2.group1
            )
         join 
         football_regions r1
         on (t1.rgn_id = r1.id)
         join 
         football_regions r2
          on (t2.rgn_id = r2.id)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
)
select substr(tag, 1, 25) tag
,      home_country
,      away_country
from   tagged
       join
       matches m
       on (m.id = tagged.match_id)
 
 
 with matches as
( select mr.id 
  ,      mr.home_goals
  ,      mr.away_goals
  ,      t1.country home_country
  ,      t2.country away_country
  ,      t1.fifa_rank home_fifa_rank
  ,      t2.fifa_rank away_fifa_rank
  ,      r1.name home_region
  ,      r1.continent home_continent
  ,      r2.name away_region
  ,      r2.continent away_continent
  ,      mr.local_start_time
  ,      mr.scoring_process
  ,      cty.lattitude
  from   match_results mr
         join
         stadiums sdm
         on (mr.sdm_id = sdm.id)
         join
         cities cty
         on (sdm.cty_id = cty.id)
         join
         teams t1
         on ( mr.home = t1.seq_in_group
              and 
              mr.group1 = t1.group1
            )
         join
         teams t2
         on ( mr.away= t2.seq_in_group
              and
              t1.group1 = t2.group1
            )
         join 
         football_regions r1
         on (t1.rgn_id = r1.id)
         join 
         football_regions r2
          on (t2.rgn_id = r2.id)
)
, scoring_process (match_id, intermediate_score, iteration) as
( select id, substr(scoring_process,1,1), 1
  from   match_results
  union all
  select id,substr(scoring_process,1,iteration+1), iteration+1
  from   match_results mr
         join
         scoring_process sp
         on (sp.match_id = mr.id and length(scoring_process)>= iteration + 1)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,      m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or 
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or 
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,      m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,      m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24') 
         when '13' then 'early' 
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.* 
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all 
 select  'comeback' tag 
 ,        match_id 
 from     ( select ms.match_id 
            from   ( select sp.* 
                     ,      length(translate('1'||intermediate_score, '10','1')) - length(translate('0'||intermediate_score, '01','0')) running_score 
                     from   scoring_process sp 
                   ) ms          
            having max(running_score)> 0 and min(running_score) < 0 
            group 
            by     ms.match_id 
          )         
)
select substr(tag, 1, 25) tag
,      home_country
,      away_country
from   tagged
       join
       matches m
       on (m.id = tagged.match_id)
  
  
 with matches as
( select mr.id 
  ,      mr.home_goals
  ,      mr.away_goals
  ,      t1.country home_country
  ,      t2.country away_country
  ,      t1.fifa_rank home_fifa_rank
  ,      t2.fifa_rank away_fifa_rank
  ,      r1.name home_region
  ,      r1.continent home_continent
  ,      r2.name away_region
  ,      r2.continent away_continent
  ,      mr.local_start_time
  ,      mr.scoring_process
  ,      cty.lattitude
  from   match_results mr
         join
         stadiums sdm
         on (mr.sdm_id = sdm.id)
         join
         cities cty
         on (sdm.cty_id = cty.id)
         join
         teams t1
         on ( mr.home = t1.seq_in_group
              and 
              mr.group1 = t1.group1
            )
         join
         teams t2
         on ( mr.away= t2.seq_in_group
              and
              t1.group1 = t2.group1
            )
         join 
         football_regions r1
         on (t1.rgn_id = r1.id)
         join 
         football_regions r2
          on (t2.rgn_id = r2.id)
)
, scoring_process (match_id, intermediate_score, iteration) as
( select id, substr(scoring_process,1,1), 1
  from   match_results
  union all
  select id,substr(scoring_process,1,iteration+1), iteration+1
  from   match_results mr
         join
         scoring_process sp
         on (sp.match_id = mr.id and length(scoring_process)>= iteration + 1)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,      m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or 
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or 
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,      m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,      m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24') 
         when '13' then 'early' 
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.* 
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all 
 select  'comeback' tag 
 ,        match_id 
 from     ( select ms.match_id 
            from   ( select sp.* 
                     ,      length(translate('1'||intermediate_score, '10','1')) - length(translate('0'||intermediate_score, '01','0')) running_score 
                     from   scoring_process sp 
                   ) ms          
            having max(running_score)> 0 and min(running_score) < 0 
            group 
            by     ms.match_id 
          )         
)
select substr(tag, 1, 25) tag
,      count(match_id) occurrences
from   tagged
group
by     substr(tag, 1, 25)
order
by     occurrences
   
TAG CLOUD per match:
 with matches as
( select mr.id 
  ,      mr.home_goals
  ,      mr.away_goals
  ,      t1.country home_country
  ,      t2.country away_country
  ,      t1.fifa_rank home_fifa_rank
  ,      t2.fifa_rank away_fifa_rank
  ,      r1.name home_region
  ,      r1.continent home_continent
  ,      r2.name away_region
  ,      r2.continent away_continent
  ,      mr.local_start_time
  ,      mr.scoring_process
  ,      cty.lattitude
  from   match_results mr
         join
         stadiums sdm
         on (mr.sdm_id = sdm.id)
         join
         cities cty
         on (sdm.cty_id = cty.id)
         join
         teams t1
         on ( mr.home = t1.seq_in_group
              and 
              mr.group1 = t1.group1
            )
         join
         teams t2
         on ( mr.away= t2.seq_in_group
              and
              t1.group1 = t2.group1
            )
         join 
         football_regions r1
         on (t1.rgn_id = r1.id)
         join 
         football_regions r2
          on (t2.rgn_id = r2.id)
)
, scoring_process (match_id, intermediate_score, iteration) as
( select id, substr(scoring_process,1,1), 1
  from   match_results
  union all
  select id,substr(scoring_process,1,iteration+1), iteration+1
  from   match_results mr
         join
         scoring_process sp
         on (sp.match_id = mr.id and length(scoring_process)>= iteration + 1)
)
, tagged as
(select t.tag
 ,      mt.match_id
 from   match_tags mt
        join
        tags t
        on (mt.tag_id = t.id)
 union all
 select 'Surprise' tag
 ,      m.id match_id
 from   matches m
 where (m.home_goals > m.away_goals and m.home_fifa_rank - m.away_fifa_rank > 5)
        or 
       (m.home_goals < m.away_goals and m.home_fifa_rank - m.away_fifa_rank < -5)
        or 
       (m.home_goals = m.away_goals and abs(m.home_fifa_rank - m.away_fifa_rank)  > 10)
 union all      
 select 'Intercontinental' tag
 ,      m.id match_id
 from   matches m
 where (m.home_continent != m.away_continent)
 union all      
 select 'Derby' tag
 ,        m.id match_id
 from   matches m
 where (m.home_region = m.away_region)
 union all      
 select case
        when lattitude < 10 then 'north'
        when lattitude > 23 then 'south'
        else 'central' end tag
 ,      m.id match_id
 from   matches m
 union all      
 select  case to_char(m.LOCAL_START_TIME, 'HH24') 
         when '13' then 'early' 
         when '22' then 'late'
         end tag
 ,       m.id match_id
 from    matches m
 where   to_char(m.LOCAL_START_TIME, 'HH24') in ('13','22')
 union all      
 select   'goalless tie' tag
 ,       m.id match_id
 from    matches m
 where   home_goals + away_goals=0
 union all      
 select  'recent' tag
 ,       m.id match_id
 from    ( select m.* 
           ,      row_number() over (order by m.local_start_time desc) rnk
           from   matches m
        ) m
 where   rnk < 6
 union all      
 select  'exciting' tag
 ,        m.id match_id
 from     matches m
 where    home_goals + away_goals > 4
 union all 
 select  'comeback' tag 
 ,        match_id 
 from     ( select ms.match_id 
            from   ( select sp.* 
                     ,      length(translate('1'||intermediate_score, '10','1')) - length(translate('0'||intermediate_score, '01','0')) running_score 
                     from   scoring_process sp 
                   ) ms          
            having max(running_score)> 0 and min(running_score) < 0 
            group 
            by     ms.match_id 
          )         
)
select home_country ||'-'||away_country lineup
,      home_goals ||'-'||away_goals score
,      tags
from   ( select match_id
         ,      listagg(lower(tag),',') within group (order by tag) tags
         from   tagged
         group
         by     match_id
       ) mt
       join
       matches m
       on (m.id = mt.match_id)