Days since last purchase postgres (for each purchase)

Question

Just have a standard orders table:

• order_id
• order_date
• customer_id
• order_total

Trying to write a query that generates a column that shows the days since the last purchase, for each customer. If the customer had no prior orders, the value would be zero.

I have tried something like this:

WITH user_data AS (
 SELECT customer_id, order_total, order_date::DATE,
   ROW_NUMBER() OVER (
     PARTITION BY customer_id ORDER BY order_date::DATE DESC
   )
   AS order_count
 FROM transactions
 WHERE STATUS = 100 AND order_total > 0
)
SELECT * FROM user_data WHERE order_count < 3;

Which I could feed into tableau, then use some table calculations to wrangle the data, but I really would like to understand the SQL approach. My approach also only analyzes the most recent 2 transactions, which is a drawback.

Thanks

Answer 1

You should use lag() function:

select *, 
    lag(order_date) over (partition by customer_id order by order_date) 
    as prior_order_date
from transactions
order by order_id

To have the number of days since last order, just subtract the prior order date from the current order date:

select *, 
    order_date- lag(order_date) over (partition by customer_id order by order_date)
    as days_since_last_order
from transactions
order by order_id

The query selects null if there is no prior order. You can use coalesce() to change it to zero.

Answer 2

You indicated that you need to calculate number of days since the last purchase.

..Trying to write a query that generates a column that shows the days since the last purchase

So, basically you need get a difference between now and last purchase date for each client. Query can be the following:

-- test DDL
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE,
  customer_id INTEGER,
  order_total INTEGER
);

INSERT INTO orders(order_date, customer_id, order_total) VALUES
  ('01-01-2015'::DATE,1,2),
  ('01-02-2015'::DATE,1,3),
  ('02-01-2015'::DATE,2,4),
  ('02-02-2015'::DATE,2,5),
  ('03-01-2015'::DATE,3,6),
  ('03-02-2015'::DATE,3,7);

WITH orderdata AS (
    SELECT customer_id,order_total,order_date,
      (now()::DATE - max(order_date) OVER (PARTITION BY customer_id)) as days_since_purchase
    FROM orders
    WHERE order_total > 0
)
SELECT DISTINCT customer_id ,days_since_purchase FROM orderdata ORDER BY customer_id;