analytical.sql

DROP FUNCTION IF EXISTS countryRankings();
CREATE OR REPLACE FUNCTION countryRankings()
RETURNS TABLE (

    country_code CHAR(3),
    country_name VARCHAR(30),
    count bigint


) AS $$

BEGIN

    DROP VIEW IF EXISTS olympics.COUNTRYOLYMPIADS;
    CREATE VIEW olympics.COUNTRYOLYMPIADS AS (
        SELECT country_code, country_name
        FROM olympics.OLYMPIAD
        LEFT JOIN olympics.COUNTRY
        ON olympics.OLYMPIAD.country = olympics.COUNTRY.country_code
    );

    RETURN QUERY
    SELECT countryolympiads.country_code, countryolympiads.country_name, count(countryolympiads.country_code)
    FROM olympics.COUNTRYOLYMPIADS
    GROUP BY countryolympiads.country_code, countryolympiads.country_name
    ORDER BY count(COUNTRYOLYMPIADS.country_code) DESC;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION topSports(x INT, k INT)
RETURNS TABLE(
    sport_id INT,
    sport_name VARCHAR(30),
    teamCount bigint
) AS $$
DECLARE
    startdate timestamp;
BEGIN
    IF k <= 0 THEN k := 0; END IF;

    startdate := CURRENT_DATE - (x || ' months')::interval;

    RETURN QUERY
    SELECT olympics.sport.sport_id, olympics.sport.sport_name, count(olympics.team.sport)
    FROM olympics.sport LEFT JOIN olympics.team
    ON olympics.sport.sport_id = olympics.team.sport AND olympics.sport.date_added >= startdate
    GROUP BY olympics.sport.sport_id, olympics.sport.sport_name
    ORDER BY count(olympics.team.sport) DESC
    LIMIT k;


END;
$$ LANGUAGE plpgsql;