8 Comerica SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

Comerica employees use SQL daily for analyzing customer banking behaviors and predicting financial risks based on historical transaction data. Unsurprisingly this is why Comerica almost always evaluates jobseekers on SQL questions during interviews for Data Science, Analytics, and & Data Engineering jobs.

To help you practice for the Comerica SQL interview, we've collected 8 Comerica SQL interview questions can you solve them?

Comerica SQL Interview Questions

8 Comerica SQL Interview Questions

SQL Question 1: Comerica Loans Analysis

Comerica is a financial services company, so a dataset relevant to them might be the loans they issue. Let's say you have a table , where each row corresponds to a client's loan and contains information about the client, the amount of the loan, the term, and the date when the loan was issued.

Your task is to write a SQL query that determines the total amount of loans issued per month across the entire dataset. More specifically, the output should be a table that has a row for each month in the dataset across the years and the total sum of loan_amount for that particular month. The months should be listed in chronological order.

Example Input:
Example Output:


In the above query, a PostgreSQL function is used, , which extracts the month and year portion from the column in 'MM-YYYY' format.

Then, sums up the loan_amounts for all the loans grouped by the month-year.

Finally, sorts the results in chronological order, ensuring that the earliest month appears first.

To solve another window function question on DataLemur's free online SQL coding environment, try this Google SQL Interview Question: Google SQL Interview Question

SQL Question 2: Second Highest Salary

Suppose there was a table of Comerica employee salary data. Write a SQL query to find the 2nd highest salary among all employees.

Comerica Example Input:


Example Output:


You can solve this interview question interactively on DataLemur:

2nd Highest Salary SQL Interview Question


You can find a step-by-step solution here: 2nd Highest Salary.

Check out the Comerica career page and see what jobs are looking for those with SQL skills.

SQL Question 3: What is a SQL constraint?

A UNIQUE constraint ensures that all values in a column are different. It is often used in conjunction with other constraints, such as NOT NULL, to ensure that the data meets certain conditions.

For example, if you had Comerica employee data stored in a database, here's some constraints you'd use:

In the Comerica employee example, the UNIQUE constraint is applied to the "email" field to ensure that each employee has a unique email address. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two employees had the same email address.

Comerica SQL Interview Questions

SQL Question 4: Calculate the Average Account Balance for Each Account Type

At Comerica, one of the challenges could involve analyzing financial data related to customer accounts. For instance, a question could be: Given a table of customer account data, can you find the average account balance per account type for all accounts?

Here is some sample data:

Example Input:


This SQL query will group all accounts by their type (either 'checking' or 'savings') and compute the average balance for each account type. The AVG function is used to calculate the average of all the balance values for each distinct group.

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for aggregating average metrics grouped by category or this Alibaba Compressed Mean Question which is similar for calculating mean values on large data sets.

SQL Question 5: What's the major difference between and ?

The clause works similarly to the clause, but it is used to filter the groups of rows created by the clause rather than the rows of the table themselves.

For example, say you were analyzing Comerica sales data:

This query retrieves the total sales for all products in each region, and uses the clause to only sales made after January 1, 2023. The rows are then grouped by region and the clause filters the groups to include only those with total sales greater than $500k.

SQL Question 6: Calculate Click-Through Conversion Rates

Comerica is a financial services company providing various banking products and services to businesses and individuals. As a part of their digital marketing strategies, they run a variety of online campaigns. They are interested in the click-through conversion rate from viewing an ad to browsing a specific banking product's detail page.

Given two tables, and , write a SQL query to calculate the click-through conversion rate for each ad campaign for a specific month, say July 2022.

Table Example:
10110012022-07-05 15:14:00201
10210022022-07-06 10:20:00200
10310032022-07-10 11:30:00202
10410012022-07-12 12:40:00203
10510052022-07-15 14:50:00204
Table Example:
20110012022-07-05 15:15:005001
20210032022-07-10 11:35:005002
20310012022-07-12 12:45:005003


In PostgreSQL, the query to solve this problem would look something like:

In this SQL block, the CTE first calculates total clicks for each campaign during July 2022. The second CTE determines how many of those clicks led to product views. Finally, the main query calculates the click-through conversion rate which is total_views/total_clicks. We multiply by 100 to convert it into a percentage.

While the SQL does the major heavy lifting, please remember to verify your actual application logic to ensure users are not engaging in any unusual behaviors that might distort your statistics.

To practice a similar SQL interview question on DataLemur's free interactive SQL code editor, attempt this Meta SQL interview question: Facebook App CTR SQL Interview question

SQL Question 7: What do primary keys do?

The primary key of a table is a column or set of columns that serves as a unique identifier for each row. It ensures that all rows are distinct and does not allow null values.

For example, say you had stored some Facebook ad campaign data that Comerica ran:

The column uniquely identifies each row in the table, and the PRIMARY KEY constraint ensures that no two rows have the same . This helps to maintain the integrity of the data in the table by preventing duplicate rows.

The primary key is also an important part of the table because it allows you to easily identify and reference specific campaigns in your Facebook Ad data. You can use it to join to other tables in the database, such as a table containing data on the results of the campaigns.

SQL Question 8: Query Clients from Specific Cities

As a Database Specialist at Comerica, you're given a task to filter the client data. You're required to write a SQL query that will retrieve all customer details who are from cities with names that start with 'San'.

Below is an example of the 'clients' table:

Example Input:
2324John DoeSan FranciscoCA5846
5643Jane SmithLos AngelesCA8976
9872Robert JohnsonSan DiegoCA3410
3485William BrownSacramentoCA6528
4829Michael DavisSanta RosaCA2851

Your task is to create a query that will show the following results:

Example Output:
2324John DoeSan FranciscoCA5846
9872Robert JohnsonSan DiegoCA3410
4829Michael DavisSanta RosaCA2851


The above query uses the operator in SQL, coupled with the % wildcard character, to match clients whose city names start with 'San'. The % character signifies any number of characters coming after 'San', so it considers all names that start with 'San' regardless of what comes after.

Preparing For The Comerica SQL Interview

The best way to prepare for a Comerica SQL interview is to practice, practice, practice. Beyond just solving the above Comerica SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like Netflix, Airbnb, and Amazon. DataLemur Questions

Each exercise has hints to guide you, detailed solutions and crucially, there's an online SQL code editor so you can instantly run your SQL query and have it executed.

To prep for the Comerica SQL interview it is also wise to solve SQL problems from other banking & finanacial services companies like:

In case your SQL query skills are weak, forget about diving straight into solving questions – go learn SQL with this free SQL for Data Analytics course.

DataLemur SQL Course

This tutorial covers things like 4 types of JOINS and LEAD/LAG window functions – both of which show up frequently in SQL interviews at Comerica.

Comerica Data Science Interview Tips

What Do Comerica Data Science Interviews Cover?

Besides SQL interview questions, the other topics covered in the Comerica Data Science Interview include:

Comerica Data Scientist

How To Prepare for Comerica Data Science Interviews?

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

  • 201 Interview Questions from tech companies like Netflix, Google, & Airbnb
  • A Refresher on Python, SQL & ML
  • Great Reviews (1000+ reviews, 4.5-star rating)

Acing Data Science Interview