Dynamic Game Summary
Start Timer
0:00:00
A sports team wants to see their record for the season, as well as how much they won or lost by for each game.
Given table scores , which holds the date, result, and score differential of each game, create a varchar column scoreline which contains the summary of the game represented in the following format: {Result} by {Score}, and write a query to retrieve this new data.
Input:
scores table
| Column | Type |
|---|---|
| Date | DATETIME |
| Result | VARCHAR |
| Differential | INTEGER |
Output:
| Column | Type |
|---|---|
| Date | DATETIME |
| Result | VARCHAR |
| Differential | INTEGER |
| Scoreline | VARCHAR |
Example 1:
Input:
| Date | Result | Differential |
|---|---|---|
| 1/1/2024 | W | 1 |
| 1/2/2024 | W | 2 |
| 1/3/2024 | L | 4 |
| 1/4/2024 | W | 3 |
| 1/5/2024 | L | 2 |
| 1/6/2024 | L | 3 |
Output:
| Date | Result | Differential | Scoreline |
|---|---|---|---|
| 1/1/2024 | W | 1 | W by 1 |
| 1/2/2024 | W | 2 | W by 2 |
| 1/3/2024 | L | 4 | L by 4 |
| 1/4/2024 | W | 3 | W by 3 |
| 1/5/2024 | L | 2 | L by 2 |
| 1/6/2024 | L | 3 | L by 3 |
Bonus question: Suppose that this table also contains millions of games. How would you make sure that the ALTER TABLE statement does not bottleneck and cause extensive table locks?
.
.
.
.
.
.
.
.
.
Comments