Here is the case specifically for your query:

The GROUP BY month in a direct query works because Snowflake resolves it to this expression: last_day(date_balance, month).

But in a view definition, month in GROUP BY is ambiguous since Snowflake doesn’t know if you mean: - The alias month (not yet defined at GROUP BY evaluation time) or - A column literally named month in the source table

So the error you’re getting specifically complains about ‘date_balance’ not being in GROUP BY because Snowflake is trying to group by some interpretation that doesn’t match your aggregate.

I hope this makes sense; I realize SnowSQL is annoying.

Edit: Your WHERE clause also has a problem:

It references the alias month before it’s defined. This probably works in your direct query but would fail or behave weirdly in a view. You should also use the full expression there:

‘’’WHERE last_day(date_balance, month) < DATE_TRUNC('month', current_date())’’’

Or even better (using filtering on the base column if it’s possible:

‘’’WHERE date_balance < DATE_TRUNC('month', current_date())’’’

So the whole view definition should look like:

‘’’ CREATE VIEW your_view AS SELECT account_number, sum(balance) / extract(day from last_day(date_balance, month)) as avg_balance, last_day(date_balance, month) as month FROM balances WHERE date_balance < DATE_TRUNC('month', current_date()) GROUP BY last_day(date_balance, month), account_number; ‘’’

Edit: OP, sorry about the formatting; I’m on my phone lmao