0

Postgres 14

db fiddle updated - Link

EDIT: It seems like I need 1 more row: CREATED AT - specifies when conversation was created.

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

Here, I want to count every first message sent. Can we use minimum of sent_time of every single date? I want the Count.

Returns:

conv_id user_id content sent_time created_time
1 1 1st_msg 01-01-2021 00:01 01-01-2021 00:01
1 1 4th_msg 01-01-2021 00:01 01-01-2021 00:01
1 2 2nd_msg 01-01-2021 00:01 01-01-2021 00:01
1 2 3rd_msg 01-01-2021 00:01 01-01-2021 00:01
2 1 1st_msg 02-01-2021 00:02 01-01-2021 00:01
2 1 4th_msg 02-01-2021 00:02 01-01-2021 00:01
2 2 2nd_msg 02-01-2021 00:02 01-01-2021 00:01
2 2 3rd_msg 02-01-2021 00:02 01-01-2021 00:01
3 2 2nd_msg 03-01-2021 00:03 01-01-2021 00:01
3 2 4th_msg 03-01-2021 00:03 01-01-2021 00:01

Expected Answer:

  1. First message on 1st Day of Jan was - 1st msg - COUNT 1
  2. First message on 2nd Day of Jan was - 1st msg - COUNT 2 . . . n - counts
  • What is n or number of first messages?
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
33a5G7s9t1e3L
  • 119
  • 5
  • 1
    Hi - and welcome to dba.se! You appear to want to count new conversations? Can we use minimum of sent_time of every single date? I want the Count.. No, you can't! The dates are pretty arbitrary per conversation - you could have 5 conversations start on the same day - each with different 1st message times. Also, you could have a long conversation which started on day x and went over into x+1 at 10s after midnight Unless I've misunderstood, is this what you want? If so, I can write it up as an answer. – Vérace Jun 24 '22 at 14:31
  • @Vérace-СлаваУкраїні Thanks for the reply. What if we add the created_at column. Is it then possible to count the first messages? Please see the edited question – 33a5G7s9t1e3L Jun 24 '22 at 14:45
  • 1
    So do you want 3 records in the answer? Please construct a table with your desired result and put it into the question. Notify me when done. – Vérace Jun 24 '22 at 15:40
  • @Vérace-СлаваУкраїні I have updated my requirements more clearly. This below code will only count the number of unique conversations. What I am looking for is to figure out the count of first messages sent in each conversation. 
    SELECT
  COUNT(DISTINCT(conv_id)) AS msg_count
FROM 
  messages;
    – 33a5G7s9t1e3L Jun 24 '22 at 19:16

1 Answers1

1

It sounds like you are looking for the number of conversations started each day:

SELECT date_trunc('day', sent_time) AS the_day, count(*) AS conversations_started
FROM  (
   SELECT DISTINCT ON (conv_id)  sent_time
   FROM   messages
   ORDER  BY conv_id, sent_time
   ) sub
GROUP  BY 1
ORDER  BY 1;  -- optional
the_day conversations_started
2021-01-01 00:00:00 1
2021-01-02 00:00:00 2

db<>fiddle here

Again, performance optimization possible for many rows per conversation. And "the day" is not well defined while data type (and time zone for timestamptz) are not well defined. Like in your previous question:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600