Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.2
-- Dumped by pg_dump version 13.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: matches; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.matches (
    id_match smallint NOT NULL,
    id_home smallint NOT NULL,
    id_away smallint NOT NULL,
    date_time timestamp without time zone NOT NULL
);

--
-- Name: players; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.players (
    id_player integer NOT NULL,
    id_team smallint NOT NULL,
    first_name character varying(15),
    last_name character varying(15) NOT NULL,
    kit smallint NOT NULL,
    CONSTRAINT players_kit_check CHECK (((kit >= 1) AND (kit <= 99)))
);

--
-- Name: players_id_player_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.players_id_player_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--
-- Name: players_id_player_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.players_id_player_seq OWNED BY public.players.id_player;

--
-- Name: results; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.results (
    id_match smallint NOT NULL,
    home smallint NOT NULL,
    away smallint NOT NULL,
    CONSTRAINT results_away_check CHECK ((away >= 0)),
    CONSTRAINT results_home_check CHECK ((home >= 0))
);

--
-- Name: scores; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.scores (
    id_score smallint NOT NULL,
    id_match smallint NOT NULL,
    id_team smallint NOT NULL,
    id_player smallint NOT NULL,
    goals smallint NOT NULL,
    assists smallint NOT NULL,
    CONSTRAINT scores_assists_check CHECK ((assists >= 0)),
    CONSTRAINT scores_goals_check CHECK ((goals >= 0))
);

--
-- Name: teams; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.teams (
    id_team integer NOT NULL,
    club character varying(25) NOT NULL,
    shorthand character varying(3) NOT NULL,
    CONSTRAINT teams_shorthand_check CHECK ((length((shorthand)::text) = 3))
);

--
-- Name: teams_id_team_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.teams_id_team_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--
-- Name: teams_id_team_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.teams_id_team_seq OWNED BY public.teams.id_team;

--
-- Name: players id_player; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.players ALTER COLUMN id_player SET DEFAULT nextval('public.players_id_player_seq'::regclass);

--
-- Name: teams id_team; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.teams ALTER COLUMN id_team SET DEFAULT nextval('public.teams_id_team_seq'::regclass);

--
-- Data for Name: matches; Type: TABLE DATA; Schema: public; Owner: postgres
--

insert into public.matches (id_match, id_home, id_away, date_time) values
(1, 1, 2, '2018-11-03 16:30:00'),
(2, 1, 3, '2019-11-03 16:30:00'),
(3, 2, 3, '2019-11-03 16:30:00'),
(4, 3, 1, '2020-11-03 16:30:00');

--
-- Data for Name: players; Type: TABLE DATA; Schema: public; Owner: postgres
--

insert into public.players (id_player, id_team, first_name, last_name, kit) values 
(2, 1, 'Rene', 'Verbanec', 10),
(3, 1, 'Sebastjan', 'Verbanec', 10),
(4, 1, 'Tadej', 'Frankovic', 11),
(5, 1, 'Jure', 'Hudelja', 17),
(6, 2, 'Eden', 'Hazard', 17),
(7, 2, 'Cristiano', 'Ronaldo', 7),
(8, 2, 'Lionel', 'Messi', 72),
(9, 3, 'Rafel', 'Falcaop', 25),
(10, 3, 'Riyad', 'Mahrez', 32);

--
-- Data for Name: results; Type: TABLE DATA; Schema: public; Owner: postgres
--

insert into public.results (id_match, home, away) values
(1, 5, 3),
(2, 2, 0),
(3, 0, 0),
(4, 1, 3);

--
-- Data for Name: scores; Type: TABLE DATA; Schema: public; Owner: postgres
--

insert into public.scores (id_score, id_match, id_team, id_player, goals, assists) values 
(1, 1, 1, 2, 3, 1),
(2, 1, 1, 3, 2, 2),
(3, 1, 2, 7, 3, 0),
(4, 2, 1, 4, 2, 0),
(5, 4, 3, 9, 3, 0),
(6, 4, 1, 2, 1, 0),
(7, 4, 1, 4, 0, 1);

