# 8 Cintas SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

Cintas employees use SQL daily for analyzing uniform rental patterns and inventory management. Which is why Cintas covers SQL query questions during interviews for Data Science and Data Engineering positions.

So, to help you study, here's 8 Cintas SQL interview questions – able to solve them?

## 8 Cintas SQL Interview Questions

### SQL Question 1: Employee Uniform Sales Analysis

Cintas is a company that provides a range of products and services to businesses such as uniforms, janitorial supplies, safety & compliance training, and Business Protection Services (fire and security solutions). For this question, let's assume that you're analyzing the uniform sales department.

As a data analyst, you are asked to compile a monthly report for the last year by the head office. This report needs to show the total number of uniform pieces sold each month per department and the average sale per item in each department. You have access to the sales data of each transaction.

Here is your database table named .

##### Example Input:
sales_idsales_datedepartment_idnum_of_uniformstotal_sale
87652021-04-1000110200.00
52102021-04-2800250450.00
59312021-06-1600130660.00
35042021-11-2500260540.00
24862022-01-1800120400.00

Your task is to write a SQL query to create this report. The output should look like this. The date should be in the 'yyyymm' format.

##### Example Output:
month_yeardepartment_idtotal_num_of_uniformsavg_sale_per_item
2021040011020.00
202104002509.00
2021060013022.00
202111002609.00
2022010012020.00

In PostgreSQL, you can use the function to get the month and year part of a date, and and function to calculate the average sale per uniform item. Here is the query that solves the problem:

This query first truncates the to the month and formats it as 'YYYYMM'. Then it groups the sales by and to calculate the total number of pieces sold and the average sale per item in each month for each department. It then rounds the average sale per item to 2 decimal places using the function.

For more window function practice, try this Uber SQL Interview Question on DataLemur's online SQL code editor:

### SQL Question 2: Top 3 Salaries

Imagine there was a table of Cintas employee salary data. Write a SQL query to find the top three highest paid employees within each department.

#### Cintas Example Input:

employee_idnamesalarydepartment_id
1Emma Thompson38001
2Daniel Rodriguez22301
3Olivia Smith20001
4Noah Johnson68002
5Sophia Martinez17501
8William Davis68002
10James Anderson40001

#### Example Input:

department_iddepartment_name
1Data Analytics
2Data Science

#### Example Output:

department_namenamesalary
Data AnalyticsJames Anderson4000
Data AnalyticsEmma Thompson3800
Data AnalyticsDaniel Rodriguez2230
Data ScienceNoah Johnson6800
Data ScienceWilliam Davis6800

You can solve this question interactively on DataLemur:

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 tough, you can find a detailed solution here: Top 3 Department Salaries.

### SQL Question 3: What does the 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 Cintas employees:

first_namejob_title
AkashData Analyst
BrittanyData Scientist
CarlosData Engineer
DiegoData Engineer

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 Cintas:

job_title
Data Analyst
Data Scientist
Data Engineer

### SQL Question 4: Calculate the Average Units Used per Location for Each Product

Cintas is a company that provides specialized services to businesses, including uniform rental and supply. For each location, they maintain inventory for numerous products.

In this question, you are required to find out the average number of units used per location for each product across all the locations, during a year.

##### Sample Input:
product_idlocation_idyearunits_used
10010012020500
10010022020400
10010032020600
10020012020200
10020022020250
10020032020300
##### Sample Input:
product_idproduct_name
1001Uniform
1002Towels
##### Example Output:
productaverage_units_used_per_location
Uniform500
Towels250

In this query, we join the inventory table with the products table on the product_id. We then filter the records for the year 2020. Using the AVG function, we compute the average units used per location for each product. The GROUP BY clause is used to group the records by product name.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for inventory and product analysis or this CVS Health Pharmacy Analytics (Part 1) Question which is similar for average calculation across locations.

### SQL Question 5: What is the role of the constraint in SQL?

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 Cintas's CRM (customer-relationship management) tool.

In this example, the table has a foreign key field called that references the "account_id" field in the table (the primary key). This helps to link the data about each opportunity to the corresponding account information in the accounts table.

This makes sure 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 ensures that data is not deleted from the table if there are still references to it in the table.

### SQL Question 6: Analyze the Click-Through-Rates (CTR) for Cintas Ads

