Since upgrade to Athena engine version 3 a query with a subquery in the where clause is no longer supported. Athena responds with, "NOT_SUPPORTED: line 24:6: Given correlated subquery is not supported".
Example:
select
sup1.dynamodb.keys.something.s something1,
site.m.something.s something2,
count(1) count
from
"prefs" as sup1
cross join unnest(sup1.dynamodb.newimage.somethings.l) as t(something)
where
1 = 1
and sup1.partition_0 = '2023'
and sup1.partition_1 = '06'
and sup1.partition_2 = '27'
and sup1.partition_3 =
(select
max(sup2.partition_3)
from
"prefs" as sup2
where
sup2.partition_0 = sup1.partition_0
and sup2.partition_1 = sup1.partition_1
and sup2.partition_2 = sup1.partition_2
and sup2.dynamodb.keys.something.s = sup1.dynamodb.keys.something.s)
group by
sup1.dynamodb.keys.something.s,
site.m.something.s
having
count(1) > 1
order by
1