extract min and max records from a table

User_SPVYV

Hi:

I need to generate output as below from my input table. I need to capture latest and immediate previous device info by subs_key. For latest device identification condition is - pick the records with with Max date and EVENT = 'RECON' by each subs_key and for previous device any event for the same subs_key but the event should occur on the same day or within 30 days from latest transaction.

For example, subs_key 90011 - latest transaction happened on 16th Oct and with Event Recon it's latest IMEI is 352505, then we need to checked the most closest transaction to identify previous device and the lookback period is 30 days form latest transaction.

I've developed a query but it's not showing correct output. Can anyone help please?

Input table:

Output:

thanks!