Single filter value, multiple columns - possible?

[adrian12345]

I have 2 tables that I want to join (user, events), both partitioned on date (signup date, action date). This is to create a retention cohort analysis, the tables being joined on action_date between signup and signup+7day

I cannot run this query without both date filters, because the cost of the full table reads would exceed my allotted read query quota.

I want to be able to choose a single date filter in looker and have it propagate to both the partition columns. Is this possible in looker? BQ from what I can tell needs both columns explicitly mentioned or it will do a full table scan which for my use case would be ridiculously expensive.

Looker’s derived tables do not seem suitable as the generated table SQL reads both tables without filters and also creates a copy of the data. I am trying to solve it with a view that I can then add to lookML

Any ideas appreciated

[Dawid]

I think you just need to always offset the joining table of 7 days. Let’s assume you create hidden dimension:

dimension: signup_date_plus_7 {
  hidden: yes
  sql: DATE_ADD(signup_date, INTERVAL + 7 DAY) ;;

and then in the join

sql_on: events.action_date BETWEEN user.signup_date AND user.signup_date_plus_7 ;;

At least this is what comes to my mind right now. Because you can’t insert the actual date you select in the filter (that was my first idea). If you select “Last 5 months” you need to get last 5 months of data of users and 5 months + 7 days of events. If you offset the join, that should work

[jamesnestler]

You might find this useful How To: Smart Partition Key Filtering with Liquid