Cintas Corporation is a business services company. Suppose you are a data analyst at Cintas and are interested in understanding the effectiveness of advertisements displayed across various websites. In particular, you are tasked to determine the click-through-rate (CTR) for each website in the past one month. Further, you are also interested in understanding the conversion rate (how many clicked ads led to a real sale).

To answer these questions, we will consider two tables - and .

##### Example Input:
101400506/10/2022 00:00:00Yahoo
103400506/16/2022 00:00:00Yahoo
104200206/20/2022 00:00:00MSN
105200206/30/2022 00:00:00MSN
##### Example Input:
conversion_idclick_idsales_amt
39101150.00
40102250.00
41104120.00

We are interested in understanding the total click-through rate and conversion rate for each website.

In the above solution, we first calculate the total number of ad clicks () per website for a given one month period in the CTE.

Next, we calculate the total number conversions () per website for the same time period in the CTE.

Finally, we perform a left join on these two tables on the column and calculate the by dividing by . We also calculate the by dividing by the sum of over all websites. Both rates are presented as percentages.

This query gives us both the click-through rate and conversion rate for each website where the ads were displayed. This data can be used to understand the return on investment for advertising on each website and also to guide future advertising decisions.

To solve a related problem on DataLemur's free interactive SQL code editor, attempt this SQL interview question asked by Facebook:

### SQL Question 7: Can you explain what / SQL commands do?

Note: interviews at Cintas often aren't trying to test you on a specific flavor of SQL. As such, you don't need to exactly know that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle – you just need to know the general concept!

Your answer should mention that the / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.

Here's a PostgreSQL example of using EXCEPT to find all of Cintas's Facebook video ads with more than 50k views that aren't also being run on YouTube:

If you want to retain duplicates, you can use the EXCEPT ALL operator instead of EXCEPT. The EXCEPT ALL operator will return all rows, including duplicates.

### SQL Question 8: Filter Customer Records by Service Location

You're working for Cintas, a company that provides specialized services to businesses, including uniform rental and cleaning services. The company has a wide customer database and different service locations. Your task is to write a SQL query to filter the database so you only retrieve customers whose service location starts with 'San' (This means any location that starts with 'San' such as 'San Diego', 'San Francisco' etc.)

For simplicity, let's consider the customer records database has the following structure:

##### Example Input:
customer_idcustomer_nameservice_locationservice_typestart_date
125McDonaldsSan DiegoUniform Rental2015-03-09
678StarbucksNew YorkCleaning Services2017-06-27
231In-N-OutSan FranciscoUniform Rental2016-12-03
900Burger KingLos AngelesCleaning Services2017-11-14
754SubwaySan AntonioUniform Rental2018-05-21

To filter the customers whose service location starts with 'San', you can use the SQL statement:

This query will return the records of the customers with a service location starting with 'San'. The '%' symbol is a wildcard character that matches any sequence of characters.

##### Example Output:
customer_idcustomer_nameservice_locationservice_typestart_date
125McDonaldsSan DiegoUniform Rental2015-03-09
231In-N-OutSan FranciscoUniform Rental2016-12-03
754SubwaySan AntonioUniform Rental2018-05-21

The SQL query has filtered the database to return only those customers whose service location starts with 'San'. It enables Cintas to easily get insight into their customers in 'San' locations and provides a starting point for further data analysis.

### Cintas SQL Interview Tips

The key to acing a Cintas SQL interview is to practice, practice, and then practice some more! In addition to solving the above Cintas SQL interview questions, you should also solve the 200+ tricky sql questions on DataLemur which come from companies like Amazon, Microsoft, Meta, and smaller tech companies.

Each problem on DataLemur has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an online SQL code editor so you can right online code up your SQL query and have it graded.

To prep for the Cintas SQL interview it is also helpful to practice SQL questions from other apparel companies like:

But if your SQL query skills are weak, don't worry about going right into solving questions – go learn SQL with this SQL tutorial for Data Analytics.

This tutorial covers things like aggregate functions like SUM()/COUNT()/AVG() and WHERE vs. HAVING – both of which come up frequently in Cintas SQL assessments.

### Cintas Data Science Interview Tips

#### What Do Cintas Data Science Interviews Cover?

Beyond writing SQL queries, the other types of problems to practice for the Cintas Data Science Interview are:

#### How To Prepare for Cintas Data Science Interviews?

The best way to prepare for Cintas Data Science interviews is by reading Ace the Data Science Interview. The book's got: