Error while running innerjoin : Error: Unknown exception Detected duplicate prefix in join clauses: [j0.] org.apache.druid.java.util.common.IAE

Hi team,
we have data set we need to slice the database of (week,month,quarter…) and compare it with the previous period. I tried it with simple SQL inner join but it running fine without where clause but as put some condition its showing error:

Pls. someone suggest what we are missing.

Thanks

“Error: Unknown exception Detected duplicate prefix in join clauses: [j0.] org.apache.druid.java.util.common.IAE”


Query working fine:

with aa as (
SELECT distinct __time t ,
TIME_EXTRACT(__time,‘WEEK’) w
FROM demamddata2
where
__time >= cast(‘2021-04-05’ as date) and __time <= cast(‘2021-05-02’ as date)
)
,
ab as (select distinct w from aa
order by w desc
limit 2
)
, ac as (
select sku1code,sum(uprice) paa ,TIME_EXTRACT(__time,‘WEEK’) ww
from demamddata2
where
TIME_EXTRACT(__time,‘WEEK’) in (select w from ab)
group by sku1code, TIME_EXTRACT(__time,‘WEEK’)
)
, gp1 as (
select sku1code sku1g1, paa aag1,ww wwg1
from ac
where ww =(select MAX(a.ww) from ac a )
)
, gp2 as (
select sku1code sku1g2, paa aag2,ww wwg2
from ac
where ww =(select Min(a.ww) from ac a )
)
select gp1.sku1g1 s1,gp1.wwg1 t1 ,gp2.wwg2 t2 ,round((gp2.aag2 - gp1.aag1)*100/gp1.aag1) as pros
from gp1 INNER join gp2 on gp1.sku1g1 = gp2.sku1g2


Query giving error while just adding where clause :

with aa as (
SELECT distinct __time t ,
TIME_EXTRACT(__time,‘WEEK’) w
FROM demamddata2
where
__time >= cast(‘2021-04-05’ as date) and __time <= cast(‘2021-05-02’ as date)
)
,
ab as (select distinct w from aa
order by w desc
limit 2
)
, ac as (
select sku1code,sum(uprice) paa ,TIME_EXTRACT(__time,‘WEEK’) ww
from demamddata2
where
TIME_EXTRACT(__time,‘WEEK’) in (select w from ab)
group by sku1code, TIME_EXTRACT(__time,‘WEEK’)
)
, gp1 as (
select sku1code sku1g1, paa aag1,ww wwg1
from ac
where ww =(select MAX(a.ww) from ac a )
)
, gp2 as (
select sku1code sku1g2, paa aag2,ww wwg2
from ac
where ww =(select Min(a.ww) from ac a )
)
select gp1.sku1g1 s1,gp1.wwg1 t1 ,gp2.wwg2 t2 ,round((gp2.aag2 - gp1.aag1)*100/gp1.aag1) as pros
from gp1 INNER join gp2 on gp1.sku1g1 = gp2.sku1g2
where
round((gp2.aag2 - gp1.aag1)*100/gp1.aag1) >= 10


sampledate

“__time”,“sku1code”,“uprice”
“2021-04-05T00:00:00.000Z”,“35858923”,“350”
“2021-04-05T00:00:00.000Z”,“35858924”,“136”
“2021-04-05T00:00:00.000Z”,“35858923”,“50”
“2021-04-05T00:00:00.000Z”,“35858924”,“50”
“2021-04-05T00:00:00.000Z”,“35840214”,“50”
“2021-05-01T00:00:00.000Z”,“35858923”,“350”
“2021-05-01T00:00:00.000Z”,“35858924”,“350”
“2021-05-01T00:00:00.000Z”,“35856224”,“350”
“2021-05-01T00:00:00.000Z”,“35856221”,“350”
“2021-05-01T00:00:00.000Z”,“35840212”,“50”
“2021-05-01T00:00:00.000Z”,“35847472”,“50”