How to limit the date query range within 100 days

Knowledge Drop

Last tested: Aug 8, 2019
 

Sometimes admins might want to limit the number of days their users can query to save the datawarehouse cost, for example, they want their users can only query no more than 100 days of range. The solution is using sql_always_where with date_start and date_end liquid parameters:

sql_always_where:

(orders_1.created_at<=COALESCE({% date_start orders_1.created_date %}, {% date_end orders_1.created_date %}) AND orders_1.created_at > date_add(COALESCE({% date_start orders_1.created_date %}, {% date_end orders_1.created_date %}), interval -100 day)OR orders_1.created_at>=COALESCE({% date_start orders_1.created_date %}, {% date_end orders_1.created_date %}) AND orders_1.created_at < date_add(COALESCE({% date_start orders_1.created_date %}, {% date_end orders_1.created_date %}), interval 100 day)

);;

For example, when we query "is before 2019-08-01", this is the generated sql:

WHERE ((orders_1.created_at < (TIMESTAMP('2019-08-01')))) AND ((orders_1.created_at<=COALESCE(NULL, (TIMESTAMP('2019-08-01'))) AND orders_1.created_at > date_add(COALESCE(NULL, (TIMESTAMP('2019-08-01'))), interval -100 day) OR orders_1.created_at>=COALESCE(NULL, (TIMESTAMP('2019-08-01'))) AND orders_1.created_at < date_add(COALESCE(NULL, (TIMESTAMP('2019-08-01'))), interval 100 day)

))

This content is subject to limited support.                

Version history
Last update:
‎06-14-2021 06:14 PM
Updated by: