不要重复 with . 1 with 可以定义多个CTE：

with temp as (
      select  account_id, asm_signatures_classification, count(*) as cnt
      from asm_insights 
      where date = '2020-05-20'
      group by account_id, asm_signatures_classification
     ),
     temp2 as (
      select account_id, app_id
      from asm_insights 
      where date = '2020-05-20'
     )
select *
from temp join
     temp2
     on temp.account_id = temp2.account_id;

当然，这似乎是一个愚蠢的例子，因为这并不需要CTE。即便如此，我还是解决了一些问题： ORDER BY 在CTE中不合适（除非限制行数）。
所有列都应该有别名。
这个查询更简单地写为：

select account_id, app_id, asm_signatures_classification
       cont(*) over (partition by account_id, asm_signatures_classification)
from asm_insights

其中日期='2020-05-20'

请使用下面的查询，

with temp as (
select  account_id, asm_signatures_classification, count(*) 
from asm_insights t1
inner join (select  account_id, app_id from asm_insights where date = '2020-05-20') t2
on (temp.account_id = temp2.account_id)
where date = '2020-05-20'
group by account_id, asm_signatures_classification
order by account_id)
select * from temp;