11 TD Synnex SQL Interview Questions (Updated 2024)
At TD Synnex, SQL is used for extracting and managing customer data, allowing them to track purchasing patterns and preferences across various product lines. It is also used to analyze IT distribution trends to identify which technologies are in high demand and how they vary by region, which is why TD Synnex often tests SQL problems during interviews for Data Science and Data Engineering positions.
Thus, to help you study, here's 11 TD Synnex SQL interview questions – able to answer them all?
11 TD Synnex SQL Interview Questions
SQL Question 1: Identify Power Users in TD Synnex
TD Synnex, a leading distributor of IT products and services, wishes to categorize their power users. A power user is defined as a user who has made 20 or more purchases within a single month.
Given a table, the task is to write a SQL query that outputs the user_id and the month of the users who have made more than 20 purchases in that month.
Example Input:
| purchase_id | user_id | purchase_date | product_id |
|---|---|---|---|
| 1 | 310 | 06/02/2022 | 501 |
| 2 | 412 | 06/08/2022 | 502 |
| 3 | 310 | 06/14/2022 | 501 |
| 4 | 412 | 06/19/2022 | 501 |
| 5 | 310 | 06/21/2022 | 503 |
| ... | ... | ... | ... |
| 45 | 310 | 06/28/2022 | 506 |
| 46 | 412 | 06/29/2022 | 501 |
| 47 | 310 | 06/30/2022 | 502 |
Answer:
This query first groups the data by both the of the purchase date and , and counts the number of rows in each group. The clause then filters out the groups where the count of purchases is less than 20. The result is the list of s who have made more than 20 purchases in a single month.
To practice a super-customer analysis question on DataLemur's free online SQL code editor, try this Microsoft Azure Cloud SQL Interview Question:
Dive into TDSynnex's Destination AI and learn about their cutting-edge solutions that are transforming the data and IoT landscape! Understanding their approach to technology can provide valuable insights into how they are shaping the future of connectivity and data management.
SQL Question 2: Top Three Salaries
Given a table of TD Synnex employee salary information, write a SQL query to find the top 3 highest paid employees within each department.
TD Synnex Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Try this interview question interactively on DataLemur:
Answer:
We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.
If the code above is confusing, you can find a step-by-step solution here: Top 3 Department Salaries.
SQL Question 3: Can you describe the difference between a unique and a non-unique index?
Some similarities between unique and non-unique indexes include:
- Both indexes improve the performance of SQL queries by providing a faster way to lookup the desired data.
- Both indexes use an additional data which requires more storage space which impacts write performance.
- Both indexes can be created on one or more columns of a table
Some differences between unique and non-unique indexes include:
- A unique index enforces the uniqueness of the indexed columns, meaning that no duplicate values are allowed in the indexed columns. A non-unique index allows duplicate values in the indexed columns.
- A unique index can be used to enforce the primary key of a table, but a non-unique index cannot.
- A unique index can have a maximum of one NULL value in the indexed columns, but a non-unique index can have multiple NULLs
SQL Question 4: Average Sales per Product by Month
You are given a dataset containing sales data from TD Synnex. Each row in the dataset represents an individual sale, with the following columns:
- : An ID number uniquely identifying each sale (integer).
- : An ID number uniquely identifying each product (integer).
- : The date and time when the sale occurred (timestamp).
- : The quantity of the product that was sold (integer).
- : The price per unit of the product at the time of the sale (double).
Write a SQL query that calculates the total sales (total quantity of products sold) per product per month.
Example Input:
| sale_id | product_id | sale_date | quantity | price_per_unit |
|---|---|---|---|---|
| 10 | 1 | 2022-06-08 00:00:00 | 5 | 15.0 |
| 20 | 2 | 2022-06-10 00:00:00 | 3 | 20.0 |
| 30 | 1 | 2022-06-18 00:00:00 | 2 | 15.0 |
| 40 | 2 | 2022-07-26 00:00:00 | 1 | 20.0 |
| 50 | 1 | 2022-07-05 00:00:00 | 6 | 15.0 |
Example Output:
| month | product_id | total_sales |
|---|---|---|
| 6 | 1 | 7 |
| 6 | 2 | 3 |
| 7 | 1 | 6 |
| 7 | 2 | 1 |
Answer:
This SQL script uses PostgreSQL's window function to partition data by and , then it sums the quantity for each partition (i.e., for each in each ) to calculate the total sales.
For more window function practice, try this Uber SQL Interview Question within DataLemur's interactive coding environment:
SQL Question 5: What do the / operators do, and can you give an example?
The / operator is used to return all rows from the first statement that are not returned by the second statement.
Note that is available in PostgreSQL and SQL Server, while is available in MySQL and Oracle (but don't stress about knowing which DBMS supports what exact commands since the interviewers at TD Synnex should be lenient!).
Here's a PostgreSQL example of using to find all of TD Synnex's Facebook video ads with more than 10k views that aren't also being run on YouTube:
If you want to retain duplicates, you can use the operator instead of . The operator will return all rows, including duplicates.
SQL Question 6: Filter Customer Records Based on Business Conditions
TD Synnex, a tech solutions distributor, maintains a database of their customer records. Your task is to write an SQL query to filter down this customer records database. The conditions you need to filter on are:
- Customers located in Toronto (='Toronto')
- Their business type is 'IT & Services' (='IT & Services')
- Their last purchase was made within 30 days from today ( <= NOW() - INTERVAL '30 days')
- They have a 'High' credit rating (='High')
You'll be working with the table, which is set up as follows:
Example Input:
| customer_id | name | city | business_type | last_purchase_date | credit_rating |
|---|---|---|---|---|---|
| 9142 | Tech Kompany | Toronto | IT & Services | 2022-10-05 | High |
| 4875 | Generic Group | Montreal | Retail | 2022-11-26 | Medium |
| 2854 | Innovation Inc | Toronto | IT & Services | 2022-11-29 | High |
| 5876 | Futuristic Firm | Toronto | Shipping | 2022-11-25 | Low |
| 4798 | Solutions Corp | Toronto | IT & Services | 2022-08-07 | High |
Answer:
This query filters down the table based on the mentioned conditions. It uses to apply the main filter, and to combine the different conditions. The function with an interval of '30 days' is used to find records where the is within the past 30 days.
SQL Question 7: What does the SQL keyword do?
The clause in SQL allows you to select records that are unique, eliminating duplicates.
For a tangible example, say you had a table of TD Synnex employees:
| first_name | job_title |
|---|---|
| Akash | Data Analyst |
| Brittany | Data Scientist |
| Carlos | Data Engineer |
| Diego | Data Engineer |
| Eva | Data Analyst |
If you were doing an HR Analytics project and you wanted to get all the unique job titles that currently worked at the company, you would write the following SQL query:
The output would give you 3 distinct job titles at TD Synnex:
| job_title |
|---|
| Data Analyst |
| Data Scientist |
| Data Engineer |
SQL Question 8: Average Sales by Product Category
As an analyst for TD Synnex, a major IT distributor, you are asked to determine the average sales per category for the past year.
To respond to this request, you will need to use data from two tables:
The table, which contains the sale details such as the , , , and the :
Example Input:
| sale_id | product_id | sale_date | amount |
|---|---|---|---|
| 6171 | 1001 | 06/08/2021 | 500 |
| 7802 | 1002 | 06/10/2021 | 200 |
| 5293 | 1001 | 06/18/2021 | 400 |
| 6352 | 1002 | 07/26/2021 | 300 |
| 4517 | 1001 | 07/05/2021 | 600 |
The table, which contains the product details such as the and :
Example Input:
| product_id | category |
|---|---|
| 1001 | Laptop |
| 1002 | Desktop |
Using these two tables, write a SQL query to calculate the average sales per category.
Answer:
This query calculates the average sales () for each product category (). The tables and are joined over the , and the query only considers the sales data from the past year. The result is grouped by product category.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating highest sales by category or this Wayfair Y-on-Y Growth Rate Question which is similar for analysis of sales data over time.
SQL Question 9: Calculate the Click-Through-Rate for TD Synnex ads
TD Synnex, a large IT distribution company, uses digital ads to attract potential customers. You have been asked to analyze the effectiveness of these ads. In particular, your task is to calculate the Click-Through-Rate (CTR) for the digital ads. You're given two tables: and . The table contains information about each displayed ad, and the table contains information about each ad that was clicked by a user.
Example Input:
| ad_id | display_date | product_id |
|---|---|---|
| 1001 | 06/01/2021 00:00:00 | 50250 |
| 1002 | 06/07/2021 00:00:00 | 65500 |
| 1003 | 06/14/2021 00:00:00 | 50250 |
| 1004 | 06/20/2021 00:00:00 | 65500 |
| 1005 | 06/27/2021 00:00:00 | 50250 |
Example Input:
| click_id | click_date | ad_id |
|---|---|---|
| 2001 | 06/01/2021 02:00:00 | 1001 |
| 2002 | 06/07/2021 01:00:00 | 1002 |
| 2003 | 06/14/2021 05:00:00 | 1001 |
| 2004 | 06/20/2021 09:00:00 | 1004 |
| 2005 | 06/28/2021 07:00:00 | 1005 |
Count the total number of times each ad was displayed and clicked.
Answer:
This PostgreSQL query joins and on as it's the common attribute in both tables that represents the digital ads. gives the total number of times each ad was displayed and gives the total number of times each ad was clicked. The Click-Through-Rate (CTR) is then calculated by dividing the number of clicks by the number of displays and multiplying by 100 to express it as a percentage.
To solve a similar problem on DataLemur's free interactive SQL code editor, solve this Facebook SQL Interview question:
SQL Question 10: Can you explain the distinction between a clustered and a non-clustered index?
A clustered index is an index where the order of the rows in the database corresponds to the order of the rows in the index. Because of this, a table can only have one clustered index, but it can have multiple non-clustered indexes.
The main difference between the two is that the database tries to keep the data in the same order as the corresponding keys in the clustered index. This can improve the performance of most query operations, as it provides a linear-access path to the data stored in the database.
SQL Question 11: Locate Customer Contacts Information
TD Synnex maintains a customer records database that keeps track of all customer names, contact information, and location details. You're tasked with finding all the customer records that have a Florida-based address. The table has columns for , , , , , and . Assume that the field contains a string with no particular format but always ends with the state of the customer.
Construct a PostgreSQL query that returns all customer records from Florida.
Example Input:
| customer_id | first_name | last_name | phone_number | address | |
|---|---|---|---|---|---|
| 1 | John | Doe | jdoe@example.com | 555-555-5555 | 123 Oak Street, Jacksonville, Florida |
| 2 | Jane | Smith | jsmith@example.com | 555-555-5556 | 456 Pine Street, Miami, Florida |
| 3 | James | Johnson | jjohnson@example.com | 555-555-5557 | 789 Maple Street, New York, New York |
| 4 | Linda | Williams | lwilliams@example.com | 555-555-5558 | 321 Birch Street, Atlanta, Georgia |
| 5 | Michael | Brown | mbrown@example.com | 555-555-5559 | 654 Cedar Street, Orlando, Florida |
Answer:
This query uses the keyword to search for addresses that contain the string 'Florida'. The percent sign () before 'Florida' is a wildcard character that matches any sequence of characters. As a result, any customer record where 'Florida' appears anywhere in the field will be returned by this query. Note that 'Florida' is the last part of the string, according to database design.
TD Synnex SQL Interview Tips
The best way to prepare for a TD Synnex SQL interview is to practice, practice, practice. In addition to solving the earlier TD Synnex SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Google, Microsoft and Silicon Valley startups.
Each exercise has multiple hints, step-by-step solutions and best of all, there's an interactive SQL code editor so you can right online code up your SQL query answer and have it checked.
To prep for the TD Synnex SQL interview you can also be a great idea to solve interview questions from other industrial and electrical distribution companies like:
- Arrow Electronics SQL questions
- Applied SQL questions
- MSC Industrial Direct SQL questions
- Fastenal SQL questions
In case your SQL skills are weak, don't worry about jumping right into solving questions – go learn SQL with this SQL tutorial for Data Scientists & Analysts.
This tutorial covers things like WHERE vs. HAVING and filtering data with WHERE – both of which show up frequently during SQL job interviews at TD Synnex.
TD Synnex Data Science Interview Tips
What Do TD Synnex Data Science Interviews Cover?
In addition to SQL query questions, the other question categories to practice for the TD Synnex Data Science Interview include:
- AB Testing Questions
- Python Pandas or R Coding Questions
- Product Data Science Interview Questions
- ML Modelling Questions
- Behavioral Interview Questions
How To Prepare for TD Synnex Data Science Interviews?
I believe the optimal way to study for TD Synnex Data Science interviews is to read the book I wrote: Ace the Data Science Interview.
It has 201 interview questions taken from Facebook, Google & startups. The book's also got a crash course covering SQL, Product-Sense & ML. And finally it's helped thousands of people land their dream job in data, which is why it's got over 1000+ 5-star reviews on Amazon.
While the book is more technical, it's also crucial to prepare for the TD Synnex behavioral interview. A good place to start is by understanding the company's values and mission.