Question

user selected value in query as filter

  • 2 August 2022
  • 0 replies
  • 12 views

I am trying to create a derived table based off the below query on Looker. Except, I want the user to choose the date. used below (2022-07-29) from a range of dates. I tried using templated filters and got the where clause to work, but unsure how to do the days difference calculation between the user selected date and the date from the table. 

 

Should I be using parameters? 

 

SELECT distinct user_id,

max(DATE(ts_created)) as login_date,

DATE_DIFF('2022-07-29',max(DATE(ts_created)),DAY) as Days_Since_Last_Login,
CASE 
WHEN DATE_DIFF('2022-07-29',max(DATE(ts_created)),DAY) <=30 THEN '30 Days'
WHEN DATE_DIFF('2022-07-29',max(DATE(ts_created)),DAY) BETWEEN 31 AND 60 THEN '30 - 60 Days' 
WHEN DATE_DIFF('2022-07-29',max(DATE(ts_created)),DAY) BETWEEN 61 AND 90 THEN '60 - 90 Days' 
WHEN DATE_DIFF('2022-07-29',max(DATE(ts_created)),DAY) >90 THEN '90+ Days'
END AS Activity_Flag FROM Visits 
where DATE(ts_created)<='2022-07-29'
group by user_id
 


0 replies

Be the first to reply!

Reply