How can I write this SQL query to display assets?-CodePudding

I have a table calls assets with the following schema

CREATE TABLE assets
(
   id int,
   location_id int,
   updated_at timestamp
)

An asset could be assigned to different locations by time, but only one location at a time, this table track all the assets and all the location changes. such as

(1,1,2022-08-01)
(1,2,2022-09-01)
(1,3,2022-10-01)
(2,1,2022-01-01)
(3,2,2022-10-05)
(4,1,2022-01-01)
(4,2,2022-02-01)

I need a query for the following results: return assets which has newly deployed within a month or relocated within a month. For the above data set, it should return:

(1)--Asset 1 relocated within a month
(3)--Asset newly deployed within a month.

How can I write this query?

CodePudding user response：

I think this should get you started down a path at least but I pulled all the rows that occurred in the last month and then look to see their last update time was. You can adjust message/formatting as needed.

You can also look at Postgres window functions, like lag or lead if you need to have a deeper look into the past history.

select id, location_id, updated_at, 
(select max(updated_at) from assets a2 where a2.id = a.id and a2.updated_at < a.updated_at) as last_update_at
from assets a
where a.updated_at between current_timestamp - interval '1 month' and current_timestamp;