I have troubles creating certain aggregations. I’m using this table in Postgres:
CREATE TABLE public.customer_courier_chat_messages (
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.customer_courier_chat_messages(
sender_app_type, customer_id, from_id, to_id, chat_started_by_message, order_id, order_stage, courier_id, message_sent_time)
VALUES ('Customer IOS',99,99,21,FALSE,555,'PICKING_UP',21,timestamp '9/8/22 8:02'),
('Courier IOS',99,21,99,FALSE,555,'ARRIVING',21,timestamp '9/8/22 8:01'),
('Customer IOS',99,99,21,FALSE,555,'PICKING_UP',21,timestamp '9/8/22 8:00'),
('Courier Android',122,87,122,TRUE,38,'ADDRESS_DELIVERY',87,timestamp '9/8/22 7:55'),
('Customer Android',43,43,75,FALSE,875,'PICKING_UP',75,timestamp '7/8/22 14:55'),
('Courier Android',43,75,43,FALSE,875,'ARRIVING',75,timestamp '7/8/22 14:53'),
('Customer Android',43,43,75,FALSE,875,'PICKING_UP',75,timestamp '7/8/22 14:51'),
('Courier Android',43,75,43,TRUE,875,'ADDRESS_DELIVERY',75,timestamp '7/8/22 14:50'),
('Customer IOS',23,23,21,FALSE,134,'PICKING_UP',21,timestamp '7/8/22 10:02'),
('Courier IOS',23,21,23,FALSE,134,'ARRIVING',21,timestamp '7/8/22 10:01'),
('Customer IOS',23,23,21,FALSE,134,'PICKING_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:
- first_courier_message: Timestamp of the first courier message
- first_customer_message: Timestamp of the first customer message
- num_messages_courier: Number of messages sent by courier
- num_messages_customer: Number of messages sent by customer
- first_message_by: The first message sender (courier or customer)
- conversation_started_at: Timestamp of the first message in the conversation
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 customer_courier_chat_messages 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?
2
Answers
you need to use the window function: first_value() and last_value()
so in your case:
https://www.postgresqltutorial.com/postgresql-window-function/postgresql-first_value-function/
https://www.postgresql.org/docs/current/tutorial-window.html
https://www.postgresql.org/docs/current/functions-window.html
Do you mean something like this?