Recursive SQL

Self Join

-- Look at all messages sent to one person after a certain day.
-- We want to find all the messages, but not the very first one.
SELECT DISTINCT sn.uuid
FROM public.soon sn
WHERE sn.uuid = 'cabd80ab-8e0d-4883-9fe0-9670ffe97d2c'
  AND sn.date_created > '1996-06-17'

-- Now take away the very first message.
-- This part finds the first one and removes it from the list.
EXCEPT
(
  SELECT sn.uuid
  FROM public.soon sn
  WHERE sn.uuid = 'cabd80ab-8e0d-4883-9fe0-9670ffe97d2c'
    AND sn.date_created > '1996-06-17'

    -- This checks if there are no messages before this one.
    -- If there aren’t, then this is the first message.
    AND NOT EXISTS (
      SELECT 1
      FROM public.soon sub
      WHERE sub.uuid = sn.uuid
        AND sub.date_created > '1996-06-17'
        AND sub.date_created < sn.date_created
    )
);

References

https://dba.stackexchange.com/questions/209291/how-to-optimize-a-keyset-pagination-query-with-ctes-on-a-big-table

https://youtu.be/RtlV1lowdzE?feature=shared

http://www.codedependant.net/2017/04/30/build-json-api-responses-postregres-with-cte/

https://dba.stackexchange.com/questions/97393/query-for-a-table-with-paging-and-filtering-vs-cte-common-table-expression

https://dba.stackexchange.com/questions/230516/slow-left-join-lateral-in-subquery

https://stackoverflow.com/questions/59898931/how-can-i-efficiently-paginate-the-results-of-a-complex-sql-query

https://wiki.postgresql.org/images/4/4f/SNisenbaum_Yello_Becoming_A_SQL_Guru.pdf

https://stackoverflow.com/questions/61160156/get-paginated-rows-and-total-count-in-single-query

https://jaxenter.com/10-sql-tricks-that-you-didnt-think-were-possible-125934.html

https://www.experts-exchange.com/articles/31097/Power-of-the-Recursive-SQL-arrives-in-MySQL-and-MariaDB.html