Skip to Main Content

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL query help for use case

User_CXOM2Jun 22 2021

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

Comments

Post Details

Added on Jun 22 2021
2 comments
294 views