CODEWITHSUNDEEP
oracle-database
Daniel Lee
Daniel Lee

Reputation: 310

How do I join tables in Oracle 19 in a way that reflects the current team ranking while the player is on the team?

I have the following tables in Oracle SQL 19:

Ranking Table:

+--------------+--------------+--------------+
| Team Ranking |     Team     | Ranking Date |
+--------------+--------------+--------------+
|            2 | FC Barcelona | 15-Jul-20    |
|            5 | FC Barcelona | 25-Jul-20    |
|            4 | FC Barcelona | 29-Jul-20    |
|            1 | Real Madrid  | 15-Jul-20    |
|            7 | Real Madrid  | 20-Jul-20    |
|            3 | Real Madrid  | 22-Jul-20    |
|            1 | Real Madrid  | 29-Jul-20    |
+--------------+--------------+--------------+

Player Table:

+--------------------+--------------+------------------+
|       Player       |     Team     | Player Join Date |
+--------------------+--------------+------------------+
| Lionel Messi       | FC Barcelona | 15-Jul-20        |
| Luis Suarez        | FC Barcelona | 15-Jul-20        |
| Antoine Griezmann  | FC Barcelona | 15-Jul-20        |
| Martin Braithwaite | FC Barcelona | 25-Jul-20        |
| Ansu Fati          | FC Barcelona | 27-Jul-20        |
| Eden Hazard        | Real Madrid  | 15-Jul-20        |
| Sergio Ramos       | Real Madrid  | 16-Jul-20        |
| Karim Benzema      | Real Madrid  | 18-Jul-20        |
| Gareth Bale        | Real Madrid  | 20-Jul-20        |
| James Rodriguez    | Real Madrid  | 24-Jul-20        |
+--------------------+--------------+------------------+

I want to join the tables in a way that answers the following question:

  1. When a player joins a team, what is the current or most recent ranking, of the team?
  2. How does the ranking for the team change while the player is on the team?
  3. For example, Sergio Ramos joined Real Madrid on 7/16/20. When he joined, the most recent ranking for Real Madrid was 1 (Ranking Date = 7/15/20). So, I need to show a row with Sergio Ramos with Ranking Date = 7/15/2020, Ranking = 1, Player Join Date = 7/16/20. While Sergio Ramos was on the team, the ranking for Real Madrid changes three more times:
    1. 7/20/20 - Ranking 7
    2. 7/22/20 - Ranking 3
    3. 7/29/20 - Ranking 1
    4. I need to create a row for each of these dates with corresponding ranking for Sergio Ramos.
  4. As another example, Ansu Fati joins FC Barcelona on 7/27/20. The most recent ranking for FC Barcelona when Ansu Fati joins is 5 (Ranking Date = 7/25/20). I need to create a row in the resulting table with Ranking = 5, Ranking Date = 7/25/20 with Ansu Fati. While Ansu Fati is on the team, the ranking changes once to 4 on 7/29/20. Hence, I need to create this row as well on the resulting table.

Below is the resulting table after the join:

