-- Tricky data

-- Expected result:

-- Position  Team        P  W D L F A Points
-- --------- ----------- -- - - - - - ------
-- 1         Albania     10 4 5 1 4 1 17
-- 2         Luxemburg   10 1 8 1 1 1 11     -- jeden gol na wyjezdzie
-- 3         Malta       10 1 8 1 1 1 11     -- jeden gol, ale w domu (dlatego za Luxemburgiem)
-- 4         Poland      10 1 8 1 1 1 11     -- ma mniejsze TeamId niż San Marino
-- 5         San Marino  10 1 8 1 1 1 11     -- ma większe TeamId niż Poland
-- 6         Ivory Coast 10 0 7 3 0 3 7

IF OBJECT_ID(N'dbo.Games', 'U') IS NOT NULL
  DROP TABLE dbo.Games;
IF OBJECT_ID(N'dbo.Teams', 'U') IS NOT NULL
  DROP TABLE dbo.Teams;
CREATE TABLE dbo.Teams (
  TeamId int NOT NULL,
  TeamName varchar(20) NOT NULL
);
CREATE TABLE dbo.Games (
  HomeId int NOT NULL,
  VisitorId int NOT NULL,
  HomeGoals int NOT NULL,
  VisitorGoals int NOT NULL
);
INSERT INTO dbo.Teams (TeamId, TeamName) 
VALUES (1, 'Poland');
INSERT INTO dbo.Teams (TeamId, TeamName) 
VALUES (2, 'Malta');
INSERT INTO dbo.Teams (TeamId, TeamName) 
VALUES (3, 'Ivory Coast');
INSERT INTO dbo.Teams (TeamId, TeamName) 
VALUES (4, 'Luxemburg');
INSERT INTO dbo.Teams (TeamId, TeamName) 
VALUES (5, 'San Marino');
INSERT INTO dbo.Teams (TeamId, TeamName) 
VALUES (6, 'Albania');

INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 2, 1, 1, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 3, 1, 0, 1;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 4, 1, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 5, 1, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 6, 1, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 1, 2, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 3, 2, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 4, 2, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 5, 2, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 6, 2, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 1, 3, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 2, 3, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 4, 3, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 5, 3, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 6, 3, 1, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 1, 4, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 2, 4, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 3, 4, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 5, 4, 0, 1;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 6, 4, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 1, 5, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 2, 5, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 3, 5, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 4, 5, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 6, 5, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 1, 6, 0, 0;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 2, 6, 0, 1;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 3, 6, 0, 1;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 4, 6, 0, 1;
INSERT INTO dbo.Games (HomeId, VisitorId, HomeGoals, VisitorGoals)
SELECT 5, 6, 1, 0;
GO