Search code examples
sqlshiftdbt

SQL divide by shifted value of other field

I have a table that looks like this: enter image description here

Where RETENTION is the division of N_CUSTOMERS by CUSTOMER_WHEN_0 for each PERIOD_NUMBER.

Now I need to get another new field that is the result of dividing each retention value of thar PERIOD_NUMBER by the retetion value of the previous PERIOUD_NUMBER. In the example of the image it's not appreciated, but I have all the consecutive values for PERIOD_NUMBER. When the PERIOD_NUMBER is 0, it doesn't matter that the division by its shifter value is NAN or similar, I will replace it later.

So, summarizing, my desired output is a ner field that shows the result of dividing the RETENTION value of the PERIOD_NUMBER of that row by the RETENTION value of the previous PERIOD_NUMBER. I'm using DBT and in case is useful I have the querys I used to get these fields and I have the code to do that process in Python, but I need to do it in SQL

first purchase of the user, ORDER_MONTH is the date of that purchase, and PERIOD_NUMBER is the date difference in months between COHORT and ORDER_MONTH. N_CUSTOMERS is the number of customers in each PERIOD_NUMBER in each COHORT, and CUSTOMER_WHEN_0 is the number of users in each cohort when the PERIOD_NUMBER is 0. I had to use a window function in order to achive this last field.

Solution

  • Here is an example of how you can get the previous RETENTION using a subquery, assuming that the period numbers are all consecutive:

    SELECT
    N_CUSTOMERS,
    PERIOD_NUMBER,
    CUSTOMER_WHEN_0,
    RETENTION,
    (SELECT st_inner.RETENTION
        FROM sourcetable st_inner
        WHERE st_inner.PERIOD_NUMBER = st_outer.PERIOD_NUMBER - 1
    ) as PREVIOUS_RETENTION
FROM sourcetable st_outer

    I left out the calculation for readability's sake, but I think it should be clear how to do this.