0

I am trying to form a query for returning, reactivated and WAU as defined below:

  • Returning WAU - active last week
  • WAU - not active last week, but active within last 30 days
  • Reactivated WAU – not seen in 30+ days

I have table for past 60 days containing cust_id, login date but cant lag function to work (Teradata ODBC connection). I keep getting this error:

[3706] Syntax error: Data Type "logindate" does not match a Defined Type name. My format is: select .... lag(logindate, 1) over (partition by cust_id order by 1 asc) as lag_ind from ( ....

Please help for the 3 cases above.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
analytics_123
  • 3
  • 1

1 Answers1

0

You can aggregate to get the expected answer:

select cust_id,
   case
     when max(logindate) > current_date - 7  -- active last week
       then 'Returning WAU' 
     when max(logindate) > current_date - 30 -- not active last week, but active within last 30 days
       then 'WAU'
     else 'Reactivated WAU'                  –- not seen in 30+ days
   end
from tab
group by 1

Regarding the issue with LAG, this has been introduced in 16.10, before you have to rewrite:

lag(logindate, 1)
over (partition by cust_id
      order by col asc) as lag_ind

max(logindate)
over (partition by cust_id
      order by col asc
      rows between 1 preceding and 1 preceding) as lag_ind

Hint: never use ORDER BY 1 in an OLAP function, here it's the literal value one and not the first column.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks @dnoeth for the help. This is not helping for reporting on weekly basis ... basically I have expanded my table to past 3 months and trying to produce a report as week # | Returning WAU | WAU | Reactivated WAU July 04 (ending Saturday) | xxxx | xxx | xxxx – analytics_123 Jul 10 '20 at 18:22