Federal Contract Spending Trends by Agency

-- Federal Contract Spending Trends by Agency
SELECT
    fiscal_year,
    agency_id,
    agency_name,
    round(sum(obligated_amount) / 1000000000, 2) AS spending_billion_usd,
    count() AS award_count,
    round(sum(obligated_amount) / count(), 0) AS avg_award_size
FROM
(
    SELECT
        if(
            toUInt8(substring(content__award__relevantContractDates__signedDate, 6, 2)) >= 10,
            toUInt16(substring(content__award__relevantContractDates__signedDate, 1, 4)) + 1,
            toUInt16(substring(content__award__relevantContractDates__signedDate, 1, 4))
        ) AS fiscal_year,
        content__award__purchaserInformation__contractingOfficeAgencyID AS agency_id,
        content__award__purchaserInformation__contractingOfficeAgencyID__name AS agency_name,
        content__award__dollarValues__obligatedAmount AS obligated_amount
    FROM fpds.data
    WHERE
        contract_type = 1
        AND content__award__relevantContractDates__signedDate != ''
        AND content__award__purchaserInformation__contractingOfficeAgencyID IS NOT NULL
        AND content__award__purchaserInformation__contractingOfficeAgencyID != ''
)
WHERE fiscal_year < toYear(today())
GROUP BY
    fiscal_year,
    agency_id,
    agency_name
ORDER BY
    fiscal_year ASC,
    spending_billion_usd DESC