-1

Table messages:

conv_id user_id content sent_time
1 001 1st_msg 01-01-1990 00:00:00
2 002 2nd_msg 02-01-1990 00:00:00

How do we select the first message and the first reply sent in a conversation (conv_id) every day?

Notes:

  1. There can be many users.
  2. A single user can send multiple messages.
  3. This is a dataset of people, and only two people are chatting with each other.
  4. Throughout the day, multiple messages get exchanged.
  5. First message can be defined by the minimum sent time within day 1 of the first user.
  6. First reply can be defined as the minimum sent time within day 1 of the second user.
New contributor
33a5G7s9t1e3L is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
8
  • Please consider following these suggestions.
    – mustaccio
    Jun 23 at 17:33
  • it is a general query. Either of the answer is fine for me. Jun 23 at 17:50
  • "First few messages" or "first message"? Big difference. How is "first" defined exactly? By sent_time? Why id = 1 twice? Please edit your question to clarify. Jun 24 at 0:09
  • @ErwinBrandstetter yes 'sent_time' can be used to define first message. Now that i think about it. id column has no use at all. Can ignore. i have deleted that column Jun 24 at 3:15
  • I assume both rows in your example should have the same conv_id to be part of the same conversation? Jun 24 at 3:20

1 Answer 1

0

There is a truly simple solution with DISTINCT ON:

SELECT DISTINCT ON (date_trunc('day', sent_time), conv_id, user_id)
       *
FROM   tbl
ORDER  BY date_trunc('day', sent_time), conv_id, user_id, sent_time;

db<>fiddle here

See:

An emulated index-skip may be faster. See:

In case of timestamptz, "days" are defined by the timezone setting of your current session, unless defined explicitly. See:

2
  • Thank you it works like a charm. Do you mind telling how do we count the number that this query returns?? Jun 24 at 8:11
  • @33a5G7s9t1e3L: I don't know what "count the number" means. Please start a new question if you have a new question. Jun 24 at 23:13

Your Answer

33a5G7s9t1e3L is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.