Create a Derived Table

This derived table pulls the data for Winner and Loser names from the atp_matches and atp_players tables using the player Id.

Start from the ATPTennisData Project. Select: Projects > ATPTennisData
Click to add the existing Players atp_matches table from the datasource.
Click Create File(s).
Add a new table using the Add Tables icon.
Name the table MatchesAndPlayers. Click to create the file.
Add the SQL to define where you are going to fetch the data.

table MatchesAndPlayers
  data_source TennisData
  sql """ SELECT m.tournament, m.Surface, m.WinnerId, m.LoserId,m.Score, m.Round,
          m.Minutes,
      concat(p.Fname, ' , ', p.Lname) as WinnerName, concat(pl.Fname, ' , ',pl.Lname) 
          as LoserName
          FROM players.atp_matches m join players.atp_players p on m.winnerid = p.id
               left join players.atp_players pl on m.loserid = pl.id """

Add the column definitions:

  column tournament
    sql tournament
    type string
  column Surface
    sql Surface
    type string
  column WinnerId
    sql WinnerId
    type int
  column LoserId
    sql LoserId
    type int
  column Score
    sql Score
    type string
  column Minutes
    sql Minutes
    type int
  column WinnerName
    sql WinnerName
    type string
  column LoserName
    sql LoserName
    type string
  column number_of_rows
    sql number_of_rows
    type int

Create a RoundName column to make the existing Round abbreviations user-friendly:

column RoundName
  sql """ case when (Round = 'QF') then 'Quarter Final' else
                (case when (Round = 'SF') then 'Semi Final' else
                  (case when (Round = 'F') then 'Final' else '' end )
                 end)
              end """
  type string

Click Save.
Edit the ATPTennisData dataset:
- Add atp_matches and MatchesAndPlayers to table
- Add the join for atp_matches and atp_players. No joins are required for the new MatchesAndPlayers table.
Click Save.
Click Commit.
Click Deploy.

In the Create the Visualizations tutorials, a new sheet is created for a tabular data visualization of the new derived table.

Create a Derived Table

Home