I've got a SQL query I want to visualize through Tableau that uses a WITH AS condition to create a temp table, query against it and combine the results into a single table that can be visualized against. However the request is "executing query" for at least 20 minutes now, while it takes 4 minutes for the same query to return results through RazorSQL. Reading through other comments regarding using custom sql and slowness the main solution seems to be to create a data source using this query and then do the calcs through Tableau. Based on some work I've done with Tableau & SQL contractors this is a bit difficult to run through Tableau so trying to get an MVP of this report out within SQL before I spend time figuring it out and optimizing in Tableau.
A version of the query is below, it's designed to calculate wonback & lost users per a marketing request. This is the SQL I've been using and it renders results within 4 minutes looking at 1% of our users, so I know it works from a query perspective. I removed the order by clauses from each of the subqueries and final query since that didn't seem to matter.
Does the group have any thoughts on ways to make this work better?
with tb as (select date(timestamp) as dash_date
where event = 'login'
and right(user_id,2) in (89)
, winback as (
select Adate as date, count(A.user_id) as winback
(select distinct user_id, tb.dash_date as Adate from tb inner join dim_date on tb.dash_date = dim_date.thedate) as A
left join (select distinct tb.user_id, dim_date.thedate as Bdate from tb inner join dim_date on datediff('day',dim_date.thedate, tb.dash_date) <= 28 and datediff('day',dim_date.thedate, tb.dash_date) > 0) as B
on A.user_id = B.user_id and A.Adate = B.Bdate
where B.user_id is NULL
group by 1
, lost as
select Adate as date, count(A.user_id) as lost
from (select distinct user_id, dim_date.thedate as Adate from tb inner join dim_date on datediff('day',dim_date.thedate, tb.dash_date) = 28) as A
left join (select distinct user_id, dim_date.thedate as Bdate from tb inner join dim_date on datediff('day',dim_date.thedate, tb.dash_date) < 28 and datediff('day',dim_date.thedate, tb.dash_date) >= 0) as B on Adate = Bdate and A.user_id = B.user_id
where B.user_id is NULL
group by Adate
select winback.date, winback.winback, lost.lost
join lost on winback.date = lost.date