I would like to calculate the time delay between several customer purchases. However, each purchase is saved in an individual row. The data set looks similar to the following:
x
customer | order_id | purchase_date | product | sequencen| | customer1 | 1247857 | 2020-01-30 | ProdA, ProdB | 1 | | customer2 | 4454874 | 2020-02-07 | ProdA | 1 | | customer3 | 3424556 | 2020-02-28 | ProdA | 1 | | customer4 | 5678889 | 2020-03-14 | ProdB | 1 | | customer3 | 5853778 | 2020-03-22 | ProdA, ProdB | 2 | | customer4 | 7578345 | 2020-03-30 | ProdA, ProdB | 2 | | customer2 | 4892978 | 2020-05-10 | ProdA | 2 | | customer5 | 4834789 | 2020-07-05 | ProdA, ProdB | 1 | | customer5 | 9846726 | 2020-07-27 | ProdB | 2 | | customer1 | 1774783 | 2020-12-12 | ProdB | 2 | | Per customer, I would like to end up with a table that calculates the time-difference (in days) between a certain purchase and the purchase that came before. Basically, I would like to know the time delay (latency) between a customers first and second purchase, second and third purchase, and so on. The result should look like the following:
customer | order_id | purchase_date | product | sequencen| | purchase_latency customer1 | 1247857 | 2020-01-30 | ProdA, ProdB | 1 | | customer1 | 1774783 | 2020-12-12 | ProdB | 2 | | 317 customer2 | 4454874 | 2020-02-07 | ProdA | 1 | | customer2 | 4892978 | 2020-05-10 | ProdA | 2 | | 93 customer3 | 3424556 | 2020-02-28 | ProdA | 1 | | customer3 | 5853778 | 2020-03-22 | ProdA, ProdB | 2 | | 23 customer4 | 5678889 | 2020-03-14 | ProdB | 1 | | customer4 | 7578345 | 2020-03-30 | ProdA, ProdB | 2 | | 16 customer5 | 4834789 | 2020-07-05 | ProdA, ProdB | 1 | | customer5 | 9846726 | 2020-07-27 | ProdB | 2 | | 22I am struggling to add the purchase_latency calculation to my current query, as the calculation would require me to do a calculation over rows. Any ideas how I could add this to my current query?:
SELECTorder_idmax(customer) as customer,max(purchase_date) as purchase_date,STRING_AGG(product, ",") as product,,FROM SELECT(od.order_number as order_id,od.customer_email as customer,od.order_date as purchase_datedd.sku as product,ROW_NUMBER() OVER (PARTITION BY od.customer_email ORDER BY od.order_date ) as sequencenFROM orders_data odJOIN detail_data ddON od.order_number = dd.order_numberwhere od.price> 0 ANDod.sku in ("ProdA","ProdB"))GROUP BY order_idAdvertisement
Answer
Did you try row navigation functions like LAG?
WITH finishers AS (SELECT 'Sophia Liu' as name, TIMESTAMP '2016-10-18 2:51:45' as finish_time, 'F30-34' as division UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39' UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34' UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39' UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39' UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39' UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34' UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34' UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29' UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')SELECT name, finish_time, division, LAG(name) OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runnerFROM finishers;+-----------------+-------------+----------+------------------+| name | finish_time | division | preceding_runner |+-----------------+-------------+----------+------------------+| Carly Forte | 03:08:58 | F25-29 | NULL || Sophia Liu | 02:51:45 | F30-34 | NULL || Nikki Leith | 02:59:01 | F30-34 | Sophia Liu || Jen Edwards | 03:06:36 | F30-34 | Nikki Leith || Meghan Lederer | 03:07:41 | F30-34 | Jen Edwards || Lauren Reasoner | 03:10:14 | F30-34 | Meghan Lederer || Lisa Stelzner | 02:54:11 | F35-39 | NULL || Lauren Matthews | 03:01:17 | F35-39 | Lisa Stelzner || Desiree Berry | 03:05:42 | F35-39 | Lauren Matthews || Suzy Slane | 03:06:24 | F35-39 | Desiree Berry |+-----------------+-------------+----------+------------------+