NBA stats DB api
$ docker compose build$ docker compose up
use mysql on another window
$ docker container exec -it mysql_host bash -c "mysql test_database -uroot -proot"show player table
mysql> SELECT * FROM inactive_players LIMIT 3;
+----+-----------------+------------+------------+-----------+---------------------+---------------------+
| id | full_name | first_name | last_name | is_active | created_at | updated_at |
+----+-----------------+------------+------------+-----------+---------------------+---------------------+
| 2 | Byron Scott | Byron | Scott | 0 | 2022-08-19 13:41:37 | 2022-08-19 13:41:37 |
| 3 | Grant Long | Grant | Long | 0 | 2022-08-19 13:41:37 | 2022-08-19 13:41:37 |
| 7 | Dan Schayes | Dan | Schayes | 0 | 2022-08-19 13:41:37 | 2022-08-19 13:41:37 |
+----+-----------------+------------+------------+-----------+---------------------+---------------------+
3 rows in set (0.00 sec)show game log by each player
mysql> SELECT * FROM player_game_log LIMIT 3;
+-----------+-----------+------------+--------------+-------------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+-----------------+---------------------+---------------------+
| SEASON_ID | Player_ID | Game_ID | GAME_DATE | MATCHUP | WL | MIN | FGM | FGA | FG_PCT | FG3M | FG3A | FG3_PCT | FTM | FTA | FT_PCT | OREB | DREB | REB | AST | STL | BLK | TOV | PF | PTS | PLUS_MINUS | VIDEO_AVAILABLE | created_at | updated_at |
+-----------+-----------+------------+--------------+-------------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+-----------------+---------------------+---------------------+
| 22021 | 203500 | 0022100007 | OCT 20, 2021 | MEM vs. CLE | W | 32 | 4 | 7 | 0.571 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 8 | 14 | 3 | 1 | 1 | 2 | 0 | 8 | 1 | 1 | 2022-08-23 17:32:59 | 2022-08-23 17:32:59 |
| 22021 | 203500 | 0022100034 | OCT 23, 2021 | MEM @ LAC | W | 27 | 6 | 9 | 0.667 | 0 | 0 | 0 | 5 | 5 | 1 | 5 | 4 | 9 | 5 | 2 | 0 | 1 | 0 | 17 | 17 | 1 | 2022-08-23 17:32:59 | 2022-08-23 17:32:59 |
| 22021 | 203500 | 0022100040 | OCT 24, 2021 | MEM @ LAL | L | 34 | 7 | 11 | 0.636 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 8 | 16 | 6 | 0 | 0 | 4 | 5 | 14 | 15 | 1 | 2022-08-23 17:32:59 | 2022-08-23 17:32:59 |
+-----------+-----------+------------+--------------+-------------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+-----------------+---------------------+---------------------+
3 rows in set (0.00 sec)show game log by each team
mysql> SELECT * FROM team_game_log LIMIT 3;
+------------+------------+--------------+-------------+------+------+------+-------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+---------------------+---------------------+
| Team_ID | Game_ID | GAME_DATE | MATCHUP | WL | W | L | W_PCT | MIN | FGM | FGA | FG_PCT | FG3M | FG3A | FG3_PCT | FTM | FTA | FT_PCT | OREB | DREB | REB | AST | STL | BLK | TOV | PF | PTS | created_at | updated_at |
+------------+------------+--------------+-------------+------+------+------+-------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+---------------------+---------------------+
| 1610612737 | 0022100014 | OCT 21, 2021 | ATL vs. DAL | W | 1 | 0 | 1 | 240 | 45 | 94 | 0.479 | 15 | 35 | 0.429 | 8 | 9 | 0.889 | 6 | 49 | 55 | 31 | 8 | 9 | 13 | 16 | 113 | 2022-08-27 11:40:01 | 2022-08-27 11:40:01 |
| 1610612737 | 0022100027 | OCT 23, 2021 | ATL @ CLE | L | 1 | 1 | 0.5 | 240 | 38 | 99 | 0.384 | 10 | 34 | 0.294 | 9 | 15 | 0.6 | 17 | 37 | 54 | 20 | 5 | 3 | 9 | 23 | 95 | 2022-08-27 11:40:01 | 2022-08-27 11:40:01 |
| 1610612737 | 0022100043 | OCT 25, 2021 | ATL vs. DET | W | 2 | 1 | 0.667 | 240 | 46 | 90 | 0.511 | 12 | 32 | 0.375 | 18 | 21 | 0.857 | 10 | 39 | 49 | 24 | 11 | 3 | 13 | 19 | 122 | 2022-08-27 11:40:01 | 2022-08-27 11:40:01 |
+------------+------------+--------------+-------------+------+------+------+-------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+---------------------+---------------------+
3 rows in set (0.00 sec)show points ranking on 1 game in this season, using player game log table
mysql> SELECT p.full_name AS FULL_NAME, SUBSTRING(pgl.MATCHUP, 1, 3) AS team, pgl.GAME_DATE, pgl.MATCHUP, pgl.WL, pgl.PTS, pgl.MIN, pgl.FGM, pgl.FGA, pgl.FG_PCT, pgl.FG3M, pgl.FG3A, pgl.FG3_PCT, pgl.FTM, pgl.FTA, pgl.FT_PCT, pgl.OREB, pgl.DREB, pgl.REB, pgl.AST, pgl.STL, pgl.BLK, pgl.TOV, pgl.PF, pgl.PTS, pgl.PLUS_MINUS, pgl.updated_at FROM player_game_log AS pgl INNER JOIN all_players AS p ON pgl.Player_ID =
p.id ORDER BY PTS DESC LIMIT 20;
+-----------------------+------+--------------+-------------+------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+---------------------+
| FULL_NAME | team | GAME_DATE | MATCHUP | WL | PTS | MIN | FGM | FGA | FG_PCT | FG3M | FG3A | FG3_PCT | FTM | FTA | FT_PCT | OREB | DREB | REB | AST | STL | BLK | TOV | PF | PTS | PLUS_MINUS | updated_at |
+-----------------------+------+--------------+-------------+------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+---------------------+
| Donovan Mitchell | CLE | JAN 02, 2023 | CLE vs. CHI | W | 71 | 50 | 22 | 34 | 0.647 | 7 | 15 | 0.467 | 20 | 25 | 0.8 | 3 | 5 | 8 | 11 | 0 | 1 | 4 | 3 | 71 | 19 | 2023-01-15 08:12:57 |
| Luka Doncic | DAL | DEC 27, 2022 | DAL vs. NYK | W | 60 | 47 | 21 | 31 | 0.677 | 2 | 6 | 0.333 | 16 | 22 | 0.727 | 5 | 16 | 21 | 10 | 2 | 1 | 4 | 5 | 60 | 7 | 2023-01-15 08:12:57 |
| Joel Embiid | PHI | NOV 13, 2022 | PHI vs. UTA | W | 59 | 37 | 19 | 28 | 0.679 | 1 | 5 | 0.2 | 20 | 24 | 0.833 | 1 | 10 | 11 | 8 | 1 | 7 | 5 | 3 | 59 | 25 | 2023-01-15 08:12:57 |
| Devin Booker | PHX | DEC 17, 2022 | PHX vs. NOP | W | 58 | 42 | 21 | 35 | 0.6 | 6 | 12 | 0.5 | 10 | 15 | 0.667 | 1 | 5 | 6 | 5 | 0 | 0 | 2 | 3 | 58 | 11 | 2023-01-15 08:12:57 |
| Anthony Davis | LAL | DEC 04, 2022 | LAL @ WAS | W | 55 | 38 | 22 | 30 | 0.733 | 2 | 3 | 0.667 | 9 | 9 | 1 | 3 | 14 | 17 | 1 | 0 | 3 | 2 | 1 | 55 | 17 | 2023-01-15 08:12:57 |
| Giannis Antetokounmpo | MIL | JAN 03, 2023 | MIL vs. WAS | W | 55 | 37 | 20 | 33 | 0.606 | 0 | 3 | 0 | 15 | 16 | 0.938 | 2 | 8 | 10 | 7 | 2 | 0 | 5 | 3 | 55 | 25 | 2023-01-15 08:12:57 |
| Klay Thompson | GSW | JAN 02, 2023 | GSW vs. ATL | W | 54 | 46 | 21 | 39 | 0.538 | 10 | 21 | 0.476 | 2 | 2 | 1 | 2 | 6 | 8 | 3 | 0 | 1 | 3 | 4 | 54 | 13 | 2023-01-15 08:12:57 |
| Joel Embiid | PHI | DEC 11, 2022 | PHI vs. CHA | W | 53 | 34 | 20 | 32 | 0.625 | 2 | 3 | 0.667 | 11 | 11 | 1 | 3 | 9 | 12 | 3 | 0 | 1 | 2 | 2 | 53 | 11 | 2023-01-15 08:12:57 |
| Pascal Siakam | TOR | DEC 21, 2022 | TOR @ NYK | W | 52 | 41 | 17 | 25 | 0.68 | 2 | 6 | 0.333 | 16 | 18 | 0.889 | 3 | 6 | 9 | 7 | 1 | 0 | 1 | 2 | 52 | 8 | 2023-01-15 08:12:57 |
| Devin Booker | PHX | NOV 30, 2022 | PHX vs. CHI | W | 51 | 31 | 20 | 25 | 0.8 | 6 | 7 | 0.857 | 5 | 6 | 0.833 | 0 | 4 | 4 | 6 | 1 | 0 | 2 | 3 | 51 | 23 | 2023-01-15 08:12:57 |
| Luka Doncic | DAL | DEC 31, 2022 | DAL @ SAS | W | 51 | 37 | 18 | 29 | 0.621 | 6 | 10 | 0.6 | 9 | 15 | 0.6 | 1 | 5 | 6 | 9 | 4 | 1 | 0 | 3 | 51 | 7 | 2023-01-15 08:12:57 |
| Darius Garland | CLE | NOV 13, 2022 | CLE vs. MIN | L | 51 | 40 | 16 | 31 | 0.516 | 10 | 15 | 0.667 | 9 | 13 | 0.692 | 1 | 1 | 2 | 6 | 2 | 0 | 2 | 3 | 51 | 6 | 2023-01-15 08:12:57 |
| Damian Lillard | POR | JAN 12, 2023 | POR vs. CLE | L | 50 | 40 | 16 | 28 | 0.571 | 5 | 13 | 0.385 | 13 | 15 | 0.867 | 0 | 2 | 2 | 3 | 1 | 0 | 1 | 1 | 50 | -2 | 2023-01-15 08:12:57 |
| Luka Doncic | DAL | DEC 23, 2022 | DAL @ HOU | W | 50 | 42 | 17 | 30 | 0.567 | 6 | 12 | 0.5 | 10 | 12 | 0.833 | 0 | 8 | 8 | 10 | 3 | 0 | 7 | 3 | 50 | 0 | 2023-01-15 08:12:57 |
| Stephen Curry | GSW | NOV 16, 2022 | GSW @ PHX | L | 50 | 36 | 17 | 28 | 0.607 | 7 | 11 | 0.636 | 9 | 9 | 1 | 0 | 9 | 9 | 6 | 0 | 1 | 3 | 2 | 50 | -14 | 2023-01-15 08:12:57 |
| Devin Booker | PHX | NOV 18, 2022 | PHX @ UTA | L | 49 | 41 | 16 | 31 | 0.516 | 2 | 9 | 0.222 | 15 | 15 | 1 | 1 | 7 | 8 | 10 | 1 | 0 | 1 | 3 | 49 | -4 | 2023-01-15 08:12:57 |
| Jayson Tatum | BOS | NOV 30, 2022 | BOS vs. MIA | W | 49 | 39 | 15 | 25 | 0.6 | 8 | 12 | 0.667 | 11 | 12 | 0.917 | 1 | 10 | 11 | 3 | 2 | 0 | 1 | 3 | 49 | 29 | 2023-01-15 08:12:57 |
| Lauri Markkanen | UTA | JAN 05, 2023 | UTA @ HOU | W | 49 | 36 | 15 | 27 | 0.556 | 6 | 15 | 0.4 | 13 | 13 | 1 | 1 | 7 | 8 | 1 | 0 | 1 | 0 | 0 | 49 | 16 | 2023-01-15 08:12:57 |
| Ja Morant | MEM | OCT 21, 2022 | MEM @ HOU | W | 49 | 31 | 17 | 26 | 0.654 | 5 | 6 | 0.833 | 10 | 13 | 0.769 | 3 | 1 | 4 | 8 | 1 | 2 | 3 | 2 | 49 | 23 | 2023-01-15 08:12:57 |
| Joel Embiid | PHI | DEC 27, 2022 | PHI @ WAS | L | 48 | 36 | 17 | 32 | 0.531 | 1 | 5 | 0.2 | 13 | 14 | 0.929 | 4 | 6 | 10 | 1 | 3 | 3 | 3 | 5 | 48 | 8 | 2023-01-15 08:12:57 |
+-----------------------+------+--------------+-------------+------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+---------------------+
20 rows in set (0.03 sec)show most FG3M ranking, using player game log
mysql> SELECT p.full_name AS FULL_NAME, SUBSTRING(pgl.MATCHUP, 1, 3) AS team, pgl.GAME_DATE, pgl.MATCHUP, pgl.WL, pgl.FG3M, pgl.MIN, pgl.FGM, pgl.FGA, pgl.FG_PCT, pgl.FG3M, pgl.FG3A, pgl.FG3_PCT, pgl.FTM, pgl.FTA, pgl.FT_PCT, pgl.OREB, pgl.DREB, pgl.REB, pgl.AST, pgl.STL, pgl.BLK, pgl.TOV, pgl.PF, pgl.PTS, pgl.PLUS_MINUS FROM player_game_log AS pgl INNER JOIN all_players AS p ON pgl.Player_ID = p.id WHERE pgl.FG3M = (SELECT MAX(pgl1.FG3M) FROM player_game_log AS pgl1);
+----------------+------+--------------+-------------+------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+
| FULL_NAME | team | GAME_DATE | MATCHUP | WL | FG3M | MIN | FGM | FGA | FG_PCT | FG3M | FG3A | FG3_PCT | FTM | FTA | FT_PCT | OREB | DREB | REB | AST | STL | BLK | TOV | PF | PTS | PLUS_MINUS |
+----------------+------+--------------+-------------+------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+
| Damian Lillard | POR | DEC 12, 2022 | POR vs. MIN | W | 11 | 29 | 13 | 21 | 0.619 | 11 | 17 | 0.647 | 1 | 1 | 1 | 0 | 2 | 2 | 3 | 0 | 0 | 4 | 1 | 38 | 24 |
| CJ McCollum | NOP | DEC 30, 2022 | NOP vs. PHI | W | 11 | 35 | 13 | 20 | 0.65 | 11 | 16 | 0.688 | 5 | 6 | 0.833 | 1 | 3 | 4 | 5 | 2 | 0 | 2 | 2 | 42 | 6 |
| Zach LaVine | CHI | JAN 06, 2023 | CHI @ PHI | W | 11 | 38 | 14 | 19 | 0.737 | 11 | 13 | 0.846 | 2 | 2 | 1 | 1 | 1 | 2 | 6 | 2 | 1 | 3 | 1 | 41 | 17 |
+----------------+------+--------------+-------------+------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+------------+
3 rows in set (0.03 sec)show points ranking in this season
mysql> SELECT MAX(p.full_name) AS FULL_NAME, SUM(pgl.FG3M) AS FG3M, SUM(pgl.MIN) AS MIN, SUM(pgl.FGM) AS FGM, SUM(pgl.FGA) AS FGA, SUM(pgl.FGM) / SUM(pgl.FGA) AS FG_PCT, SUM(pgl.FG3M) AS FG3M, SUM(pgl.FG3A) AS FG3A, SUM(pgl.FG3M) / SUM(pgl.FG3A) AS FG3_PCT, SUM(pgl.FTM) AS FTM, SUM(pgl.FTA) AS FTA, SUM(pgl.FTM) / SUM(pgl.FTA) AS FT_PCT, SUM(pgl.OREB) AS OREB, SUM(pgl.DREB) AS DREB, SUM(pgl.REB) AS REB, SUM(pgl.AST) AS AST, SUM(pgl.STL) AS STL, SUM(pgl.BLK) AS BLK, SUM(pgl.TOV) AS TOV, SUM(pgl.PF) AS PF, SUM(pgl.PTS) AS PTS, MAX(pgl.updated_at) AS updated_at FROM player_game_log AS pgl INNER JOIN all_players AS
p ON pgl.Player_ID = p.id GROUP BY pgl.Player_ID ORDER BY PTS DESC LIMIT 10;
+-------------------------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+---------------------+
| FULL_NAME | FG3M | MIN | FGM | FGA | FG_PCT | FG3M | FG3A | FG3_PCT | FTM | FTA | FT_PCT | OREB | DREB | REB | AST | STL | BLK | TOV | PF | PTS | updated_at |
+-------------------------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+---------------------+
| Luka Doncic | 113 | 1496 | 454 | 912 | 0.4978 | 113 | 322 | 0.3509 | 330 | 452 | 0.7301 | 38 | 320 | 358 | 353 | 64 | 24 | 146 | 117 | 1351 | 2023-01-15 08:12:57 |
| Jayson Tatum | 135 | 1544 | 423 | 906 | 0.4669 | 135 | 391 | 0.3453 | 305 | 354 | 0.8616 | 46 | 300 | 346 | 179 | 44 | 34 | 108 | 88 | 1286 | 2023-01-15 08:12:57 |
| Shai Gilgeous-Alexander | 38 | 1391 | 397 | 788 | 0.5038 | 38 | 108 | 0.3519 | 366 | 401 | 0.9127 | 31 | 158 | 189 | 219 | 66 | 43 | 124 | 107 | 1198 | 2023-01-15 08:12:57 |
| Kevin Durant | 71 | 1403 | 410 | 734 | 0.5586 | 71 | 189 | 0.3757 | 267 | 286 | 0.9336 | 14 | 248 | 262 | 207 | 32 | 58 | 136 | 92 | 1158 | 2023-01-15 08:12:57 |
| Donovan Mitchell | 147 | 1427 | 387 | 797 | 0.4856 | 147 | 371 | 0.3962 | 203 | 234 | 0.8675 | 37 | 117 | 154 | 190 | 57 | 13 | 115 | 99 | 1124 | 2023-01-15 08:12:57 |
| Jaylen Brown | 98 | 1421 | 407 | 817 | 0.4982 | 98 | 298 | 0.3289 | 174 | 219 | 0.7945 | 49 | 234 | 283 | 128 | 43 | 21 | 122 | 110 | 1086 | 2023-01-15 08:12:57 |
| Giannis Antetokounmpo | 25 | 1184 | 382 | 729 | 0.5240 | 25 | 102 | 0.2451 | 296 | 453 | 0.6534 | 80 | 338 | 418 | 187 | 28 | 32 | 140 | 124 | 1085 | 2023-01-15 08:12:57 |
| DeMar DeRozan | 16 | 1481 | 381 | 753 | 0.5060 | 16 | 59 | 0.2712 | 291 | 328 | 0.8872 | 20 | 186 | 206 | 201 | 41 | 19 | 80 | 109 | 1069 | 2023-01-15 08:12:57 |
| Anthony Edwards | 112 | 1597 | 377 | 821 | 0.4592 | 112 | 312 | 0.3590 | 181 | 238 | 0.7605 | 26 | 239 | 265 | 194 | 72 | 23 | 140 | 120 | 1047 | 2023-01-15 08:12:57 |
| Trae Young | 88 | 1358 | 330 | 780 | 0.4231 | 88 | 270 | 0.3259 | 296 | 331 | 0.8943 | 26 | 81 | 107 | 373 | 32 | 6 | 145 | 58 | 1044 | 2023-01-15 08:12:57 |
+-------------------------+------+------+------+------+--------+------+------+---------+------+------+--------+------+------+------+------+------+------+------+------+------+---------------------+
10 rows in set (0.05 sec)show the number of triple-double games by each player, using player game log
mysql> SELECT MAX(p.full_name) AS FULL_NAME, COUNT(1) AS GAMES FROM player_game_log AS pgl INNER JOIN all_players AS p ON p.id = pgl.Player_ID WHERE pgl.PTS >= 10 AND pgl.AST >= 10 AND pgl.REB >= 10 GROUP BY pgl.Player_ID ORDER BY COUNT(1) DESC LIMIT 5;
+------------------+-------+
| FULL_NAME | GAMES |
+------------------+-------+
| Nikola Jokic | 11 |
| Luka Doncic | 10 |
| Domantas Sabonis | 4 |
| James Harden | 3 |
| Ja Morant | 3 |
+------------------+-------+
5 rows in set (0.01 sec)This operation erases the DB container, so the data you put in the DB will disappear.
$ docker compose down$ sudo service docker restart