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.
IN THIS PAGE
Sign up for a forever free account!
Connect to leading SQL databases
Build visual data models without learning a new language
Create two dashboards and ten reports for free