Can you include a division in a window function (redshift)

Question

I am looking a consultant dataset and want to use a window function to calculate a ratio per consultant. I want to know how many sales the consultant made, when they called a customer

select
"consultant", "country",
(count(case when "sales"=1 then "call id" end) / count(case when "call to"='customer' then "call id" end)
over (partition by "consultant" order by "consultant") as "Sales Ratio"
from consultant
group by 1,2

Table that I am using:

Now I am doubting I can use a window function in this case. The error that I get is: The database reported a syntax error: Amazon Invalid operation: syntax error at or near "over" Position: 3191;

Answer 1

I want to know how many sales the consultant made, when they called a customer

I'm a little confused where the window functions come in. This sounds like conditional aggregation:

select "consultant", 
       sum(case when sales = 1 then 1 else 0 end) as num_sales,
       sum(case when sales = 1 and "call to" = 'customer' then 1 else 0 end) as num_sales_with_call,
       sum(case when sales = 1 then 1.0 else 0 end) / sum(case when "call to" = 'customer' then 1 end) as sales_to_call_ratio
from consultant
group by 1;