--
-- Data for Name: teams; Type: TABLE DATA; Schema: public; Owner: postgres
--

insert into public.teams (id_team, club, shorthand) values 
(1, 'Vinica' , 'VIN'),
(2, 'Kolpa', 'KOL'),
(3, 'Crnomelj', 'CRN'),
(4, 'Klub Študentov', 'KBS'),
(5, 'Zilje', 'ZLJ'),
(6, 'Preloka', 'PRK');

--
-- Name: players_id_player_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.players_id_player_seq', 10, true);

--
-- Name: teams_id_team_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.teams_id_team_seq', 6, true);

--
-- Name: matches matches_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.matches
    ADD CONSTRAINT matches_pkey PRIMARY KEY (id_match);

--
-- Name: players players_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.players
    ADD CONSTRAINT players_pkey PRIMARY KEY (id_player);

--
-- Name: results results_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.results
    ADD CONSTRAINT results_pkey PRIMARY KEY (id_match);

--
-- Name: scores scores_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.scores
    ADD CONSTRAINT scores_pkey PRIMARY KEY (id_score);

--
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_pkey PRIMARY KEY (id_team);

--
-- Name: teams teams_shorthand_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_shorthand_key UNIQUE (shorthand);

--
-- Name: matches matches_id_away_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.matches
    ADD CONSTRAINT matches_id_away_fkey FOREIGN KEY (id_away) REFERENCES public.teams(id_team);

--
-- Name: matches matches_id_home_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.matches
    ADD CONSTRAINT matches_id_home_fkey FOREIGN KEY (id_home) REFERENCES public.teams(id_team);

--
-- Name: players players_id_team_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.players
    ADD CONSTRAINT players_id_team_fkey FOREIGN KEY (id_team) REFERENCES public.teams(id_team);

--
-- Name: results results_id_match_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.results
    ADD CONSTRAINT results_id_match_fkey FOREIGN KEY (id_match) REFERENCES public.matches(id_match);

--
-- Name: scores scores_id_match_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.scores
    ADD CONSTRAINT scores_id_match_fkey FOREIGN KEY (id_match) REFERENCES public.matches(id_match);

--
-- Name: scores scores_id_player_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.scores
    ADD CONSTRAINT scores_id_player_fkey FOREIGN KEY (id_player) REFERENCES public.players(id_player);

--
-- Name: scores scores_id_team_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.scores
    ADD CONSTRAINT scores_id_team_fkey FOREIGN KEY (id_team) REFERENCES public.teams(id_team);

--
-- PostgreSQL database dump complete
--

set_config

4 rows affected

9 rows affected

4 rows affected

7 rows affected

6 rows affected

setval
10

setval
6

select t.club as team_name,
       count(*) as total_number_of_matches,
       count(*) filter (where is_home and r.home > r.away or not is_home and r.away > r.home) as matches_won,
       count(*) filter (where r.home = r.away) as matches_draw,
       count(*) filter (where is_home and r.home < r.away or not is_home and r.away < r.home) as matches_lost,
       sum(r.home) filter (where is_home) + sum(r.away) filter (where not is_home) as total_goals_scored,
       sum(r.away) filter (where is_home) + sum(r.home) filter (where not is_home) as total_goals_conceded,
       sum(r.home - r.away) filter (where is_home) + sum(r.away - r.home) filter (where not is_home) as goal_difference,
       (3 * count(*) filter (where is_home and r.home > r.away or not is_home and r.away > r.home) +
        count(*) filter (where r.home = r.away) 
       ) as points
from public.matches m cross join lateral
     (values (m.id_home, true), (m.id_away, false)
     ) v(id_team, is_home) join
     public.teams t
     on t.id_team = v.id_team join
     public.results r
     on m.id_match = r.id_match
group by t.club;

team_name	total_number_of_matches	matches_won	matches_draw	matches_lost	total_goals_scored	total_goals_conceded	goal_difference	points
Kolpa	2	0	1	1	3	5	-2	1
Vinica	3	3	0	0	10	4	6	9
Crnomelj	3	0	1	2	1	5	-4	1