Lateral Join

LATERAL JOIN

Data:

                Table "public.orders"
   Column   |            Type             | Modifiers
------------+-----------------------------+-----------
 id         | integer                     | not null
 user_id    | integer                     |
 created_at | timestamp without time zone |


 id | user_id |         created_at
----+---------+----------------------------
  1 |       1 | 2017-06-20 04:35:03.582895
  2 |       2 | 2017-06-20 04:35:07.564973
  3 |       3 | 2017-06-20 04:35:10.986712
  4 |       1 | 2017-06-20 04:58:10.137503
  5 |       3 | 2017-06-20 04:58:17.905277
  6 |       3 | 2017-06-20 04:58:25.289122

Problem: the time of the first order created by each user and what is the ID and time of the next order

Solution:

SELECT user_id, first_order_time, next_order_time, id FROM
  (SELECT user_id, min(created_at) AS first_order_time FROM orders GROUP BY user_id) o1
  LEFT JOIN LATERAL
  (SELECT id, created_at AS next_order_time
   FROM orders
   WHERE user_id = o1.user_id AND created_at > o1.first_order_time
   ORDER BY created_at ASC LIMIT 1)
   o2 ON true;

monadplus/lateral_join.md

LATERAL JOIN