Help Desk

Helpdesk Easy Questions

  1. There are three issues that include the words “index” and “Oracle”. Find the call_date for each of them.
SELECT call_date, call_ref
FROM Issue
WHERE detail LIKE '%index%' AND detail LIKE '%Oracle%';

2. Samantha Hall made three calls on 2017-08-14. Show the date and time for each.

SELECT Issue.call_date, Caller.first_name, Caller.last_name
FROM Caller JOIN Issue ON Caller.caller_id = Issue.caller_id
WHERE Issue.call_date LIKE '%2017-08-14%' AND first_name = 'Samantha' AND last_name = 'Hall';

3. There are 500 calls in the system (roughly). Write a query that shows the number that have each status.

SELECT Issue.status, COUNT(*) AS Volume
FROM Issue
GROUP BY status;

4. 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?

SELECT COUNT(*) AS mlcc
FROM Issue JOIN Staff ON Issue.assigned_to = Staff.staff_code
  JOIN Level ON Staff.level_code = Level.level_code
WHERE Level.manager = 'Y'

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.

SELECT Shift.shift_date, Shift.shift_type, Staff.first_name, Staff.last_name
FROM Shift JOIN Staff ON Shift.Manager = Staff.Staff_code
ORDER BY Shift.shift_date;

Helpdesk Medium Questions

6. List the Company name and the number of calls for those companies with more than 18 calls.

SELECT Customer.company_name, COUNT(*) AS cc
FROM Issue JOIN Caller ON Issue.caller_id = Caller.caller_id
  JOIN Customer ON Caller.company_ref = Customer.company_ref
GROUP BY Customer.company_name
HAVING COUNT(*) > 18;

7. Find the callers who have never made a call. Show first name and last name.

SELECT first_name, last_name
FROM Caller
WHERE caller_id NOT IN (
  SELECT Issue.caller_id
  FROM Issue JOIN Caller ON Issue.caller_id = Caller.caller_id);

8. For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5.

SELECT a.company_name, b.first_name, b.last_name, a.nc
FROM
(SELECT Customer.company_name, COUNT(*) AS nc, Customer.contact_id
FROM Customer JOIN Caller ON Customer.company_ref = Caller.company_ref
  JOIN Issue ON Caller.caller_id = Issue.caller_id
GROUP BY Customer.company_name, Customer.contact_id
HAVING COUNT(*) < 5) AS a
JOIN
(SELECT *
FROM Caller) AS b
ON (a.contact_id = b.caller_id);

9. 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’).

SELECT a.shift_date, a.shift_type, COUNT(DISTINCT(role)) AS cw
FROM
(SELECT shift_date, shift_type, manager AS role
FROM Shift
UNION ALL
SELECT shift_date, shift_type, operator AS role
FROM Shift
UNION ALL
SELECT shift_date, shift_type, engineer1 AS role
FROM Shift
UNION ALL
SELECT shift_date, shift_type, engineer2 AS role
FROM Shift) AS a
GROUP BY a.shift_date, a.shift_type;

10. 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.

SELECT Staff.first_name, Staff.last_name, call_date
FROM Issue JOIN Caller ON Issue.caller_id = Caller.caller_id
  JOIN Staff ON Issue.taken_by = Staff.staff_code
WHERE Caller.first_name = 'Harry'
ORDER BY call_date DESC
LIMIT 1;

Or instead of using order by desc, we can use

SELECT Staff.first_name, Staff.last_name, MAX(call_date)
FROM Issue JOIN Caller ON Issue.caller_id = Caller.caller_id
  JOIN Staff ON Issue.taken_by = Staff.staff_code
WHERE Caller.first_name = 'Harry'
GROUP BY Staff.first_name, Staff.last_name
LIMIT 1;

Helpdesk Hard Questions

11. Show the manager and number of calls received for each hour of the day on 2017-08-12

