postgresql - Calculate previous order date and status in Postgres -

postgresql - Calculate previous order date and status in Postgres -

i have simple table of orders, , need calculate stats each order. have postgres db fields: order_id (unique), user_id, created_at (date), city, total

i want write query generate, each order_id:

1) created_at date of user's recent order prior current order_id (so if client placed order order_id=200005b on 9/20/14, date of user's recent previous order?)

2) field showing user's "status" based on date, given next cases: -- if user's first order, status="new"; -- if recent previous order date <= 60 days before given/current order, status="active"; -- if recent previous order date > 60 days before given/current order, status="reactivated"

i think there's way write query using nested selects, , maybe self-join, don't know postgresql plenty understand ordering of queries. have been able generate "order_n" field using next query utilize lookup (order_n)-1 find date, stuck 1 time trying utilize in nesting.

select user_id, order_id, created_at, row_number() on (partition user_id order created_at ) order_n order user_id, created_at;

does have ideas?

postgresql date nested vlookup