1 Reply Latest reply on Apr 17, 2018 4:03 PM by Patrick A Van Der Hyde

    Redshift custom SQL using WITH AS condition running slowly, if at all

    Scott Laronge

      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

      , user_id

      from log_files

      where event = 'login'

      and date(timestamp)>=getdate()-180

      and right(user_id,2) in (89)

      )

      , winback as (

      select Adate as date, count(A.user_id) as winback

      from

      (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

      from winback

      join lost on winback.date = lost.date