logo

10 M&T Bank SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

M&T Bank employees write SQL queries often for data extraction and analysis for lending risk assessment. For this reason M&T Bank frequently asks SQL coding questions during interviews for Data Science, Data Engineering and Data Analytics jobs.

Thus, to help you study, we've curated 10 M&T Bank SQL interview questions – able to answer them all?

M&T Bank SQL Interview Questions

10 M&T Bank SQL Interview Questions

SQL Question 1: Calculate the Running Total of Deposits for Each Customer

As an analyst at M&T Bank, you have been given a task to analyze the deposits made by customers. Write a SQL query to calculate the running total of deposits for each customer, ordered by the deposit date. A "running total" for a row is defined as the sum of all the deposits made by that customer up to and including that row.

Consider a 'deposit' table as follows:

Example Input:
deposit_idcustomer_iddeposit_dateamount
10112301/06/20222000
10212301/07/20221000
10345601/07/20223000
10412301/09/2022500
10545601/09/20221500

In this case, our output should be a table that shows the customer id, deposit date, and running total of deposits for each customer up to the given date.

Example Output:
customer_iddeposit_daterunning_total
12301/06/20222000
12301/07/20223000
45601/07/20223000
12301/09/20223500
45601/09/20224500

Answer:


This query uses a window function to calculate the running sum of deposits grouped by customer id and ordered by the deposit date. For each row, it sums deposits made by the same customer including and prior to the current row.

For more window function practice, try this Uber SQL problem on DataLemur's online SQL coding environment:

Uber Window Function SQL Interview Question

Look at the open job listings at MT&T Bank and see what interests you!

SQL Question 2: Top Department Salaries

Suppose there was a table of M&T Bank employee salary data. Write a SQL query to find the top three highest paid employees within each department.

M&T Bank 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

Try this question interactively on DataLemur:

Top 3 Department Salaries

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 detailed solution with hints here: Top 3 Department Salaries.

SQL Question 3: How is a foreign key different from a primary key in a database?

To explain the difference between a primary key and foreign key, let's start with an example M&T Bank sales database:

:

+------------+------------+------------+------------+ | order_id | product_id | customer_id| quantity | +------------+------------+------------+------------+ | 1 | 303 | 1 | 2 | | 2 | 404 | 1 | 1 | | 3 | 505 | 2 | 3 | | 4 | 303 | 3 | 1 | +------------+------------+------------+------------+

In this table, could be a primary key. It is unique for each row in the table and cannot contain null values.

and could both be foreign keys. They reference the primary keys of other tables, such as a Products table and a Customers table, respectively. This establishes a relationship between the table and the other tables, such that each row in the sales database corresponds to a specific product and a specific customer.

M&T Bank SQL Interview Questions

SQL Question 4: Loan Payment Management

As a data analyst at M&T Bank, you've been asked to design a database for managing loan payments. You need to keep track of the clients, their loans, and the payments they make. Each loan is linked to a client, and each payment is linked to a loan.

Here are the details that need to be stored for each entity:

  • Clients: Client ID, Name, and Email
  • Loans: Loan ID, Client ID, Loan Amount, and Outstanding Amount
  • Payments: Payment ID, Loan ID, Payment Amount, and Payment Date

The primary way the database will be used is to find out the outstanding amount for each client, so the design should facilitate this kind of query.

Now, write a PostgreSQL query to find out the Outstanding Loan Amount for each Client.

Your task includes:

  1. Design the , , and table.
  2. Generate sample data for each table.
  3. Write a PostgreSQL query to compute the Outstanding Loan Amount for each client.
Sample Input:
client_idnameemail
1001John Doejohndoe@example.com
1002Jane Doejanedoe@example.com
Sample Input:
loan_idclient_idloan_amountoutstanding_amount
500110015000020000
500210027000050000
Sample Input:
payment_idloan_idpayment_amountpayment_date
1500120002022-12-01
2500110002022-12-02
3500250002022-12-03

Answer:


This query joins the and tables using the field. It then groups the resulting records by and , and computes the sum of the field within each group. The result is a list of clients and their total outstanding loan amounts.

SQL Question 5: What do the SQL commands / do?

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

