Snowflake won't allow an analytic function to be renamed

Question

In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails. I think this is valid SQL. But I'm using Snowflake, so I'm asking it on Super User as I fear this is a vendor issue.

How can I create a table with the output of that query?

drop table a;

create table a (
    a1 varchar,
    a2 varchar
);

insert into a values ( 'a', 1 );
insert into a values ( 'a', 3 );
insert into a values ( 'a', 5 );
insert into a values ( 'a', 9 );
insert into a values ( 'b', 1 );
insert into a values ( 'b', 3 );
insert into a values ( 'b', 4 );
insert into a values ( 'c', 3 );
insert into a values ( 'c', 4 );
insert into a values ( 'c', 5 );

-- This works fine.
select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2)
  from a
;

-- This fails.
select a1
     , a2
     , lag(a2) new_col_name
  over (partition by a1 order by a2)
  from a
;

-- But if I can't name the column, I can't CTAS.
create table b as
select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2)
  from a
;

Output is:

status
A successfully dropped.
status
Table A successfully created.
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
number of rows inserted
                      1
A1  A2  LiteralRedacted0
b   1   NULL
b   3   1
b   4   3
c   3   NULL
c   4   3
c   5   4
a   1   NULL
a   3   1
a   5   3
a   9   5
001003 (42000): SQL compilation error:
syntax error line 4 at position 2 unexpected 'over'.
002022 (42601): SQL compilation error:
Missing column specification

Answer 1

lag(...) over(...) is an entire expression, you cannot split it at will. The correct syntax is

select a1
     , a2
     , lag(a2) over (partition by a1 order by a2) new_col_name
from a

Answer 2

SQL server has another syntax for creating tables from another select.

Also you need to do that all columns named

--drop table a;

create table a (
    a1 varchar,
    a2 varchar
);

insert into a values ( 'a', 1 );
insert into a values ( 'a', 3 );
insert into a values ( 'a', 5 );
insert into a values ( 'a', 9 );
insert into a values ( 'b', 1 );
insert into a values ( 'b', 3 );
insert into a values ( 'b', 4 );
insert into a values ( 'c', 3 );
insert into a values ( 'c', 4 );
insert into a values ( 'c', 5 );

-- This works fine.
select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2)
  from a
;

-- This fails.
select a1
     , a2
     , lag(a2) 
  over (partition by a1 order by a2) as new_col_name
  from a
;

-- But if I can't name the column, I can't CTAS.

select a1
     , a2
     , lag(a2)
  over (partition by a1 order by a2) as new_column

  INTO b
  from a
;

a1	a2	(No column name)
a	1	null
a	3	1
a	5	3
a	9	5
b	1	null
b	3	1
b	4	3
c	3	null
c	4	3
c	5	4

a1	a2	new_col_name
a	1	null
a	3	1
a	5	3
a	9	5
b	1	null
b	3	1
b	4	3
c	3	null
c	4	3
c	5	4

SELECT * FROM b

a1	a2	new_column
a	1	null
a	3	1
a	5	3
a	9	5
b	1	null
b	3	1
b	4	3
c	3	null
c	4	3
c	5	4

fiddle