CODEWITHSUNDEEP
postgresql
ZiiMakc
ZiiMakc

Reputation: 36826

select firsts closest to date rows

How for this table x:

pk - uid  | post_id | date - timestamp | likes
1            1             01.01.2020      1
2            1             01.01.2021      5            
3            2             01.01.2020      1
4            4             01.01.2021      3
5            4             01.01.2022      5
6            4             01.01.2023      10

Using two dates (range):

const [start_date, end_date] = ['01.01.2021', '01.01.2022']

Get rows in time range (one for each post_id), closest to start_date

pk - uid  | post_id | date - timestamp | likes
2            1             01.01.2021      5            
4            4             01.01.2021      3

and end_date (separate query):

pk - uid  | post_id | date - timestamp | likes
2            1             01.01.2021      5            
3            2             01.01.2020      1
5            4             01.01.2022      5

I was traying to do it like this, but got duplicate post_id's:

SELECT uid, post_id, like
        FROM x
        WHERE date <= ${end_date}
        GROUP BY uid, post_uid
        ORDER BY date DESC

Bonus question - i can do this using js, with 2 result arrays above, but maybe i can get result that will be difference of end_date rows likes - start date rows:

pk - uid  | post_id | likes
2            1         0            
4            4         2

Upvotes: 0

Views: 41

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

You can use window functions first_value() and last_value() to accomplish this:

select distinct first_value(uid) over w as uid, post_id,
       first_value(likes) over w as oldest_likes,
       last_value(likes) over w as newest_likes,
       last_value(likes) over w - first_value(likes) over w as likes
  from x
 where ddate between '2021-01-01' and '2022-01-01'
window w as (partition by post_id 
                 order by ddate 
          rows between unbounded preceding and unbounded following)
;

 uid | post_id | oldest_likes | newest_likes | likes 
-----+---------+--------------+--------------+-------
   2 |       1 |            5 |            5 |     0
   4 |       4 |            3 |            5 |     2
(2 rows)

Upvotes: 1

Related Questions