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?
Recommended questions for you
Personalized based on your user activity, skill level, and preferences.
.
.
.
.
.
.
.
.
.
Comments