DatabaseCreation: sponsor_generator.sql

-- random sponsor names
INSERT INTO
    Sponsor (company_name)
VALUES
    ('Albatross Energy'),
    ('Kronos Bank'),
    ('Veltex Sportswear'),
    ('Orion Telecom'),
    ('Nexus Brewing Co.'),
    ('Solara Motors'),
    ('Pinnacle Insurance'),
    ('Helix Pharma'),
    ('Titan Construction'),
    ('Ember Airlines'),
    ('Cascade Finance'),
    ('Polaris Media Group'),
    ('Axiom Logistics'),
    ('Verdant Foods'),
    ('Stratos Tech'),
    ('Cobalt Gaming'),
    ('Meridian Hotels'),
    ('Fenix Apparel'),
    ('Crestwood Beverages'),
    ('Aurum Jewellers'),
    ('Driftwood Publishing'),
    ('Ironclad Security'),
    ('Luminary Cosmetics'),
    ('Quantum Analytics');

-- sponsor deals
WITH
 -- 1: find every (team, season) pair that exists in the data
-- by unioning home and away teams from the Match table
-- example:
-- team_id  season_id
-- 1        10
-- 2        10
-- 1        11
team_seasons AS (
    SELECT
        DISTINCT home_team_id AS team_id,
        season_id
    FROM
        Match
    UNION
    SELECT
        DISTINCT away_team_id AS team_id,
        season_id
    FROM
        Match
),
-- 2: get the first and last match date for each season
-- so we know the date range to use for the sponsor deal
-- example:
-- season_id    season_start    season_end
-- 10           2023-08-12      2024-05-19
-- 11           2024-08-10      2025-05-18
season_date_ranges AS (
    SELECT
        season_id,
        MIN(match_date) AS season_start,
        MAX(match_date) AS season_end
    FROM
        Match
    GROUP BY
        season_id
),
-- 3: join team_seasons with season_date_ranges and randomly
-- assign each (team, season) between 1 and 3 sponsors
-- example:
-- team_id  season_id   season_start    season_end      num_sponsors
-- 1        10          2023-08-12      2024-05-19      2
-- 2        10          2023-08-12      2024-05-19      1
-- 3        10          2023-08-12      2024-05-19      3
sponsor_assignments AS (
    SELECT
        ts.team_id,
        ts.season_id,
        sdr.season_start,
        sdr.season_end,
        1 + FLOOR(RANDOM() * 3) :: INT AS num_sponsors
    FROM
        team_seasons ts
        JOIN season_date_ranges sdr ON sdr.season_id = ts.season_id
),
-- 4: cross join every (team, season) with every sponsor to get
-- all possible pairings, then shuffle them randomly within each
-- (team, season) group using ROW_NUMBER() + ORDER BY RANDOM()
-- this gives each sponsor a unique random rank per (team, season)
-- example:
-- team_id  season_id   num_sponsors    sponsor_id  rn
-- 1        10          2              3            1
-- 1        10          2              1            2
-- 1        10          2              4            3
-- 1        10          2              2            4
-- 2        10          1              2            1
-- 2        10          1              4            2
shuffled_sponsors AS (
    SELECT
        sa.team_id,
        sa.season_id,
        sa.season_start,
        sa.season_end,
        sa.num_sponsors,
        sp.sponsor_id,
        ROW_NUMBER() OVER (
            PARTITION BY sa.team_id,
            sa.season_id
            ORDER BY
                RANDOM()
        ) AS rn
    FROM
        sponsor_assignments sa
        CROSS JOIN Sponsor sp
),
-- 5: keep only the first N sponsors for each (team, season)
-- where N is num_sponsors. because rn is unique within each group,
-- this guarantees no duplicate sponsors for the same (team, season)
-- example:
-- team_id  season_id   season_start    season_end  sponsor_id
-- 1        10          2023-08-12      2024-05-19  3
-- 1        10          2023-08-12      2024-05-19  1
-- 2        10          2023-08-12      2024-05-19  2
matched_sponsors AS (
    SELECT
        team_id,
        season_id,
        season_start,
        season_end,
        sponsor_id
    FROM
        shuffled_sponsors
    WHERE
        rn <= num_sponsors
),
-- 6: assign a random deal value and random start/end dates
-- that fall within the season's date range.
-- start_date is a random date in the first 75% of the season,
-- end_date is a random date between start_date and season_end.
-- example:
-- sponsor_id  team_id  season_id   start_date    end_date      deal_value
-- 3           1        10          2023-09-15    2024-02-28    12,340,000
-- 1           1        10          2023-10-01    2024-04-10     4,780,000
-- 2           2        10          2023-08-20    2023-12-05      890,000
final_deals AS (
    SELECT
        sponsor_id,
        team_id,
        season_id,
        ROUND((100000 + RANDOM() * 49900000) :: NUMERIC, -4) AS deal_value,
        season_start + (RANDOM() * 0.75 * (season_end - season_start)) :: INT AS start_date,
        season_start + (RANDOM() * (season_end - season_start)) :: INT AS end_date
    FROM
        matched_sponsors
) 
-- 7: insert into Sponsor_deal, ensuring end_date is always
-- after start_date by using GREATEST/LEAST to swap if needed
INSERT INTO
    Sponsor_deal (
        sponsor_id,
        team_id,
        season_id,
        deal_value,
        start_date,
        end_date
    )
SELECT sponsor_id,
    team_id,
    season_id,
    deal_value,
    LEAST(start_date, end_date) AS start_date,
    GREATEST(start_date, end_date) + 30 AS end_date
FROM
    final_deals;