SELECT a.date_hour AS Hr, COUNT(*) AS cc
FROM
(SELECT DATE_FORMAT(call_date, '%Y-%m-%d %H') AS date_hour, DATE_FORMAT(call_date, '%Y-%m-%d') AS date, DATE_FORMAT(call_date, '%H') AS hour
FROM Issue
WHERE DATE_FORMAT(Issue.call_date, '%Y-%m-%d') = '2017-08-12') AS a
JOIN Shift ON a.date = Shift.shift_date
WHERE Shift.shift_type = 'Early' AND a.hour < 14 OR Shift.shift_type = 'Late' AND a.hour >= 14
GROUP BY Shift.manager, a.date_hour
ORDER BY a.date_hour;

12. 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.

See this to calculate percentiles in MySQL: https://stackoverflow.com/a/4741301. Basically add a counter to our table of caller_id and calls for each caller_id sorted in descending order. Then we can select the top 20% of callers by using a WHERE statement on the proportion of top callers.

SELECT ROUND(SUM(b.calls_per_caller / (SELECT COUNT(*) FROM Issue) * 100), 4) AS t20pc
FROM
(SELECT a.*, @counter := @counter + 1 AS counter
FROM
(SELECT @counter := 0) AS initvar,
(SELECT caller_id, COUNT(*) AS calls_per_caller
FROM Issue
GROUP BY caller_id
ORDER BY COUNT(*) DESC) AS a
) AS b
WHERE counter <= (20/100 * @counter);

13. 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.

SELECT Customer.company_name, COUNT(*) AS abna
FROM Issue JOIN Caller ON Issue.caller_id = Caller.caller_id
  JOIN Customer ON Caller.company_ref = Customer.company_ref
WHERE Customer.company_name NOT IN
(SELECT Customer.company_name
FROM Customer JOIN Caller ON Customer.company_ref = Caller.company_ref
  JOIN Issue ON Caller.caller_id = Issue.caller_id
WHERE DATE_FORMAT(Issue.call_date, '%H:%i') BETWEEN '13:55' AND '14:00'
  OR DATE_FORMAT(Issue.call_date, '%H:%i') BETWEEN '19:55' AND '20:00'
)
GROUP BY Customer.company_name
ORDER BY COUNT(*) DESC
LIMIT 1;

14. 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.

SELECT a.company_name, b.caller_count, a.issue_count
FROM
(SELECT Customer.company_name, COUNT(*) AS issue_count
FROM Customer JOIN Caller ON Customer.company_ref = Caller.company_ref
GROUP BY Customer.company_name) AS a
JOIN
(SELECT Customer.company_name, COUNT(DISTINCT(Caller.caller_id)) AS caller_count
FROM Issue JOIN 
Caller ON Issue.caller_id = Caller.caller_id
  JOIN Customer ON Caller.company_ref = Customer.company_ref
WHERE DATE_FORMAT(call_date, '%Y-%m-%d') = '2017-08-13'
GROUP BY Customer.company_name
) AS b
ON a.issue_count = b.caller_count AND a.company_name = b.company_name

15. 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.

Note that you cannot do a SELF JOIN with a.call_date < b.call_date and a.call_ref < b.call_ref because Issue.call_ref is NOT an auto-incrementing integer primary key. You can check yourself using

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Issue' AND COLUMN_NAME = 'call_ref'

and look under EXTRA which does not have auto-increment.

Basic idea is that we need to have a counter that counts consecutive calls within 10 minutes of one another, a copy of call_date offset by 1 so that we can take time differences, and a variable holding the first call in a sequence of consecutive calls.

SELECT a.taken_by, a.first_call, a.call_date AS last_call, a.call_count AS calls
FROM
(SELECT Issue.taken_by, 
       Issue.call_date,
       @counter := CASE WHEN TIMESTAMPDIFF(MINUTE, @current_call, Issue.call_date) <= 10
                          THEN @counter + 1
                        ELSE 1
                   END AS call_count,
       @first_call := CASE WHEN @counter = 1
                             THEN @first_call := call_date
                           ELSE @first_call
                      END AS first_call,
       @current_call := Issue.call_date
FROM Issue,
(SELECT @counter := 0, @first_call := 0, @current_call := 0) AS initvar
ORDER BY Issue.taken_by, Issue.call_date) AS a
ORDER BY a.call_count DESC
LIMIT 1;