What is Another way to use division with window function and case when statement

Question

I want to calculate Cost per impression (CPM) in a specific time so i use this, worked, but any other way that shorter? I use Window function to show for all row no need GROUP BY

select      (sum(case when 
time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then amount_spent_usd else null end) over ()/
sum(case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then impressions else null end) over ())*1000 
as CPM
from t1

i use this but not work

select
case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then (sum(amount_spent_usd) over ()/ sum(impressions) over ())*1000 
else null end as cpm
from t1

Answer 1

A filtered aggregate should do that:

(sum(amount_spent_usd) filter (where time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
                                 and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59') over ())
                                  
/ 
   
((sum(impressions) filter (where time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
                             and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59') over ()) * 1000)