FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

--CREATE 'seasons' TABLE
CREATE TABLE seasons (season INT);

--INSERT SEASON VALUES INTO 'SEASONS'
INSERT INTO seasons VALUES (2002);
INSERT INTO seasons VALUES (2003);
INSERT INTO seasons VALUES (2004);
INSERT INTO seasons VALUES (2005);
INSERT INTO seasons VALUES (2006);
INSERT INTO seasons VALUES (2007);
INSERT INTO seasons VALUES (2008);
INSERT INTO seasons VALUES (2009);
INSERT INTO seasons VALUES (2010);
INSERT INTO seasons VALUES (2011);

--CREATE 'season_wins' table
CREATE TABLE season_wins (
  season INT
  , team_name VARCHAR(50)
  , wins INT
)

--INSERT VALUES INTO 'season_wins'
INSERT INTO season_wins VALUES (2002, 'New Orleans Hornets', 47);
INSERT INTO season_wins VALUES (2003, 'New Orleans Hornets', 41);
INSERT INTO season_wins VALUES (2004, 'New Orleans Hornets', 18);
INSERT INTO season_wins VALUES (2007, 'New Orleans Hornets', 56);
INSERT INTO season_wins VALUES (2008, 'New Orleans Hornets', 49);
INSERT INTO season_wins VALUES (2009, 'New Orleans Hornets', 37);
INSERT INTO season_wins VALUES (2010, 'New Orleans Hornets', 46);
INSERT INTO season_wins VALUES (2011, 'New Orleans Hornets', 21);

--SELECT FROM 'seasons' AND LEFT JOIN TWICE ON 'season_wins' (2ND JOIN TO CALCULATE PREVIOUS WINS)
SELECT 
  *
  , CASE WHEN sw2.wins IS NULL THEN 0 ELSE COALESCE(sw.wins, 0) - sw2.wins END AS wins_increase --COMBINING 'CASE' WITH 'NULL' TO CALCULATE 'wins_increase'
FROM seasons AS s
  LEFT JOIN season_wins AS sw on s.season = sw.season
  LEFT JOIN season_wins AS sw2 on s.season = sw2.season + 1;
  
--USING ABOVE QUERY AS SUB-QUERY TO DETERMINE BIGGEST INCREASE IN WINS SEASON-OVER-SEASON
SELECT
  team_name, MAX(wins_increase) AS max_wins
FROM (
  SELECT 
  	sw.season
    , sw.team_name
    , sw.wins
    , sw2.wins AS wins_prev_season
    , CASE WHEN sw2.wins IS NULL THEN 0 ELSE COALESCE(sw.wins, 0) - sw2.wins END AS wins_increase --COMBINING 'CASE' WITH 'NULL' TO CALCULATE 'wins_increase'
  FROM seasons AS s
    LEFT JOIN season_wins AS sw on s.season = sw.season
    LEFT JOIN season_wins AS sw2 on s.season = sw2.season + 1
  ) AS sq
GROUP BY team_name;

season	season_1	team_name	wins	season_4	team_name_5	wins_6	wins_increase
2002	2002	New Orleans Hornets	47	NULL	NULL	NULL	0
2003	2003	New Orleans Hornets	41	2002	New Orleans Hornets	47	-6
2004	2004	New Orleans Hornets	18	2003	New Orleans Hornets	41	-23
2005	NULL	NULL	NULL	2004	New Orleans Hornets	18	-18
2006	NULL	NULL	NULL	NULL	NULL	NULL	0
2007	2007	New Orleans Hornets	56	NULL	NULL	NULL	0
2008	2008	New Orleans Hornets	49	2007	New Orleans Hornets	56	-7
2009	2009	New Orleans Hornets	37	2008	New Orleans Hornets	49	-12
2010	2010	New Orleans Hornets	46	2009	New Orleans Hornets	37	9
2011	2011	New Orleans Hornets	21	2010	New Orleans Hornets	46	-25

team_name	max_wins
NULL	0
New Orleans Hornets	9

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(10 rows affected)

(2 rows affected)