Selecting Unanswered Support Tickets for Users

In this article, we will explore how to select users who have an unanswered support ticket. We will use two tables: users and support_messages. The support_messages table stores the history of all conversations with a user.

Understanding the Tables

Users Table

The users table contains information about each user, including their ID, name, and phone number.

Support Messages Table

The support_messages table stores the history of all conversations with a user. Each row represents a single message sent or received by the user.

Problem Statement

We want to select users who have an unanswered support ticket, i.e., the last message sent by the user was not answered yet. We also need to include the date of the last two chats (sent by the user) in our results.

Solution Overview

To solve this problem, we will use a combination of subqueries and joins to fetch the required data from both tables.

Subquery 1: Get the Last Chat Date for Each User

We start by finding the maximum date for each user’s last chat. This is done using the following subquery:

SELECT MAX(m.`date`) AS `date` 
FROM `support_messages` m
GROUP BY m.`userId`

This will give us the date of the last chat sent by each user.

Subquery 2: Get the Last Two Chat Dates for Each User

We also need to find the dates of the last two chats sent by each user. We can do this using another subquery:

SELECT MAX(p.`date`) AS preLastChatDate 
FROM `support_messages` p
WHERE p.userId = l.userId AND p.`date` < l.`date`

This will give us the date of the last chat sent by each user, which is not answered yet.

Joining Tables

We then join the users, support_messages tables with these subqueries using inner joins:

SELECT u.`name` AS `name`,
       l.`date` AS `lastChatDate`,
       u.`id` AS `id`,
       u.`phone` AS `phone`,
       (SELECT MAX(p.`date`) FROM `support_messages` p WHERE p.userId = l.userId AND p.`date`<l.`date`) AS preLastChatDate
FROM (
  select
   m.`userId` AS `userId`,
   MAX(m.`date`) AS `date` 
  from
     `support_messages` m
  group by m.`userId`)
l
INNER JOIN `support_messages` lm
ON lm.`userId`=l.`userId`
AND lm.`date`=l.`date`
INNER JOIN `user` u
ON u.id=l.`userId`
WHERE lm.`fromNumber` &lt;&gt; 'support'

This will give us the desired results, including the date of the last two chats sent by each user.

Example Use Case

Suppose we have the following data in our tables:

Users Table:

Support Messages Table:

Running the above query will give us the following results:

As expected, we get the date of John’s last unanswered chat (2022-01-03 16:00:00) in our results.

Last modified on 2025-01-07