Forum Stats

  • 3,759,471 Users
  • 2,251,548 Discussions
  • 7,870,662 Comments

Discussions

SQL query help for use case

User_CXOM2
User_CXOM2 Member Posts: 1 Green Ribbon

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

Answers