SQL query on redshift to get the first and the last value

Question

I have a data set like this.

I need to write a query which gives me the below output

for every SessionID and VisitID, it should sort based on the date_time Column and provide me with the First Category and the Last Category.

I have used the following code

 rank() OVER( PARTITION BY SessionID
            , VisitID

        ORDER by
            date_Time DESC ) as click_rank_last
where click_rank_last = 1

to get the last Category. But what I need is to get the first and the last in a single query with minimum hit to the data base as the data is huge and querying in costly.

Need the most optimum query!

trincot · Accepted Answer · 2019-03-06T14:00:58.097

One way would be:

select distinct
       sessionid,
       visitid,
       first_value(category) over (
            partition by sessionid, visitid
            order by date_time
            rows between unbounded preceding and unbounded following),
       last_value(category) over (
            partition by sessionid, visitid
            order by date_time
            rows between unbounded preceding and unbounded following)
from   tbl

SQL query on redshift to get the first and the last value

1 Answers1