sqlpostgresqlgreatest-n-per-grouppostgresql-9.5aggregate-filter

How to aggregate for first values in group


I have troubles creating certain aggregations. I'm using this table in Postgres:

CREATE TABLE public.courier (
sender_app_type character varying(255),
customer_id integer,
from_id integer,
to_id integer,
chat_started_by_message boolean,
order_id integer,
order_stage character varying(255),
courier_id integer,
message_sent_time timestamp with time zone
);

Some sample rows:

INSERT INTO public.courier(
    sender_app_type, customer_id, from_id, to_id, chat_started_by_message, order_id, order_stage, courier_id, message_sent_time)
VALUES  ('IOS',99,99,21,FALSE,555,'UP',21,timestamp '9/8/22 8:02'),
        ('Courier IOS',99,21,99,FALSE,555,'ARR',21,timestamp '9/8/22 8:01'),
        ('IOS',99,99,21,FALSE,555,'UP',21,timestamp '9/8/22 8:00'),
        ('Android',122,87,122,TRUE,38,'ADDRESS_DELIVERY',87,timestamp '9/8/22 7:55'),
        (‘Android',43,43,75,FALSE,875,'UP',75,timestamp '7/8/22 14:55'),
        ('Android',43,75,43,FALSE,875,'ARR',75,timestamp '7/8/22 14:53'),
        ('Android',43,43,75,FALSE,875,'UP',75,timestamp '7/8/22 14:51'),
        ('Android',43,75,43,TRUE,875,'ADD',75,timestamp '7/8/22 14:50'),
        ('IOS',23,23,21,FALSE,134,'UP',21,timestamp '7/8/22 10:02'),
        ('IOS',23,21,23,FALSE,134,'ARr',21,timestamp '7/8/22 10:01'),
        ('my IOS',23,23,21,FALSE,134,'UP',21,timestamp '7/8/22 10:00');

I need build that aggregates individual messages into conversations. The query result should be used to create a table customer_courier_conversations (take into consideration that a conversation is unique per order). a. The required fields are the following:

This is what I have so far:

SELECT  ccc.order_id, 
    ord.city_code,
    string_agg(ccc.message_sent_time::character varying, ',' order by ccc.courier_id desc) as first_courier_message,
    string_agg(ccc.message_sent_time::character varying, ',' order by ccc.customer_id asc) as first_customer_message,
    count(ccc.courier_id) as num_messages_courier,  
    count(ccc.customer_id) as num_messages_customer,
    string_agg(ccc.sender_app_type,' ' order by )
FROM courier ccc
INNER JOIN "Orders" ord
  ON ccc.order_id = ord.order_id
group by ccc.order_id, ord.city_code;

Am I going in the right direction?
And how do I implement the last two items?


Solution

  • Do you mean something like this?

    WITH first_msg_by(first_msg_by) AS (
      SELECT SPLIT_PART(sender_app_type,' ',1)
      FROM public.customer_courier_chat_messages
      ORDER BY message_sent_time
      LIMIT 1
    )
    SELECT
      MIN  (CASE WHEN SPLIT_PART(sender_app_type,' ',1)='Courier'  THEN message_sent_time END) AS first_courier_msg
    , MIN  (CASE WHEN SPLIT_PART(sender_app_type,' ',1)='Customer' THEN message_sent_time END) AS first_cust_msg
    , COUNT(CASE WHEN SPLIT_PART(sender_app_type,' ',1)='Courier'  THEN message_sent_time END) AS count_courier_msg
    , COUNT(CASE WHEN SPLIT_PART(sender_app_type,' ',1)='Customer' THEN message_sent_time END) AS count_cust_msg
    , MIN(first_msg_by)                                                                        AS first_msg_by
    , MIN(message_sent_time)                                                                   AS conv_started_at
    FROM public.customer_courier_chat_messages CROSS JOIN first_msg_by;
    
    first_courier_msg first_cust_msg count_courier_msg count_cust_msg first_msg_by conv_started_at
    2022-07-08 10:01:00 2022-07-08 10:00:00 5 6 Customer 2022-07-08 10:00:00