-- Suggested testing environments
-- For MS SQL:
-- https://[Log in to view URL] with language set as MS SQL
-- For MySQL:
-- https://[Log in to view URL] with MySQL version set to 8
-- For SQLite:
-- http://[Log in to view URL]
-- Put the following without '--' at the top to enable foreign key support in SQLite.
-- PRAGMA foreign_keys = ON;

-- Example case create statement:
CREATE TABLE regions(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE states(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  regionId INTEGER NOT NULL,
  FOREIGN KEY (regionId) REFERENCES regions(id)
); 

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL, 
  stateId INTEGER NOT NULL,
  FOREIGN KEY (stateId) REFERENCES states(id)
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  amount INTEGER NOT NULL,
  employeeId INTEGER NOT NULL,
  FOREIGN KEY (employeeId) REFERENCES employees(id)
);

INSERT INTO regions(id, name) VALUES(1, 'North');
INSERT INTO regions(id, name) VALUES(2, 'South');
INSERT INTO regions(id, name) VALUES(3, 'East');
INSERT INTO regions(id, name) VALUES(4, 'West');
INSERT INTO regions(id, name) VALUES(5, 'Midwest');

INSERT INTO states(id, name, regionId) VALUES(1, 'Minnesota', 1);
INSERT INTO states(id, name, regionId) VALUES(2, 'Texas', 2);
INSERT INTO states(id, name, regionId) VALUES(3, 'California', 3);
INSERT INTO states(id, name, regionId) VALUES(4, 'Columbia', 4);
INSERT INTO states(id, name, regionId) VALUES(5, 'Indiana', 5);

INSERT INTO employees(id, name, stateId) VALUES(1, 'Jaden', 1);
INSERT INTO employees(id, name, stateId) VALUES(2, 'Abby', 1);
INSERT INTO employees(id, name, stateId) VALUES(3, 'Amaya', 2);
INSERT INTO employees(id, name, stateId) VALUES(4, 'Robert', 3);
INSERT INTO employees(id, name, stateId) VALUES(5, 'Tom', 4);
INSERT INTO employees(id, name, stateId) VALUES(6, 'William', 5);

INSERT INTO sales(id, amount, employeeId) VALUES(1, 2000, 1);
INSERT INTO sales(id, amount, employeeId) VALUES(2, 3000, 2);
INSERT INTO sales(id, amount, employeeId) VALUES(3, 4000, 3);
INSERT INTO sales(id, amount, employeeId) VALUES(4, 1200, 4);
INSERT INTO sales(id, amount, employeeId) VALUES(5, 2400, 5);

-- e.g. 'Minnesota' is the only state under the 'North' region. 
-- Total sales made by employees 'Jaden' and 'Abby' for the state of 'Minnesota' is 5000 (2000 + 3000)
-- Total employees in the state of 'Minnesota' is 2
-- Average sales per employee for the 'North' region = Total sales made for the region (5000) / Total number of employees (2) = 2500
-- Difference between the average sales of the region with the highest average sales ('South'), 
-- and the average sales per employee for the region ('North') = 4000 - 2500 = 1500.
-- Similarly, no sale has been made for the only state 'Indiana' under the region 'Midwest'.
-- So the average sales per employee for the region is 0.
-- And, the difference between the average sales of the region with the highest average sales ('South'), 
-- and the average sales per employee for the region ('Midwest') = 4000 - 0 = 4000.

-- Expected output (rows in any order):
-- name     average   difference
-- -----------------------------
-- North	2500	  1500             
-- South 	4000	  0
-- East		1200   	  2800
-- West		2400	  1600
-- Midwest  	0         4000