Difference between revisions of "Help Desk"

From SQLZOO
Jump to navigation Jump to search
(Created page with "<div class='qu'> <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'> </...")
 
Line 1: Line 1:
 +
==Easy==
 
<div class='qu'>
 
<div class='qu'>
 
<p class=imper'>There are three issues that include the words "index" and "Oracle". Find the call_date for each of them</p>
 
<p class=imper'>There are three issues that include the words "index" and "Oracle". Find the call_date for each of them</p>
Line 60: Line 61:
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'>--5 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>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
Line 81: Line 82:
 
</div>
 
</div>
  
 +
==Medium==
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<pre class=output>
 +
+------------------+----+
 +
| Company_name    | cc |
 +
+------------------+----+
 +
| Gimmick Inc.    | 22 |
 +
| Hamming Services | 19 |
 +
| High and Co.    | 20 |
 +
+------------------+----+
 
</pre>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<pre class=output>
 +
+------------+-----------+
 +
| first_name | last_name |
 +
+------------+-----------+
 +
| David      | Jackson  |
 +
| Ethan      | Phillips  |
 +
+------------+-----------+
 
</pre>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<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>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<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>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<pre class=output>
 +
+------------+-----------+---------------------+
 +
| first_name | last_name | call_date          |
 +
+------------+-----------+---------------------+
 +
| Emily      | Best      | 2017-08-16 10:25:00 |
 +
+------------+-----------+---------------------+
 
</pre>
 
</pre>
 
</div>
 
</div>
  
 +
==Hard==
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<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>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<pre class=output>
 +
+------+
 +
| tpc  |
 +
+------+
 +
| 29.2 |
 +
+------+
 
</pre>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
 
<pre class=output>
 
<pre class=output>
 +
+--------------+------+
 +
| Company_name | abna |
 +
+--------------+------+
 +
| High and Co. |  20 |
 +
+--------------+------+
 
</pre>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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 lang='sql' class='def'>
 
</source>
 
</source>
Line 157: Line 231:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
<pre class=output>
+
<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>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper'></p>
+
<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.
<source lang='sql' class='def'>
+
</p>
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>

Revision as of 13:02, 10 May 2017

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.

+------+
| tpc  |
+------+
| 29.2 |
+------+

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.