I have two tables in Athena, both have the same schema (user_id - a string, items - an array of ASINs). One table records users in Week 1 and the other one in Week 2. Some users in both tables share the same user_id while the rest are different from each other. I want to do a full join of both tables on the user_id while the items should be joint as a new array.
I have the following sql:
with w1 as (
select *
from "week1_events"
limit 100
), w2 as (
select *
from "week2_events"
limit 100
), w12 as (
select COALESCE(w1.user_id, w2.user_id) as user_id,
w1.items as items_left,
w2.items as items_right
from w1
full join w2 on w1.user_id = w2.user_id
)
select user_id,
items_left || items_right as items
from w12
But "items_left || items_right" always generates empty array.
