Get the first and last shippment and arrive date for all users

Question

I have table for shipping Company.

The columns are (NationalID, shipmentdate, arraivaldate ..)

The question is: how can I get the first shippment date and arrival date and last shippment date and arrival date for each user

This is THE sample data:

NationalID	shippmetdate	arrivalDate
115	2020-10-08	2021-03-18
115	2023-02-08	1990-01-01
115	2021-08-11	2021-08-20

I use this QUERY but it's not working

select NationalID,
    min(shippmetdate) as first_shippment,
    min(arrivalDate) as arrival_first,
    max(shippmetdate) as last_shippment,
    max(arrivalDate)as arrival_last
from (
    select NationalID, shippmetdate, arrivalDate,
    ROW_NUMBER() over (partition by NationalID order by shippmetdate) as seq1, 
    ROW_NUMBER() over (partition by NationalID order by arrivalDate) as seq2 
    from shippment
) t 
group by NationalID

The output is:

NationalID	first_shippment	arrival_first	last_shippment	arrival_last
115	2020-10-08	1990-01-01	2023-02-08	2021-08-20

BUT I want the output like

NationalID	first_shippment	arrival_first	last_shippment	arrival_last
115	2020-10-08	2021-03-18	2023-02-08	1990-01-01

Actually I want get the row of the first_shippment then I will return the arrival date for same row

I try to use max and min for the date but the output is not matching.

Answer 1

We can get the first and last shipment date with just min and max.

As for the corresponding arrival dates, you are on the right track by enumerating the records of each user, then aggregating. You would need to use conditional aggregation in the outer query; also, the sequences should be adapted: both should order by the shipment date, but in different directions.

So:

select NationalID,
    min(shipmentDate) as firstShipmentDate,
    max(case when seq1 = 1 then arrivalDate  end) as firstShipmentArrivalDate,
    max(shipmentDate) as lastShipmentDate,
    max(case when seq2 = 1 then arrivalDate  end) as lastShipmentArrivalDate
from (
    select s.*,
        row_number() over (partition by NationalID order by shipmentDate     ) as seq1, 
        row_number() over (partition by NationalID order by shipmentDate desc) as seq2 
    from shipment s
) s
group by NationalID

NationalID	firstShipmentDate	firstShipmentArrivalDate	lastShipmentDate	lastShipmentArrivalDate
115	2020-10-08	2021-03-18	2023-02-08	1990-01-01

fiddle