sql - Decimal Precision in Snowflake: Window Function vs Simple Aggregation

Question

In Snowflake, these queries output the same integer values, but with different decimal precisions. Is this behavior expected?

with cte (num) as

(select 2356 union all
 select 3456 union all
 select 9999)
 
select distinct avg(num) over()
from cte;

Outputs: 5270.333

with cte (num) as

(select 2356 union all
 select 3456 union all
 select 9999)

select avg(num)
from cte;

Outputs: 5270.333333

question from:https://stackoverflow.com/questions/66067857/decimal-precision-in-snowflake-window-function-vs-simple-aggregation

Answer 1

This appears to be because of implicit casting with the literals and functions. You can force consistency by explicitly casting the literal values:

-- Returns 5270.333333333
with cte (num) as

(select 2356::float union all
 select 3456::float union all
 select 9999::float)
 
select distinct avg(num) over()
from cte;


-- Returns 5270.333333333
with cte (num) as

(select 2356::float union all
 select 3456::float union all
 select 9999::float)

select avg(num)
from cte;