+--------------------+------------------+--------------+--------------+--------------+
|       Player       | Player Join Date |     Team     | Team Ranking | Ranking Date |
+--------------------+------------------+--------------+--------------+--------------+
| Ansu Fati          | 27-Jul-20        | FC Barcelona |            5 | 25-Jul-20    |
| Ansu Fati          | 27-Jul-20        | FC Barcelona |            4 | 29-Jul-20    |
| Antoine Griezmann  | 15-Jul-20        | FC Barcelona |            2 | 15-Jul-20    |
| Antoine Griezmann  | 15-Jul-20        | FC Barcelona |            5 | 25-Jul-20    |
| Antoine Griezmann  | 15-Jul-20        | FC Barcelona |            4 | 29-Jul-20    |
| Lionel Messi       | 15-Jul-20        | FC Barcelona |            2 | 15-Jul-20    |
| Lionel Messi       | 15-Jul-20        | FC Barcelona |            5 | 25-Jul-20    |
| Lionel Messi       | 15-Jul-20        | FC Barcelona |            4 | 29-Jul-20    |
| Luis Suarez        | 15-Jul-20        | FC Barcelona |            2 | 15-Jul-20    |
| Luis Suarez        | 15-Jul-20        | FC Barcelona |            5 | 25-Jul-20    |
| Luis Suarez        | 15-Jul-20        | FC Barcelona |            4 | 29-Jul-20    |
| Martin Braithwaite | 25-Jul-20        | FC Barcelona |            5 | 25-Jul-20    |
| Martin Braithwaite | 25-Jul-20        | FC Barcelona |            4 | 29-Jul-20    |
| Eden Hazard        | 15-Jul-20        | Real Madrid  |            1 | 15-Jul-20    |
| Eden Hazard        | 15-Jul-20        | Real Madrid  |            7 | 20-Jul-20    |
| Eden Hazard        | 15-Jul-20        | Real Madrid  |            3 | 22-Jul-20    |
| Eden Hazard        | 15-Jul-20        | Real Madrid  |            1 | 29-Jul-20    |
| Gareth Bale        | 20-Jul-20        | Real Madrid  |            7 | 20-Jul-20    |
| Gareth Bale        | 20-Jul-20        | Real Madrid  |            3 | 22-Jul-20    |
| Gareth Bale        | 20-Jul-20        | Real Madrid  |            1 | 29-Jul-20    |
| James Rodriguez    | 24-Jul-20        | Real Madrid  |            3 | 22-Jul-20    |
| James Rodriguez    | 24-Jul-20        | Real Madrid  |            1 | 29-Jul-20    |
| Karim Benzema      | 18-Jul-20        | Real Madrid  |            1 | 15-Jul-20    |
| Karim Benzema      | 18-Jul-20        | Real Madrid  |            7 | 20-Jul-20    |
| Karim Benzema      | 18-Jul-20        | Real Madrid  |            3 | 22-Jul-20    |
| Karim Benzema      | 18-Jul-20        | Real Madrid  |            1 | 29-Jul-20    |
| Sergio Ramos       | 16-Jul-20        | Real Madrid  |            1 | 15-Jul-20    |
| Sergio Ramos       | 16-Jul-20        | Real Madrid  |            7 | 20-Jul-20    |
| Sergio Ramos       | 16-Jul-20        | Real Madrid  |            3 | 22-Jul-20    |
| Sergio Ramos       | 16-Jul-20        | Real Madrid  |            1 | 29-Jul-20    |
+--------------------+------------------+--------------+--------------+--------------+

I am having a hard time solving this problem in Oracle SQL 19 as I find the logic to be tricky.

Can someone show me how to do this in Oracle SQL 19?

Any help would be greatly appreciated!

Below is the sql query to generate the tables in Oracle for your convenience:

Ranking Table:

with ranking_tbl as (
    select 'FC Barcelona' team, 2 team_ranking, to_date('7/15/2020', 'MM/DD/YYYY') ranking_date from dual union
    select 'FC Barcelona' team, 5 team_ranking, to_date('7/25/2020', 'MM/DD/YYYY') ranking_date from dual union
    select 'FC Barcelona' team, 4 team_ranking, to_date('7/29/2020', 'MM/DD/YYYY') ranking_date from dual union    
    select 'Real Madrid' team, 1 team_ranking, to_date('7/15/2020', 'MM/DD/YYYY') ranking_date from dual union
    select 'Real Madrid' team, 7 team_ranking, to_date('7/20/2020', 'MM/DD/YYYY') ranking_date from dual union
    select 'Real Madrid' team, 3 team_ranking, to_date('7/22/2020', 'MM/DD/YYYY') ranking_date from dual union
    select 'Real Madrid' team, 1 team_ranking, to_date('7/29/2020', 'MM/DD/YYYY') ranking_date from dual
)
select *
from ranking_tbl
order by team, ranking_date

Player Table:

