ORA-00923: ключевое слово FROM не найдено там, где ожидалось

У вас несколько ошибок. Как указано в @ cricket_007, вам не хватает запятой между двумя выражениями счетчика. Но у вас также есть недопустимый синтаксис с вашим предложением filter, которого не существует, по крайней мере, в Oracle; вы использовали двойные кавычки вокруг строковых литералов вместо одинарных кавычек; кавычки вокруг строки даты еще более странные; и вы полагаетесь на настройки NLS для формата даты.

Это ближе к тому, что вам нужно, если я понимаю, что вы пытались:

SELECT 
    count(case when pc.collection = 'players' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_players, 
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'

Однако вывод может быть не очень полезным. Для каждого идентификатора пользователя вы можете получить несколько строк, каждая из которых показывает общее количество (которое вы почему-то назвали avg). Вероятно, вы захотите увидеть счетчики для каждого идентификатора только один раз и увидеть, к какому идентификатору они относятся, поэтому я думаю, вам нужен совокупный, а не аналитический счет:

SELECT 
    pc.user_id,
    count(case when pc.collection = 'players' then pf.pref_selection end)
      AS Avg_players,      
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'
group by pc.user_id