WITH pd1617 AS (
    SELECT
        CONCAT(
            IF(
                LENGTH(CAST(CAST(B/100 AS int) AS string)) = 4,
                '00', -- if 4 digits long, pad with two 0s
                '0'   -- if 5 digits long, pad with one 0
            ),
            CAST(CAST(B/100 AS int) AS string) -- strip last two digits and convert to string
        ) AS ope_id,
        C AS name,
        CAST(G AS float) AS awards,
        CAST(H AS float) AS recipients
    FROM pell_disbursements
    WHERE rownum >= 6 AND B IS NOT NULL
    AND pell_disbursements._meta.file_upload.file = 'pellinst1617.xlsx'
),
cs1617 AS (
    SELECT
        OPEID6 AS ope_id,
        IF(WOMENONLY = '1', 'all-female',
            IF(MENONLY = '1', 'all-male', 'mixed')) AS category
    FROM college_scorecard_16_17
)
SELECT
    cs1617.category AS cateogry,
    SUM(pd1617.awards) / SUM(pd1617.recipients) AS average_grant,
    COUNT(*) AS num_institutions
FROM cs1617 JOIN pd1617
    ON cs1617.ope_id = pd1617.ope_id
GROUP BY cs1617.category
ORDER BY average_grant DESC

/*-------------------+------------+--------------------+
| average_grant      | cateogry   | num_institutions   |
|--------------------+------------+--------------------|
| 5147.864353018005  | all-male   | 61                 |
| 4138.559830296018  | all-female | 39                 |
| 3644.2603193025525 | mixed      | 6964               |
+--------------------+------------+-------------------*/