Date differcence

[Aria]

Hi everyone, 

I have a payments database which contains a binary varialbe (true / false) called First purchase and an event time for each user who makes a payment. So I want to calculate the time difference between event time payment where first payment is false and the event time payment in the case where first purchase is true.

[Naomi_Johnson]

Hi Aria,

This would need to be done by using a derived table, in order to have the first purchase date against each row to calculate the difference. There are a few ways you could do this using derived tables - I’ve included the simplest solution below.

Make your purchase view a derived table. I would also recommend persisting the derived table with a datagroup, to reduce query time & query costs.

Remove sql_table_name in the purchases view and replace with code similar to this

derived_table: {
  sql: with first_purchases as (
          select user_id, purchase_date as first_purchase_date
          from purchases
          where is_first_purchase = true
       )
       select purchases.*, first_purchases.first_purchase_date
       from purchases
       left join first_purchases on purchases.user_id = first_purchases.user_id
  ;;
  datagroup_trigger: your_datagroup
}

You can then hide the first_purchase date dimension_group and use this for your first purchase to purchase date difference calculation

If you won’t be persisting this derived table, I’d recommend creating this first_purchase_date column in your users table in your database and using this to calculate the difference. 

I hope this helps.

Thanks,

Naomi