4.3. Track Open Tickets by Priority

SELECT priority, COUNT(*) AS open_tickets
FROM support_tickets
WHERE status = 'Open'
GROUP BY priority;

This SQL query aims to analyze open support tickets based on their priority. It starts by selecting two key pieces of information: the 'priority' of each ticket and the count of how many open tickets there are for each priority level. The data is retrieved from the 'support_tickets' table, specifically those tickets that have a status of 'Open'. The 'GROUP BY' clause is crucial here as it organizes the results by 'priority', allowing us to see the number of open tickets categorized under each priority. Essentially, we are summing up tickets that share the same priority level.

Imagine a teacher who wants to classify students' homework submissions by their grades—A, B, C, etc. The teacher can categorize homework that is 'not yet submitted' into different piles based on these grades. Similarly, in this SQL case, the tickets that are still 'Open' are sorted into groups (or piles), relying on their assigned 'priority,' helping to determine which issues need urgent attention.

1. SELECT priority, COUNT(*) AS open_tickets: This part of the query specifies that we want to retrieve the 'priority' attribute and a count of tickets that are currently open.

2. FROM support_tickets: This indicates that our data comes from the 'support_tickets' table, which contains information about various support requests.

3. WHERE status = 'Open': This condition filters our results to include only those tickets that are marked as 'Open.'

4. GROUP BY priority: Finally, this clause organizes our results by the priority value, aggregating results so that we can see how many open tickets exist for each specified priority level.

Let's break down the SQL query step by step:

1. The 'SELECT' clause defines what columns to show in the output. Here, we are interested in the 'priority' and how many open tickets exist at each priority level, hence we use COUNT(*) for counting the tickets.
2. The 'FROM' clause specifies the data source, the 'support_tickets' table. This contains all necessary ticket data.
3. The 'WHERE' clause sets a condition for our query; it only includes tickets that are 'Open,' ensuring we only analyze unresolved issues.
4. Finally, 'GROUP BY' is used to consolidate our results. Instead of listing each individual ticket, it aggregates them into categories based on 'priority,' making it easier to see total counts per priority category.

Consider you work at a restaurant and need to assess how many takeout orders are pending based on their urgency level (high, medium, low). You would check each order for its status (whether it’s still pending) and count how many orders fall into each urgency category. This method parallels the SQL query structure, where we look at a table of orders (support_tickets), filter for pending orders, and group them by urgency level (priority). Doing so helps the restaurant prioritize which orders to complete first.

--