Note that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle (but don't stress about knowing which DBMS supports what exact commands since the interviewers at M&T Bank should be lenient!).

For a tangible example in PostgreSQL, suppose you were doing an HR Analytics project for M&T Bank, and had access to M&T Bank's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all contractors who never were a employee using this query:


SQL Question 6: Filter Customer Records Based on Account Transactions

Company M&T Bank has given you access to their customer records database. The database includes a table named with records indicating the customer's account balances and transactions. You have been asked to write an SQL query to filter out records of customers who have made transactions over $10,000 in the past month and have 'Checking' account type.

Example Input:
account_idcustomer_idaccount_typetransaction_dateamount
101111Checking2022-07-0111000
102112Savings2022-07-0320000
103113Checking2022-07-055000
104114Checking2022-07-1012000
105115Savings2022-07-159000

Note: All amounts and dates in this table are hypothetical and do not represent real customer data.

Answer:

The SQL query for the PostgreSQL you would use to solve this problem would be:


This SQL query filters from the table all records where the is 'Checking' and over $10,000 with transactions made in the past month. '1 month' interval is subtracted from the current date to get the transactions made in the past month.

SQL Question 7: What sets relational and NoSQL databases apart?

While knowing this answer is beyond the scope of most Data Analyst & Data Science interviews, Data Engineers at M&T Bank should be at least aware of SQL vs. NoSQL databases.

Relational databases and non-relational (NoSQL) databases have some key differences, particularly in terms of how data is stored. Whereas relational databases have tables, made up of rows and columns, NoSQL databases use various data models like:

  • Wide-Column Stores – this database uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row within the same table
  • Key-Value Stores – instead of rows and columns, you have keys, where each key is associated with only one value in a collection (similar to a Python dictionary data structure!)
  • Graph Stores – instead of rows of data, you have nodes, and then can also have edges between entities (much like a Graph Data Structure for those who've taken a Computer Science data structures & algorithms class)

This added flexibility makes NoSQL databases well-suited for handling non-tabular data or data with a constantly changing format. However, this flexibility comes at the cost of ACID compliance, which is a set of properties (atomic, consistent, isolated, and durable) that ensure the reliability and integrity of data in a database. While most relational databases are ACID-compliant, NoSQL databases may not provide the same level of guarantees.

SQL Question 8: Calculating Average Loan Amounts

As an analyst at M&T Bank, we are interested in understanding the average loan amount our customers are applying for. Please calculate the average loan amount applied for each type of loan for each customer in the year 2021.

Example Input:
loan_idcustomer_idapplication_dateloan_typeloan_amount
10145101/15/2021Mortgage350000
10245102/10/2021Auto20000
10365704/30/2021Personal12000
10445106/25/2021Mortgage400000
10565711/19/2021Auto18000
Example Output:
customer_idloan_typeavg_loan_amount
451Mortgage375000.00
451Auto20000.00
657Personal12000.00
657Auto18000.00

Answer:


In the data provided, we have loan information including the customer_id, loan type, and loan amount. By grouping our data by the customer_id and the loan_type, and calculating the average on the loan_amount we can get the average loan amount per customer per loan type in 2021.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating top factors within categories or this JPMorgan Chase Cards Issued Difference Question which is similar for dealing with financial amounts over time.

SQL Question 9: Average Loan Amount By Type and Year

M&T Bank offers various types of loans to its customers, such as personal loans, home loans, auto loans etc. The bank needs to analyze the average loan amount provided to their customers for each type of loan, each year. For this, they want to write an SQL query that will return the average loan amount given for all types of loan per year.

For this problem, we're assuming the existence of a table with the following format.

Example Input:
loan_idcustomer_idloan_typeloan_dateloan_amount
001112auto01/15/202015000
002245home02/15/2020200000
003678personal01/16/20215000
004902auto02/18/202120000
005345home03/20/2022300000

Answer:

Here is the SQL query they can use. This query groups by the and with the clause, and it uses the function to calculate the average for each group.


This query would return a result set with each row containing the type of loan, the year, and the average amount loaned that year for that type of loan.

The is used to get the year part from the column.

Please ensure you have the necessary rights to execute this command and the relevant tables exist. The average value might be returned as a decimal number.

SQL Question 10: How would you speed up a slow SQL query?

There's several steps you can take to troubleshoot a slow SQL query.

First things first, figure out why the query is slow! You can use and commands in PostgreSQL to identify any performance bottlenecks. After that, you can start changing your query, depending on what the source of the query performance issue is.

Generally, indexes can help speed up queries. Also de-normalizing your tables might help, to remove slow joins.

Lastly, you could always just upgrade your hardware! Time and money wasted improving query performance could just be better spent on mroe powerful database servers!

While this is a very surface-level approach to optimizing SQL query performance, it should do the trick for Data Analyst interviews and Data Science interviews at M&T Bank. Data Engineers should know a bit more about the and before the interview.

How To Prepare for the M&T Bank SQL Interview

The key to acing a M&T Bank SQL interview is to practice, practice, and then practice some more! In addition to solving the above M&T Bank SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Netflix, Google, and Amazon. DataLemur SQL Interview Questions

Each exercise has multiple hints, detailed solutions and most importantly, there is an online SQL code editor so you can easily right in the browser your SQL query answer and have it graded.

To prep for the M&T Bank SQL interview it is also useful to practice SQL questions from other banking & finanacial services companies like:

However, if your SQL foundations are weak, forget about going right into solving questions – improve your SQL foundations with this SQL interview tutorial.

DataLemur SQL tutorial

This tutorial covers SQL topics like window functions and using ORDER BY – both of which come up frequently during M&T Bank SQL interviews.

M&T Bank Data Science Interview Tips

What Do M&T Bank Data Science Interviews Cover?

In addition to SQL interview questions, the other types of problems to practice for the M&T Bank Data Science Interview include:

M&T Bank Data Scientist

How To Prepare for M&T Bank Data Science Interviews?

I'm a tad biased, but I think the optimal way to study for M&T Bank Data Science interviews is to read my book Ace the Data Science Interview.

The book solves 201 interview questions taken from FAANG (FB, Apple, Amazon, Netflix, Google). It also has a refresher on SQL, AB Testing & ML. And finally it's vouched for by the data community, which is why it's got over 1000+ 5-star reviews on Amazon.

Ace the Data Science Interview