Question1:

A customer support team wants to analyze response times for resolving tickets to identify performance metrics and improve service quality. The goal is to generate a report calculating the average response times for successfully resolved customer support tickets. Resolved tickets have a value in the resolved_at field.

The result should have the following columns average_response_time The average time between created_at and resolved_at, calculated in hours and set to two decimal places, including trailing zeros if necessary (eg, 5.00).

Schema

Name Type Constraint Description
id INT Primary Key Unique identifier for each support ticket
customer_id INT Reference to the customer who created the ticket
created_at VARCHAR(19) Date and time when the ticket was created
resolved_at VARCHAR(19) Date and time when the ticket was resolved 
-- Create Database (Schema)
CREATE DATABASE IF NOT EXISTS customer_support;

-- Use the Database
USE customer_support;

-- Create Table
CREATE TABLE support_tickets (
    id INT PRIMARY KEY,
    customer_id INT,
    created_at VARCHAR(19),
    resolved_at VARCHAR(19)
);

Table (support_tickets)

id customer_id created_at resolved_at
1 1 2023-12-21 05:42:00 2024-01-01 05:42:00
2 2 2023-07-08 14:22:00 NULL
3 3 2023-05-22 08:54:00 2023-06-17 08:54:00 
INSERT INTO support_tickets (id, customer_id, created_at, resolved_at) VALUES
(1, 1, '2023-12-21 05:42:00', '2024-01-01 05:42:00'),
(2, 2, '2023-07-08 14:22:00', NULL),
(3, 3, '2023-05-22 08:54:00', '2023-06-17 08:54:00');

Calculate average response time for resolved tickets

SELECT 
    FORMAT(
        AVG(TIMESTAMPDIFF(SECOND, created_at, resolved_at)) / 3600,
        2
    ) AS average_response_time
FROM support_tickets
WHERE resolved_at IS NOT NULL;

time_difference_seconds: 444.00

Explanation

Question2: