Difference between revisions of "Help Desk"

From SQLZOO
Jump to: navigation, search
(Hard)
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Easy==
+
==Scenario==
<div class='qu'>
+
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.
<p class=imper'>There are three issues that include the words "index" and "Oracle". Find the call_date for each of them</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+---------------------+----------+
 
| 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 |
 
+---------------------+----------+
 
</pre>
 
</div>
 
  
<div class='qu'>
+
[[File:helpdesk.png]]
<p class=imper'>Samantha Hall made three calls on 2017-08-14. Show the date and time for each
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+---------------------+------------+-----------+
 
| 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      |
 
+---------------------+------------+-----------+
 
</pre>
 
</div>
 
  
<div class='qu'>
+
*[[Help Desk]]
<p class=imper'>There are 500 calls in the system (roughly). Write a query that shows the number that have each status.
+
*[[Helpdesk Easy Questions]]
</p>
+
*[[Helpdesk Medium Questions]]
<source lang='sql' class='def'>
+
*[[Helpdesk Hard Questions]]
</source>
 
<pre class=output>
 
+--------+--------+
 
| status | Volume |
 
+--------+--------+
 
| Closed |    486 |
 
| Open  |    10 |
 
+--------+--------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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?
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------+
 
| mlcc |
 
+------+
 
|  51 |
 
+------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>Show the manager for each shift. Your output should include the shift date and type; also the first and last name of the manager.</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------------+------------+------------+-----------+
 
| 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    |
 
+------------+------------+------------+-----------+
 
</pre>
 
</div>
 
 
 
==Medium==
 
<div class='qu'>
 
<p class=imper'>List the Company name and the number of calls for those companies with more than 18 calls.
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------------------+----+
 
| Company_name    | cc |
 
+------------------+----+
 
| Gimmick Inc.    | 22 |
 
| Hamming Services | 19 |
 
| High and Co.    | 20 |
 
+------------------+----+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>Find  the callers who have never made a call. Show first name and last name</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------------+-----------+
 
| first_name | last_name |
 
+------------+-----------+
 
| David      | Jackson  |
 
| Ethan      | Phillips  |
 
+------------+-----------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+--------------------+------------+-----------+----+
 
| Company_name      | first_name | last_name | nc |
 
+--------------------+------------+-----------+----+
 
| Pitiable Shipping  | Ethan      | McConnell |  4 |
 
| Rajab Group        | Emily      | Cooper    |  4 |
 
| Somebody Logistics | Ethan      | Phillips  |  2 |
 
+--------------------+------------+-----------+----+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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').
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------------+------------+----+
 
| 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 |
 
+------------+------------+----+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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.</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------------+-----------+---------------------+
 
| first_name | last_name | call_date          |
 
+------------+-----------+---------------------+
 
| Emily      | Best      | 2017-08-16 10:25:00 |
 
+------------+-----------+---------------------+
 
</pre>
 
</div>
 
 
 
==Hard==
 
<div class='qu'>
 
<p class=imper'>Show the manager and number of calls received for each hour of the day on 2017-08-12
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+---------+---------------+----+
 
| 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 |
 
+---------+---------------+----+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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.
 
</p>
 
Note - Andrew has not managed to do this in one query - but he believes it is possible.
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+---------+
 
| t20pc  |
 
+---------+
 
| 32.2581 |
 
+---------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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.</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+--------------+------+
 
| Company_name | abna |
 
+--------------+------+
 
| High and Co. |  20 |
 
+--------------+------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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.
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+-------------------+----+----+
 
| 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 |
 
+-------------------+----+----+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>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.
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 

Revision as of 14:04, 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