Helpdesk Hard Questions
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 | +----------+---------------------+---------------------+-------+