SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count, COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count
FROM benn.college_football_players
ON companies.permalink = acquisitions.company_permalink
AND acquisitions.company_permalink != '/company/1000memories'
both conditions have to be satisfied for join to take place.
So basically, where clause will be applied to only table (most useful when the where is applied on the column that is used for join)
SELECT start_terminal, duration_seconds, NTILE(4) OVER ntile_window AS quartile, NTILE(5) OVER ntile_window AS quintile, NTILE(100) OVER ntile_window AS percentile FROM tutorial.dc_bikeshare_q1_2012 WINDOW ntile_window AS (PARTITION BY start_terminal ORDER BY duration_seconds