Brtdku

I have two m:m tables, profile_wordcards and profile_activities. I want to group all wordcards by activity creation dates, where profile_id = 2.

I.e., if an activity 1 was created on 2019-01-19 2:12:05, any wordcard created on or before that date should be grouped by activity 1. If an activity 2 was created on 2019-01-19 2:14:22, all wordcards created on or before that date should be grouped by activity 2, and so on.

Table: profile_activities

activity_id | profile_id | created_at

------------------------------------------

1              2            2019-01-19 2:12:05

2              2            2019-01-19 2:14:22

Table: profile_wordcards

wordcard_id | profile_id | created_at   

-----------------------------------------   

386         2             2019-01-19 2:04:07    >> Everything below: less than activity 1 created at    

385         2             2019-01-19 2:05:19        

263         2             2019-01-19 2:05:19        

234         2             2019-01-19 2:11:49        

175         2             2019-01-19 2:12:02        

201         2             2019-01-19 2:12:02        

226         2             2019-01-19 2:12:04        

409         2             2019-01-19 2:12:05        

361         2             2019-01-19 2:12:05        

359         2             2019-01-19 2:12:25    >> Everything below: less than activity 2 created at    

188         2             2019-01-19 2:12:34        

227         2             2019-01-19 2:12:59        

187         2             2019-01-19 2:13:01        

228         2             2019-01-19 2:13:18        

384         2             2019-01-19 2:13:37        

177         2             2019-01-19 2:14:00        

225         2             2019-01-19 2:14:00        

Desired Output:

wordcard_id | profile_id |  created_at     | activity_id | activity_created_at

--------------------------------------------------------------------------------------- 

-- GROUP 1 (ACTIVITY ID 1, any wordcard.created_at <= 2019-01-19 2:12:05)

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

386             2       2019-01-19 2:04:07     1           2019-01-19 2:12:05

385             2       2019-01-19 2:05:19     1           2019-01-19 2:12:05

263             2       2019-01-19 2:05:19     1           2019-01-19 2:12:05

234             2       2019-01-19 2:11:49     1           2019-01-19 2:12:05

175             2       2019-01-19 2:12:02     1           2019-01-19 2:12:05

201             2       2019-01-19 2:12:02     1           2019-01-19 2:12:05

226             2       2019-01-19 2:12:04     1           2019-01-19 2:12:05

409             2       2019-01-19 2:12:05     1           2019-01-19 2:12:05

361             2       2019-01-19 2:12:05     1           2019-01-19 2:12:05

-- GROUP 2 (ACTIVITY ID 2, any wordcard.created_at <= 2019-01-19 2:14:22 but > 2019-01-19 2:12:05) 

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

359             2       2019-01-19 2:12:25     2           2019-01-19 2:14:22

188             2       2019-01-19 2:12:34     2           2019-01-19 2:14:22

227             2       2019-01-19 2:12:59     2           2019-01-19 2:14:22

187             2       2019-01-19 2:13:01     2           2019-01-19 2:14:22

228             2       2019-01-19 2:13:18     2           2019-01-19 2:14:22

384             2       2019-01-19 2:13:37     2           2019-01-19 2:14:22

177             2       2019-01-19 2:14:00     2           2019-01-19 2:14:22

225             2       2019-01-19 2:14:00     2           2019-01-19 2:14:22

I've tried:

select pwc.wordcard_id, pwc.created_at, pa.activity_id, pa.created_at, pwc.profile_id

from profile_wordcards pwc

left join profile_activities pa on (pa.created_at < pwc.created_at)

where pwc.profile_id = 2

order by activity_id asc

But this is returning a) activity IDs not attached to profile 2 and b) not grouping as expected.

Instead of joining profile_activities directly join a subquery that selects all columns from profile_activities and also the lag()ed created_at. Then you can compare against the created_at of the "previous" activity. For the default value for lag(), that gets taken when no previous activity exists, use '-infinity'. As all timestamps are larger than negative infinity, the comparison against the wordcard created_at will work in that cases too.

SELECT w.wordcard_id,

       w.profile_id,

       w.created_at,

       a.activity_id,

       a.profile_id,

       a.created_at

       FROM (SELECT a.activity_id,

                    a.profile_id,

                    a.created_at,

                    lag(a.created_at,

                        1,

                        '-infinity') OVER (ORDER BY a.created_at) created_at_lag

                    FROM profile_activities a) a

            INNER JOIN profile_wordcards w

                       ON w.profile_id = a.profile_id

                          AND w.created_at > a.created_at_lag

                          AND w.created_at <= a.created_at

       ORDER BY a.activity_id;

db<>fiddle