Difference between revisions of "Helpdesk Hard Questions"

From SQLZOO
Jump to: navigation, search
(Created page with "*Help Desk *Helpdesk Easy Questions *Helpdesk Medium Questions *Helpdesk Hard Questions ==Hard== <div class='qu'> <p class=imper'>Show the manager and number o...")
 
m (Tidy markup)
 
(4 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
<div class=params>
 +
schema:helpdesk
 +
</div>
 +
<div id=startAt style='display:none'>11</div>
 
*[[Help Desk]]
 
*[[Help Desk]]
 
*[[Helpdesk Easy Questions]]
 
*[[Helpdesk Easy Questions]]
Line 5: Line 9:
 
==Hard==
 
==Hard==
 
<div class='qu'>
 
<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 class=imper'>Show the manager and number of calls received for each hour of the day on 2017-08-12</p>
</p>
+
<source lang='sql' class='def' />
<source lang='sql' class='def'>
+
<pre class='output'>
</source>
 
<pre class=output>
 
 
+---------+---------------+----+
 
+---------+---------------+----+
 
| Manager | Hr            | cc |
 
| Manager | Hr            | cc |
Line 29: Line 31:
  
 
<div class='qu'>
 
<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 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>
</p>
 
 
Note - Andrew has not managed to do this in one query - but he believes it is possible.
 
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>
+
<pre class='output'>
<pre class=output>
 
 
+---------+
 
+---------+
 
| t20pc  |
 
| t20pc  |
Line 44: Line 44:
  
 
<div class='qu'>
 
<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>
+
<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>
+
<pre class='output'>
<pre class=output>
 
 
+--------------+------+
 
+--------------+------+
 
| Company_name | abna |
 
| Company_name | abna |
Line 57: Line 56:
  
 
<div class='qu'>
 
<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 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>
</p>
+
<source lang='sql' class='def' />
<source lang='sql' class='def'>
+
<pre class='output'>
</source>
+
+-------------------+--------------+-------------+
<pre class=output>
+
| company_name      | caller_count | issue_count |
+-------------------+----+----+
+
+-------------------+--------------+-------------+
| company_name      | sc | cc |
+
| Askew Inc.        |           2 |           2 |
+-------------------+----+----+
+
| Bai Services      |           2 |           2 |
| Askew Inc.        | 2 | 2 |
+
| Dasher Services  |           3 |           3 |
| Bai Services      | 2 | 2 |
+
| High and Co.      |           5 |           5 |
| Dasher Services  | 3 | 3 |
+
| Lady Retail      |           4 |           4 |
| High and Co.      | 5 | 5 |
+
| Packman Shipping  |           3 |           3 |
| Lady Retail      | 4 | 4 |
+
| Pitiable Shipping |           2 |           2 |
| Packman Shipping  | 3 | 3 |
+
| Whale Shipping    |           2 |           2 |
| Pitiable Shipping | 2 | 2 |
+
+-------------------+--------------+-------------+
| Whale Shipping    | 2 | 2 |
 
+-------------------+----+----+
 
 
</pre>
 
</pre>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<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 class='imper'>Consecutive calls occur when an operator 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>
</p>
+
<source lang='sql' class='def' />
<source lang='sql' class='def'>
+
<pre class='output'>
</source>
+
+----------+---------------------+---------------------+-------+
<pre class=output>
+
| taken_by | first_call          | last_call          | calls |
 +
+----------+---------------------+---------------------+-------+
 +
| AB1      | 2017-08-14 09:06:00 | 2017-08-14 10:17:00 |    24 |
 +
+----------+---------------------+---------------------+-------+
 
</pre>
 
</pre>
 
</div>
 
</div>

Latest revision as of 14:44, 2 November 2017

schema:helpdesk

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      | caller_count | issue_count |
+-------------------+--------------+-------------+
| 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 operator 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.

+----------+---------------------+---------------------+-------+
| taken_by | first_call          | last_call           | calls |
+----------+---------------------+---------------------+-------+
| AB1      | 2017-08-14 09:06:00 | 2017-08-14 10:17:00 |    24 |
+----------+---------------------+---------------------+-------+