BigQuery - Time difference from previous row if customer ID matches

Question

I have an order table which looks roughly like this

consumerID || TransactionDate || Revenue
1          || 2015-01-01      || 55
1          || 2015-02-01      || 65
2          || 2015-01-01      || 10
3          || 2015-03-01      || 20
4          || 2015-01-01      || 25
4          || 2015-01-01      || 45
4          || 2015-03-01      || 55

I would like to add a column that works out the time in months that it took the customer to place the next order so that the data would look like

consumerID || TransactionDate || Revenue || OrderCount || TimeInMonths
1          || 2015-01-01      || 55      || 1          || null
1          || 2015-02-01      || 65      || 2          || 1
2          || 2015-01-01      || 10      || 1          || null
3          || 2015-03-01      || 20      || 1          || null
4          || 2015-01-01      || 25      || 1          || null
4          || 2015-01-01      || 45      || 2          || 0
4          || 2015-03-01      || 55      || 3          || 2

I have figured out how to work out the running count of orders for a customer using the following

ROW_NUMBER() OVER (PARTITION BY o.ConsumerID ORDER BY TransactionDate ASC) OrderNumber,

I would like to do something similar but work out the months difference and i am stumped.

What i want is

If it is the 1st order, or the earliest date the customer is seen this is null. If order number 2 calculate the difference from the 1st, if the 3rd calculate from the 2nd and so on in months.

If it makes it easy I can ensure the data is sorted by consumerId and Transaction date

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-03-07T22:17:25

SELECT ConsumerID, TransactionDate, Revenue, OrderCount,
  DATEDIFF(TransactionDate, prevTransactionDate) AS TimeInDays,
  INTEGER(DATEDIFF(TransactionDate, prevTransactionDate)/30) AS TimeInMonths
FROM (
  SELECT ConsumerID, TransactionDate, Revenue, 
    ROW_NUMBER() OVER(PARTITION BY ConsumerID 
        ORDER BY TransactionDate ASC) OrderCount,
    LAG(TransactionDate) OVER(PARTITION BY ConsumerID 
        ORDER BY TransactionDate ASC) prevTransactionDate
  FROM YourTable 
) ORDER BY ConsumerID, TransactionDate

Calculation in Days is obvious. Calculation in Months requires you to set business logic of how to calculate it from diff in days. Above is sample rule applied.

BigQuery - Time difference from previous row if customer ID matches

1 Answers