Mode SQL

Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (Everywhere else).

Solution:

select case when state in ('OR','WA','CA') then 'West Coast'
            when state = 'TX' then 'Texas'
            else 'Other' END as region,
            count(1) as players
from benn.college_football_players
where weight >= 300
group by 1;

Link