with player_tbl as (
    select 'FC Barcelona' team, 'Lionel Messi' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'FC Barcelona' team, 'Luis Suarez' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'FC Barcelona' team, 'Antoine Griezmann' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union    
    select 'FC Barcelona' team, 'Martin Braithwaite' player, to_date('7/25/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'FC Barcelona' team, 'Ansu Fati' player, to_date('7/27/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'Real Madrid' team, 'Eden Hazard' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'Real Madrid' team, 'Sergio Ramos' player, to_date('7/16/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'Real Madrid' team, 'Karim Benzema' player, to_date('7/18/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'Real Madrid' team, 'Gareth Bale' player, to_date('7/20/2020', 'MM/DD/YYYY') player_join_date from dual union
    select 'Real Madrid' team, 'James Rodriguez' player, to_date('7/24/2020', 'MM/DD/YYYY') player_join_date from dual
)
select *
from player_tbl
order by team, player_join_date

Upvotes: 1

Views: 47

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11616

SQL> with ranking_tbl as (
  2      select 'FC Barcelona' team, 2 team_ranking, to_date('7/15/2020', 'MM/DD/YYYY') ranking_date from dual union
  3      select 'FC Barcelona' team, 5 team_ranking, to_date('7/25/2020', 'MM/DD/YYYY') ranking_date from dual union
  4      select 'FC Barcelona' team, 4 team_ranking, to_date('7/29/2020', 'MM/DD/YYYY') ranking_date from dual union
  5      select 'Real Madrid' team, 1 team_ranking, to_date('7/15/2020', 'MM/DD/YYYY') ranking_date from dual union
  6      select 'Real Madrid' team, 7 team_ranking, to_date('7/20/2020', 'MM/DD/YYYY') ranking_date from dual union
  7      select 'Real Madrid' team, 3 team_ranking, to_date('7/22/2020', 'MM/DD/YYYY') ranking_date from dual union
  8      select 'Real Madrid' team, 1 team_ranking, to_date('7/29/2020', 'MM/DD/YYYY') ranking_date from dual
  9  ),
 10  player_tbl as (
 11      select 'FC Barcelona' team, 'Lionel Messi' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
 12      select 'FC Barcelona' team, 'Luis Suarez' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
 13      select 'FC Barcelona' team, 'Antoine Griezmann' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
 14      select 'FC Barcelona' team, 'Martin Braithwaite' player, to_date('7/25/2020', 'MM/DD/YYYY') player_join_date from dual union
 15      select 'FC Barcelona' team, 'Ansu Fati' player, to_date('7/27/2020', 'MM/DD/YYYY') player_join_date from dual union
 16      select 'Real Madrid' team, 'Eden Hazard' player, to_date('7/15/2020', 'MM/DD/YYYY') player_join_date from dual union
 17      select 'Real Madrid' team, 'Sergio Ramos' player, to_date('7/16/2020', 'MM/DD/YYYY') player_join_date from dual union
 18      select 'Real Madrid' team, 'Karim Benzema' player, to_date('7/18/2020', 'MM/DD/YYYY') player_join_date from dual union
 19      select 'Real Madrid' team, 'Gareth Bale' player, to_date('7/20/2020', 'MM/DD/YYYY') player_join_date from dual union
 20      select 'Real Madrid' team, 'James Rodriguez' player, to_date('7/24/2020', 'MM/DD/YYYY') player_join_date from dual
 21  )
 22  select *
 23  from (
 24    select p.*, r.team_ranking, r.ranking_date,
 25           max(case when r.ranking_date < p.player_join_date then r.ranking_date end)
 26             over ( partition by player ) max_prev_rank
 27  from  player_tbl p,
 28        ranking_tbl r
 29  where p.team = r.team
 30  )
 31  where ranking_date >= max_prev_rank
 32  order by player, ranking_date;

TEAM         PLAYER             PLAYER_JO TEAM_RANKING RANKING_D MAX_PREV_
------------ ------------------ --------- ------------ --------- ---------
FC Barcelona Ansu Fati          27-JUL-20            5 25-JUL-20 25-JUL-20
FC Barcelona Ansu Fati          27-JUL-20            4 29-JUL-20 25-JUL-20
Real Madrid  Gareth Bale        20-JUL-20            1 15-JUL-20 15-JUL-20
Real Madrid  Gareth Bale        20-JUL-20            7 20-JUL-20 15-JUL-20
Real Madrid  Gareth Bale        20-JUL-20            3 22-JUL-20 15-JUL-20
Real Madrid  Gareth Bale        20-JUL-20            1 29-JUL-20 15-JUL-20
Real Madrid  James Rodriguez    24-JUL-20            3 22-JUL-20 22-JUL-20
Real Madrid  James Rodriguez    24-JUL-20            1 29-JUL-20 22-JUL-20
Real Madrid  Karim Benzema      18-JUL-20            1 15-JUL-20 15-JUL-20
Real Madrid  Karim Benzema      18-JUL-20            7 20-JUL-20 15-JUL-20
Real Madrid  Karim Benzema      18-JUL-20            3 22-JUL-20 15-JUL-20
Real Madrid  Karim Benzema      18-JUL-20            1 29-JUL-20 15-JUL-20
FC Barcelona Martin Braithwaite 25-JUL-20            2 15-JUL-20 15-JUL-20
FC Barcelona Martin Braithwaite 25-JUL-20            5 25-JUL-20 15-JUL-20
FC Barcelona Martin Braithwaite 25-JUL-20            4 29-JUL-20 15-JUL-20
Real Madrid  Sergio Ramos       16-JUL-20            1 15-JUL-20 15-JUL-20
Real Madrid  Sergio Ramos       16-JUL-20            7 20-JUL-20 15-JUL-20
Real Madrid  Sergio Ramos       16-JUL-20            3 22-JUL-20 15-JUL-20
Real Madrid  Sergio Ramos       16-JUL-20            1 29-JUL-20 15-JUL-20

19 rows selected.

Upvotes: 0

Related Questions