Hi Quicksight team, I am working on a dashboard migration from tableau, and it requires using two parameters in the custom sql. I am running into an issue because SPICE will not allow me to import with parameters, but direct query gives an issue saying the visualization is taking too long to process. I have also added a distribution key on the id, and sortkeys on calendar day and marketplace to optimize this, but I believe the query is still too complex to load. For reference each table has around 2 billion rows. The ids for both tables are used as a text field in the visualization so that users can compare the two revenues and other metrics.
WITH table1 AS (
SELECT *
FROM table1
WHERE id = <<$ID1>>
),
table2 AS (
SELECT *
FROM table2
WHERE id = <<$ID2>>
)
SELECT
nvl(s.id, t.id) as id,
nvl(s.marketplace_id,t.marketplace_id) as marketplace_id,
nvl(s.entity,t.entity) as entity_id,
nvl(s.calendar_day,t.calendar_day) as calendar_day,
child_account,
case when t.id is null then 'Y' else 'N' end as flag1,
case when s.id is null then 'Y' else 'N' end as flag2,
sum(nvl(s.revenue_usd,0)) as revenue1,
sum(nvl(t.revenue_usd,0)) as revenue2
FROM table1 s
FULL OUTER JOIN table2 t
ON s.id = t.id
AND s.marketplace_id = t.marketplace_id
AND s.entity = t.entity
AND s.calendar_day = t.calendar_day
group by 1,2,3,4,5,6,7```