customer_courier_chat_messages.xlsx (10.75 KB)I am new to SQL and trying to learn from different requirements on a given data set.
Requirment:
You have the customer_courier_chat_messages table that stores data about individual messages exchanged between customers and couriers via the in-app chat.
You also have access to the orders table where you have an order_id and city_code field.
Your task is to build a query that creates a table (including the DDL CREATE statement)
(customer_courier_conversations) that aggregates individual messages into conversations.
Take into consideration that a conversation is unique per order. The required fields are the
following:
● order_id
● city_code
● 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
● first_responsetime_delay_seconds: Time (in secs) elapsed until the first message was
responded
● last_message_time: Timestamp of the last message sent
● last_message_order_stage: The stage of the order when the last message was sent
Make your query scalable and readable!
Request you to please help me to write a sql query on the above requirment.
Thanks
Faizan