8 Telus International SQL Interview Questions (Updated 2024)
At Telus International, SQL is used day-to-day for analyzing customer engagement patterns and optimizing network infrastructure efficiency. Unsurprisingly this is why Telus International asks SQL query questions in interviews for Data Science, Data Engineering and Data Analytics jobs.
As such, to help you ace the Telus International SQL interview, we've collected 8 Telus International SQL interview questions – can you solve them?
8 Telus International SQL Interview Questions
SQL Question 1: Analyze VIP Customers at Telus International
Telus International is a customer service provider company. They value the clients who frequently request for their services. A power user or a VIP client can be defined as a client who has made more than service requests in the past month. Your task is to identify these VIP clients.
Consider we have a table named to hold all the service requests data and a table named to hold all the client details.
Example Input:
| request_id | client_id | request_date |
|---|---|---|
| 9001 | ABC123 | 06/10/2022 |
| 9002 | DEF456 | 06/12/2022 |
| 9003 | ABC123 | 06/15/2022 |
| 9004 | XYZ789 | 06/20/2022 |
| 9005 | ABC123 | 06/23/2022 |
| 9006 | DEF456 | 06/25/2022 |
| 9007 | ABC123 | 06/30/2022 |
| 9008 | XYZ789 | 07/01/2022 |
| 9009 | ABC123 | 07/02/2022 |
| 9010 | ABC123 | 07/05/2022 |
| 9011 | DEF456 | 07/15/2022 |
| 9012 | ABC123 | 07/19/2022 |
| 9013 | ABC123 | 07/22/2022 |
Example Input:
| client_id | name | join_date |
|---|---|---|
| ABC123 | John Doe | 01/05/2022 |
| DEF456 | Jane Smith | 02/15/2022 |
| XYZ789 | Bob Johnson | 04/12/2022 |
Answer:
In the query we are selecting the client's Id and name from the clients table and counting the number of requests per client from the service_requests table for the past month. We then filter out the clients who have made more than 10 service requests in that interval. This will give us a list of all the VIP clients.
To solve a related customer analytics question on DataLemur's free online SQL coding environment, try this recently asked Microsoft SQL interview question:
SQL Question 2: Top Engaged Customers
Assume that Telus International offers various digital products to its customers and you are tasked to analyze customer engagement for each product.
We have a table with columns:
- : The unique identifier for the customer.
- : The unique identifier for the product.
- : The date-time when the customer viewed the product.
Now, please write a SQL query to identify the top 3 most engaged customers for each product in the June 2022. Ranking should be based on the number of times the customer has viewed the product. In case of a tie, the customer with the lower 'user_id' should be ranked higher.
Example Input:
| user_id | view_date | product_id |
|---|---|---|
| 123 | 06/08/2022 00:00:00 | 50001 |
| 265 | 06/10/2022 00:00:00 | 69852 |
| 123 | 06/18/2022 00:00:00 | 50001 |
| 192 | 07/26/2022 00:00:00 | 69852 |
| 981 | 07/05/2022 00:00:00 | 69852 |
| 123 | 06/20/2022 00:00:00 | 50001 |
| 362 | 06/10/2022 00:00:00 | 50001 |
| 362 | 06/11/2022 00:00:00 | 50001 |
| 192 | 06/30/2022 00:00:00 | 69852 |
| 265 | 06/20/2022 00:00:00 | 50001 |
Answer:
As part of this query, we first create a CTE (common table expression) which aggregates the view counts for each customer for each product for the month of June, 2022. We also calculate the ranking based on the view counts and user_id (in case of tie). Finally, from the CTE, we filter out customers with ranking more than 3 to get top 3 most engaged customers for each product for that month.
To practice a similar window function interview problem which uses RANK() on DataLemur's free interactive SQL code editor, try this Amazon SQL Interview Question:
SQL Question 3: When would you use the constraint?
A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.
Say for example you had sales analytics data from Telus International's CRM (customer-relationship management) tool.
The FOREIGN KEY constraint ensures that the data in the field of the "opportunities" table is valid, and prevents the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and can be used to ensure that data is not deleted from the accounts table if there are still references to it in the opportunities` table.
SQL Question 4: Analyzing Customer Calls
A database is to be designed that will store details of customer calls handled by the customer service department of Telus International. The database should keep track of information related to the timing, duration, and nature of each customer call as well as details about the customer making the call and the agent who handles it. The data will be used to evaluate customer service performance and identify areas for improvement.
You are given two tables - and .
The table should have the following schema:
Example Input:
| customer_id | first_name | last_name | sign_up_date |
|---|---|---|---|
| 123 | John | Doe | 03/01/2021 |
| 456 | Jane | Doe | 05/10/2021 |
| 789 | Jim | Beam | 09/25/2021 |
The table should have the following schema:
Example Input:
| call_id | customer_id | agent_id | call_date | call_duration(minutes) |
|---|---|---|---|---|
| 1 | 123 | 1 | 06/10/2022 | 10 |
| 2 | 456 | 2 | 06/15/2022 | 15 |
| 3 | 789 | 3 | 06/15/2022 | 20 |
Assuming that the timings and duration of the calls are recorded in minutes past current hour, write an SQL query that will find the average duration of calls handled by each agent on a given day.
Answer:
The above query groups the records by agent and date, then calculates the average duration the agent spent on calls for each date. This helps to understand work distribution and efficiency of the agents.
SQL Question 5: Can you describe the different types of joins in SQL?
In SQL, a join is used to combine rows from different tables based on a shared key or set of keys, resulting in a single merged table of data.
There are four distinct types of JOINs: , , , and .
(INNER) JOIN: Retrieves records that have matching values in both tables involved in the join.
LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.
SQL Question 6: Average Service Rating per Account Manager
In Telus International, every customer is assigned to an Account Manager. We want to analyze the effectiveness of each Account Manager in terms of the service ratings provided by their customers. Write a SQL query to calculate the average service rating for each Account Manager, for customers they manage.
Example Input:
| customer_id | name | account_manager_id |
|---|---|---|
| 101 | John Doe | 201 |
| 102 | Jane Smith | 202 |
| 103 | Mike Davis | 201 |
| 104 | Emma Watson | 203 |
| 105 | Tom Hardy | 202 |
Example Input:
| rating_id | customer_id | rating | date |
|---|---|---|---|
| 301 | 101 | 4 | 2022-07-01 00:00:00 |
| 302 | 102 | 3 | 2022-05-20 00:00:00 |
| 303 | 103 | 5 | 2022-06-18 00:00:00 |
| 304 | 104 | 2 | 2022-07-26 00:00:00 |
| 305 | 105 | 4 | 2022-08-15 00:00:00 |
Answer:
This query works by joining the table and the table on . It then groups the result by and calculates the average for each group.
Since joins come up routinely during SQL interviews, try this Spotify JOIN SQL question:
SQL Question 7: In database normalization, what's the distinction between 1NF, 2NF, and 3NF?
Normal forms are guidelines that are used to help design a relational database in a way that minimizes redundancy and ensures the integrity of the data. The 3 most commonly use normal forms are the 1st, 2nd, and 3rd normal forms. Here's a brief explanation of each:
-
1st Normal Form (1NF) is all about keeping it simple - each column should only have one value and there should be no repeating groups of data.
-
2nd Normal Form (2NF) is about organization - your database should already be in 1NF and all the non-key columns should depend on the primary key. This means that each non-key column should be completely dependent on the entire primary key, not just part of it.
-
3rd Normal Form (3NF) is about independence - if your database is already in 2NF, then all the non-key columns should not depend on each other. They should be self-sufficient and not rely on other non-key columns.
SQL Question 8: Calculating Monthly Average Call Durations
As a company that provides digital solutions and customer experience services, Telus International will have a lot of call data. An important metric for such companies can be the average duration of the calls each month.
Below is a sample input for the table:
Example Input:
| call_id | agent_id | start_time | end_time |
|---|---|---|---|
| 001 | 345 | 2022-04-01 08:25:00 | 2022-04-01 08:45:00 |
| 002 | 291 | 2022-04-01 11:57:00 | 2022-04-01 12:10:00 |
| 003 | 345 | 2022-04-02 14:15:00 | 2022-04-02 14:26:00 |
| 004 | 345 | 2022-05-01 13:20:00 | 2022-05-01 13:45:00 |
| 005 | 291 | 2022-05-02 08:30:00 | 2022-05-02 09:05:00 |
Your task will be to write a SQL query that calculates the average duration of calls in minutes for each month.
Example Output:
| month | year | avg_duration |
|---|---|---|
| 4 | 2022 | 19.33 |
| 5 | 2022 | 30.00 |
Answer:
Here's a PostgreSQL query to solve this problem:
This query functions by first extracting the month and year from the column. It then calculates the duration of each call by subtracting the from the and changing the result from seconds (what PostgreSQL's EXTRACT function leaves us with by default) to minutes. After this, the query calculates the average duration for each month and year. The ORDER BY clause ensures that the results are displayed in chronological order.
Telus International SQL Interview Tips
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Telus International SQL interview is to solve as many practice SQL interview questions as you can!
In addition to solving the above Telus International SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups.
Each problem on DataLemur has hints to guide you, fully explained answers along with a discussion board to see how others solved it and crucially, there is an online SQL code editor so you can instantly run your SQL query and have it executed.
To prep for the Telus International SQL interview it is also a great idea to practice SQL questions from other tech companies like:
However, if your SQL foundations are weak, forget about diving straight into solving questions – strengthen your SQL foundations with this interactive SQL tutorial.
This tutorial covers topics including filtering groups with HAVING and filtering on multiple conditions using AND/OR/NOT – both of which show up routinely during SQL interviews at Telus International.
Telus International Data Science Interview Tips
What Do Telus International Data Science Interviews Cover?
In addition to SQL query questions, the other types of problems tested in the Telus International Data Science Interview are:
- Statistics Interview Questions
- Python Pandas or R Coding Questions
- Open-Ended Data Case Studies
- Machine Learning and Predictive Modeling Questions
- Resume-Based Behavioral Questions
How To Prepare for Telus International Data Science Interviews?
To prepare for Telus International Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from Google, Microsoft & tech startups
- a crash course covering Python, SQL & ML
- over 900+ 5-star reviews on Amazon