Join WITH tables in SQL redshift

0
votes
with temp as(
select  account_id, asm_signatures_classification, count(*) 
from asm_insights 
where date = '2020-05-20'
group by account_id, asm_signatures_classification
order by account_id
)

with 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`enter code here`

i wan to have smaller tables for doing some practices, how can i join 2 temp tables like that? what i did getting an error: SQL Error [500310] [42601]: Amazon Invalid operation: syntax error at or near "with" Position: 195;

2
sqljoinamazon-redshiftcommon-table-expression

2 Answers

0
votes

Do not repeat with. One with can define multiple CTEs:

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;

Of course, this seems to be a silly example, because CTEs are not needed for this. Even so, I fixed some issues:

  • ORDER BY is not appropriate in CTEs (unless you are limiting the number of rows).
  • All columns should have aliases.

This particularly query is more simply written as:

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

where date = '2020-05-20'

0
votes

Please use below query,

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;