Difference between revisions of "Helpdesk Hard Questions"
m (engineer -> operator) |
|||
| Line 82: | Line 82: | ||
<div class='qu'> | <div class='qu'> | ||
<p class=imper'>Consecutive calls occur when an | <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'> | ||
Revision as of 13:49, 31 October 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 | 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 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.