Difference between revisions of "Helpdesk Hard Questions"
Jump to navigation
Jump to search
(Updated model outputs for Q14, Q15.) |
m (Tidy markup) |
||
| Line 9: | 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'> |
| − | |||
| − | <pre class=output> | ||
+---------+---------------+----+ | +---------+---------------+----+ | ||
| Manager | Hr | cc | | | Manager | Hr | cc | | ||
| Line 33: | 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' /> |
| − | + | <pre class='output'> | |
| − | <pre class=output> | ||
+---------+ | +---------+ | ||
| t20pc | | | t20pc | | ||
| Line 48: | 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' /> |
| − | + | <pre class='output'> | |
| − | <pre class=output> | ||
+--------------+------+ | +--------------+------+ | ||
| Company_name | abna | | | Company_name | abna | | ||
| Line 61: | 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'> |
| − | |||
| − | <pre class=output> | ||
+-------------------+--------------+-------------+ | +-------------------+--------------+-------------+ | ||
| company_name | caller_count | issue_count | | | company_name | caller_count | issue_count | | ||
| Line 82: | Line 75: | ||
<div class='qu'> | <div class='qu'> | ||
| − | <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 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'> |
| − | |||
| − | <pre class=output> | ||
+----------+---------------------+---------------------+-------+ | +----------+---------------------+---------------------+-------+ | ||
| taken_by | first_call | last_call | calls | | | taken_by | first_call | last_call | calls | | ||
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 | +----------+---------------------+---------------------+-------+
