<commit_before>WITH unique_years AS (
    SELECT DISTINCT years.year
    FROM (
        SELECT
            substring(ripc.project_data ->> 'startDate' from 1 for 4) :: NUMERIC as YEAR
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' != 'In Kind'

        UNION

        SELECT
            substring(ripc.project_data ->> 'startDate' from 1 for 4) :: NUMERIC as YEAR
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' = 'In Kind'
    ) years
)

SELECT
    unique_years.year as year,
    COALESCE(SUM(icc.in_cash_contribution), 0) as in_cash_contribution,
    COALESCE(SUM(ikc.in_kind_contribution), 0) AS in_kind_contribution,
    COALESCE(SUM(icc.in_cash_contribution), 0) + COALESCE(SUM(ikc.in_kind_contribution), 0) as contribution
FROM unique_years
FULL JOIN (
    select
        unique_years.year as year,
        COALESCE(SUM(icc.in_cash_contribution), 0) as in_cash_contribution
    from unique_years
    JOIN (
        SELECT
            ripc.id,
            substring(ripc.project_data ->> 'startDate' from 1 for 4) :: NUMERIC as YEAR,
            (members ->> 'contributionObtained') :: NUMERIC as in_cash_contribution
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' != 'In Kind'
        GROUP BY YEAR, ripc.id, members
        ORDER BY YEAR
    ) icc ON icc.year = unique_years.year
    GROUP BY unique_years.year
) icc ON icc.year = unique_years.year
FULL JOIN (
    SELECT
        unique_years.year as year,
        COALESCE(SUM(ikc.in_kind_contribution), 0) AS in_kind_contribution
    FROM unique_years
    JOIN (
        SELECT
            ripc.id,
            substring(ripc.project_data ->> 'startDate' from 1 for 4) :: NUMERIC as YEAR,
            (members ->> 'contributionObtained') :: NUMERIC as in_kind_contribution
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' = 'In Kind'
        GROUP BY YEAR, ripc.id, members
        ORDER BY YEAR
    ) ikc ON ikc.year = unique_years.year
    GROUP BY unique_years.year
    ORDER BY unique_years.year
) ikc ON ikc.year = unique_years.year
WHERE
    unique_years.year IS NOT NULL
GROUP BY unique_years.year
ORDER BY unique_years.year<commit_msg>Use instituteStartDate instead of startDate<commit_after>WITH unique_years AS (
    SELECT DISTINCT years.year
    FROM (
        SELECT
            substring(ripc.project_data ->> 'instituteStartDate' from 1 for 4) :: NUMERIC as YEAR
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' != 'In Kind'

        UNION

        SELECT
            substring(ripc.project_data ->> 'instituteStartDate' from 1 for 4) :: NUMERIC as YEAR
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' = 'In Kind'
    ) years
)

SELECT
    unique_years.year as year,
    COALESCE(SUM(icc.in_cash_contribution), 0) as in_cash_contribution,
    COALESCE(SUM(ikc.in_kind_contribution), 0) AS in_kind_contribution,
    COALESCE(SUM(icc.in_cash_contribution), 0) + COALESCE(SUM(ikc.in_kind_contribution), 0) as contribution
FROM unique_years
FULL JOIN (
    select
        unique_years.year as year,
        COALESCE(SUM(icc.in_cash_contribution), 0) as in_cash_contribution
    from unique_years
    JOIN (
        SELECT
            ripc.id,
            substring(ripc.project_data ->> 'instituteStartDate' from 1 for 4) :: NUMERIC as YEAR,
            (members ->> 'contributionObtained') :: NUMERIC as in_cash_contribution
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' != 'In Kind'
        GROUP BY YEAR, ripc.id, members
        ORDER BY YEAR
    ) icc ON icc.year = unique_years.year
    GROUP BY unique_years.year
) icc ON icc.year = unique_years.year
FULL JOIN (
    SELECT
        unique_years.year as year,
        COALESCE(SUM(ikc.in_kind_contribution), 0) AS in_kind_contribution
    FROM unique_years
    JOIN (
        SELECT
            ripc.id,
            substring(ripc.project_data ->> 'instituteStartDate' from 1 for 4) :: NUMERIC as YEAR,
            (members ->> 'contributionObtained') :: NUMERIC as in_kind_contribution
        FROM "research_item_project_competitive" ripc
            JOIN verify v ON ripc.research_item = v.research_item
            JOIN "user" u ON v.research_entity = u.research_entity
            JOIN (
                select
                    ripc.id as project_id,
                    jsonb_array_elements(ripc.project_data -> 'members') as members
                FROM "research_item_project_competitive" ripc
                GROUP BY ripc.id
            ) sub on project_id = ripc.id
        WHERE
            u.id = $1 AND
            members @> json_build_object('email', u.username)::jsonb AND
            ripc.project_data ->> 'type' = 'In Kind'
        GROUP BY YEAR, ripc.id, members
        ORDER BY YEAR
    ) ikc ON ikc.year = unique_years.year
    GROUP BY unique_years.year
    ORDER BY unique_years.year
) ikc ON ikc.year = unique_years.year
WHERE
    unique_years.year IS NOT NULL
GROUP BY unique_years.year
ORDER BY unique_years.year
{'fraction_non_alphanumeric': 0.07568402471315093, 'fraction_numerical': 0.003971756398940865, 'mean_word_length': 1.9537308569566634, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 59, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2890933', 'n_tokens_mistral': 2965, 'n_tokens_neox': 2685, 'n_words': 689}