<jupyter_start><jupyter_text>Help Desk - Easy ScenarioA software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made.![rel](https://sqlzoo.net/w/images/3/38/Helpdesk.png)<jupyter_code>import getpass
import psycopg2
from sqlalchemy import create_engine
import pandas as pd
import numpy as np
pwd = getpass.getpass()
engine = create_engine(
    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))
pd.set_option('display.max_rows', 100)
shift = pd.read_sql_table('Shift', engine)
staff = pd.read_sql_table('Staff', engine)
issue = pd.read_sql_table('Issue', engine)
shift_type = pd.read_sql_table('Shift_type', engine)
level = pd.read_sql_table('Level', engine)
customer = pd.read_sql_table('Customer', engine)
caller = pd.read_sql_table('Caller', engine)<jupyter_output><empty_output><jupyter_text>1.There are three issues that include the words "index" and "Oracle". Find the call_date for each of them```+---------------------+----------+| call_date           | call_ref |+---------------------+----------+| 2017-08-12 16:00:00 |     1308 || 2017-08-16 14:54:00 |     1697 || 2017-08-16 19:12:00 |     1731 |+---------------------+----------+```<jupyter_code>issue.loc[
    (issue['Detail'].str.contains('index')) & 
    (issue['Detail'].str.contains('Oracle')), 
    ['Call_date', 'Call_ref']]<jupyter_output><empty_output><jupyter_text>2.Samantha Hall made three calls on 2017-08-14. Show the date and time for each```+---------------------+------------+-----------+| call_date           | first_name | last_name |+---------------------+------------+-----------+| 2017-08-14 10:10:00 | Samantha   | Hall      || 2017-08-14 10:49:00 | Samantha   | Hall      || 2017-08-14 18:18:00 | Samantha   | Hall      |+---------------------+------------+-----------+```<jupyter_code>import datetime
(issue[issue['Call_date'].dt.date==datetime.date(2017, 8, 14)]
 .merge(caller[(caller['First_name']=='Samantha') & 
               (caller['Last_name']=='Hall')],
       on='Caller_id')
 [['Call_date', 'First_name', 'Last_name']])<jupyter_output><empty_output><jupyter_text>3.There are 500 calls in the system (roughly). Write a query that shows the number that have each status.```+--------+--------+| status | Volume |+--------+--------+| Closed |    486 || Open   |     10 |+--------+--------+```<jupyter_code>(issue[['Status', 'Caller_id']]
  .groupby('Status')
  .count()
  .reset_index())<jupyter_output><empty_output><jupyter_text>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?```+------+| mlcc |+------+|   51 |+------+```<jupyter_code>pd.DataFrame({'mlcc': (
    issue.merge(staff, left_on='Assigned_to', right_on='Staff_code')
     .merge(level, on='Level_code')
     .query('Manager=="Y"')
     [['Call_ref']].count()
    )})<jupyter_output><empty_output><jupyter_text>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.```+------------+------------+------------+-----------+| Shift_date | Shift_type | first_name | last_name |+------------+------------+------------+-----------+| 2017-08-12 | Early      | Logan      | Butler    || 2017-08-12 | Late       | Ava        | Ellis     || 2017-08-13 | Early      | Ava        | Ellis     || 2017-08-13 | Late       | Ava        | Ellis     || 2017-08-14 | Early      | Logan      | Butler    || 2017-08-14 | Late       | Logan      | Butler    || 2017-08-15 | Early      | Logan      | Butler    || 2017-08-15 | Late       | Logan      | Butler    || 2017-08-16 | Early      | Logan      | Butler    || 2017-08-16 | Late       | Logan      | Butler    |+------------+------------+------------+-----------+```<jupyter_code>(shift.assign(shift_date=shift['Shift_date'].dt.date)
 .merge(staff, left_on='Manager', right_on='Staff_code')
 [['shift_date', 'Shift_type', 'First_name', 'Last_name']]
 .sort_values(['shift_date', 'Shift_type'])
 .drop_duplicates())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.23564131668558455, 'fraction_numerical': 0.048581157775255394, 'mean_word_length': 2.5762987012987013, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '339406', 'license': 'MIT', 'n_tokens_mistral': 1519, 'n_tokens_neox': 1310, 'n_words': 340, 'path': 'Python/12-1 Helpdesk - Easy.ipynb', 'repo_name': 'madlogos/sqlzoo', 'size': '4405'}