This case study focuses on extracting agent performance metrics from Zendesk's data. Your task is to write SQL queries that can accurately gather these metrics.

Zendesk Data Structure

Zendesk primarily organizes its data in multiple tables, but for the purpose of this case study we will focus on the following tables and fields:

$"$ZD$\_$Tickets" Table:

Key Fields: ID$,$ CREATED$\_$AT$,$ UPDATED$\_$AT$,$ ASSIGNEE$\_$ID$,$ STATUS, SATISFACTION$\_$RATING

Description: Contains comprehensive details about customer tickets.

$"$ZD$\_$Users" Table:

Key Fields: ID$,$ NAME, ROLE

Description: Details about users, including agents handling the tickets.

$"$ZD$\_$Ticket$\_$Metrics" Table:

Key Fields: TICKET$\_$ID$,$ REPLY$\_$TIME$\_$IN$\_$MINUTES, FIRST$\_$RESOLUTIONLTIME$\_$IN$\_$MINUTES

Description: Tracks specific metrics related to each ticket, like reply times and resolution times.

You can view some sample data here.

Your Task

Imagine these tables are located in a database labeled HEVO$\_$DATABASE and within the MONGO$\_$MAIN$\_$APP schema.

Create an SQL query to determine the average first reply time and average first resolution time by month for the past $12$ months.

Create an SQL query that provides average satisfaction score $($as calculated by Zendesk$)$ and total tickets solved by agent by month for the last $6$ months.