Call Center Dataset for Data Analysis and Reporting

• calls.agent_id →references agents.agent_idmany to one: each Calls row points to one Agents row
• calls.customer_id →references customers.customer_idmany to one: each Calls row points to one Customers row

1. Write a SQL query to find the average call duration per agent, filtering for 'Resolved' calls.

   tables: calls, agents

2. Create a Power BI dashboard showing the distribution of call outcomes by call type.

   tables: calls

3. Calculate the number of calls handled by each customer, ordered by the most frequent.

   tables: calls, customers

4. Identify agents with the highest average sentiment score for 'Inbound' calls.

   tables: calls, agents

Table names match the SQLite file and the SQL script.

SELECT AVG(c.duration_seconds) AS average_duration, a.first_name, a.last_name FROM calls c JOIN agents a ON c.agent_id = a.agent_id WHERE c.call_outcome = 'Resolved' GROUP BY a.agent_id, a.first_name, a.last_name ORDER BY average_duration DESC LIMIT 20;

SELECT call_type, call_outcome, COUNT(*) AS call_count FROM calls GROUP BY call_type, call_outcome ORDER BY call_type, call_count DESC LIMIT 20;

SELECT COUNT(c.call_id) AS total_calls, cust.first_name, cust.last_name FROM calls c JOIN customers cust ON c.customer_id = cust.customer_id GROUP BY cust.customer_id, cust.first_name, cust.last_name ORDER BY total_calls DESC LIMIT 20;

SELECT AVG(c.sentiment_score) AS average_sentiment, a.first_name, a.last_name FROM calls c JOIN agents a ON c.agent_id = a.agent_id WHERE c.call_type = 'Inbound' GROUP BY a.agent_id, a.first_name, a.last_name ORDER BY average_sentiment DESC LIMIT 20;

SELECT agent_id, duration_seconds, RANK() OVER (PARTITION BY agent_id ORDER BY duration_seconds DESC) as duration_rank FROM calls LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join tables based on agent_id and customer_id. Create a PivotTable on the 'calls' sheet to analyze call outcomes by call type.

Power BI

Create a star schema with 'calls' as the fact table and 'agents' and 'customers' as dimension tables. Establish relationships: calls.agent_id -> agents.agent_id, calls.customer_id -> customers.customer_id. DAX measures: Total Calls = COUNT(calls[call_id]), Average Duration = AVERAGE(calls[duration_seconds]).

SQL

Load the SQLite or SQL relational download. Use agent_id and customer_id for joining tables. Example join path: calls JOIN agents ON calls.agent_id = agents.agent_id.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file