Difference between revisions of "Help Desk"

From SQLZOO
Jump to: navigation, search
(Hard)
Line 1: Line 1:
 +
==Scenario==
 +
A software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made.
 +
[File:helpdesk.png]
 +
*[[Help Desk]]
 +
*[[Helpdesk Easy Questions]]
 +
*[[Helpdesk Medium Questions]]
 +
*[[Helpdesk Hard Questions]]
 
==Easy==
 
==Easy==
 
<div class='qu'>
 
<div class='qu'>

Revision as of 14:01, 20 October 2017

Scenario

A software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made. [File:helpdesk.png]

Easy

There are three issues that include the words "index" and "Oracle". Find the call_date for each of them

+---------------------+----------+
| call_date           | call_ref |
+---------------------+----------+
| 2017-08-12 16:00:00 |     1308 |
| 2017-08-16 14:54:00 |     1697 |
| 2017-08-16 19:12:00 |     1731 |
+---------------------+----------+

Samantha Hall made three calls on 2017-08-14. Show the date and time for each

+---------------------+------------+-----------+
| call_date           | first_name | last_name |
+---------------------+------------+-----------+
| 2017-08-14 10:10:00 | Samantha   | Hall      |
| 2017-08-14 10:49:00 | Samantha   | Hall      |
| 2017-08-14 18:18:00 | Samantha   | Hall      |
+---------------------+------------+-----------+

There are 500 calls in the system (roughly). Write a query that shows the number that have each status.

+--------+--------+
| status | Volume |
+--------+--------+
| Closed |    486 |
| Open   |     10 |
+--------+--------+

Calls are not normally assigned to a manager but it does happen. How many calls have been assigned to staff who are at Manager Level?

+------+
| mlcc |
+------+
|   51 |
+------+

Show the manager for each shift. Your output should include the shift date and type; also the first and last name of the manager.

+------------+------------+------------+-----------+
| Shift_date | Shift_type | first_name | last_name |
+------------+------------+------------+-----------+
| 2017-08-12 | Early      | Logan      | Butler    |
| 2017-08-12 | Late       | Ava        | Ellis     |
| 2017-08-13 | Early      | Ava        | Ellis     |
| 2017-08-13 | Late       | Ava        | Ellis     |
| 2017-08-14 | Early      | Logan      | Butler    |
| 2017-08-14 | Late       | Logan      | Butler    |
| 2017-08-15 | Early      | Logan      | Butler    |
| 2017-08-15 | Late       | Logan      | Butler    |
| 2017-08-16 | Early      | Logan      | Butler    |
| 2017-08-16 | Late       | Logan      | Butler    |
+------------+------------+------------+-----------+

Medium

List the Company name and the number of calls for those companies with more than 18 calls.

+------------------+----+
| Company_name     | cc |
+------------------+----+
| Gimmick Inc.     | 22 |
| Hamming Services | 19 |
| High and Co.     | 20 |
+------------------+----+

Find the callers who have never made a call. Show first name and last name

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| David      | Jackson   |
| Ethan      | Phillips  |
+------------+-----------+

For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5

+--------------------+------------+-----------+----+
| Company_name       | first_name | last_name | nc |
+--------------------+------------+-----------+----+
| Pitiable Shipping  | Ethan      | McConnell |  4 |
| Rajab Group        | Emily      | Cooper    |  4 |
| Somebody Logistics | Ethan      | Phillips  |  2 |
+--------------------+------------+-----------+----+

For each shift show the number of staff assigned. Beware that some roles may be NULL and that the same person might have been assigned to multiple roles (The roles are 'Manager', 'Operator', 'Engineer1', 'Engineer2').

+------------+------------+----+
| Shift_date | Shift_type | cw |
+------------+------------+----+
| 2017-08-12 | Early      |  4 |
| 2017-08-12 | Late       |  4 |
| 2017-08-13 | Early      |  3 |
| 2017-08-13 | Late       |  2 |
| 2017-08-14 | Early      |  4 |
| 2017-08-14 | Late       |  4 |
| 2017-08-15 | Early      |  4 |
| 2017-08-15 | Late       |  4 |
| 2017-08-16 | Early      |  4 |
| 2017-08-16 | Late       |  4 |
+------------+------------+----+

Caller 'Harry' claims that the operator who took his most recent call was abusive and insulting. Find out who took the call (full name) and when.

+------------+-----------+---------------------+
| first_name | last_name | call_date           |
+------------+-----------+---------------------+
| Emily      | Best      | 2017-08-16 10:25:00 |
+------------+-----------+---------------------+

Hard

Show the manager and number of calls received for each hour of the day on 2017-08-12

+---------+---------------+----+
| Manager | Hr            | cc |
+---------+---------------+----+
| LB1     | 2017-08-12 08 |  6 |
| LB1     | 2017-08-12 09 | 16 |
| LB1     | 2017-08-12 10 | 11 |
| LB1     | 2017-08-12 11 |  6 |
| LB1     | 2017-08-12 12 |  8 |
| LB1     | 2017-08-12 13 |  4 |
| AE1     | 2017-08-12 14 | 12 |
| AE1     | 2017-08-12 15 |  8 |
| AE1     | 2017-08-12 16 |  8 |
| AE1     | 2017-08-12 17 |  7 |
| AE1     | 2017-08-12 19 |  5 |
+---------+---------------+----+

80/20 rule. It is said that 80% of the calls are generated by 20% of the callers. Is this true? What percentage of calls are generated by the most active 20% of callers.

Note - Andrew has not managed to do this in one query - but he believes it is possible.

+---------+
| t20pc   |
+---------+
| 32.2581 |
+---------+

Annoying customers. Customers who call in the last five minutes of a shift are annoying. Find the most active customer who has never been annoying.

+--------------+------+
| Company_name | abna |
+--------------+------+
| High and Co. |   20 |
+--------------+------+

Maximal usage. If every caller registered with a customer makes a call in one day then that customer has "maximal usage" of the service. List the maximal customers for 2017-08-13.

+-------------------+----+----+
| company_name      | sc | cc |
+-------------------+----+----+
| Askew Inc.        |  2 |  2 |
| Bai Services      |  2 |  2 |
| Dasher Services   |  3 |  3 |
| High and Co.      |  5 |  5 |
| Lady Retail       |  4 |  4 |
| Packman Shipping  |  3 |  3 |
| Pitiable Shipping |  2 |  2 |
| Whale Shipping    |  2 |  2 |
+-------------------+----+----+

Consecutive calls occur when an engineer deals with two callers within 10 minutes. Find the longest sequence of consecutive calls – give the name of the operator and the first and last call date in the sequence.