# 9 Mastercard SQL Interview Questions (Updated 2024)

Updated on

August 29, 2024

Data Science, Data Engineering, and Data Analytics employees at Mastercard write SQL queries to analyze transactional data for fraud detection, including identifying high-risk transactions and monitoring account activity, as well as to optimize payment processing systems by streamlining data processing and reducing latency for efficiency and speed. That is why Mastercard asks prospective hires SQL interview problems.

As such, to help you practice for the Mastercard SQL interview, here's 9 Mastercard SQL interview questions – can you solve them?

## 9 Mastercard SQL Interview Questions

### SQL Question 1: Calculate the Product Purchase Patterns

Mastercard, being a global technology company in the payments industry, likely has vast amounts of transaction data. Here is a hypothetical situation where you are asked to analyze such data:

Given a table "transactions" where each row represents a transaction made by a customer, your task is to write SQL query that will find the total number of transactions and total amount spent per customer for each quarter of 2021. Additionally, derive the quarter over quarter growth in total amount spent for each user.

The transactions table has the following schema:

##### Example Input:
transaction_idcustomer_idpurchase_dateamount
915356803/15/2021\$120
147885904/24/2021\$200
349156806/20/2021\$180
260885907/08/2021\$220
708343709/27/2021\$80
329756812/18/2021\$100

We want to output:

##### Example Output:
quartercustomer_idtotal_transactionstotal_spentquarterly_growth
Q1-20215681\$120null
Q2-20215681\$18050%
Q4-20215681\$100-44.44%
Q2-20218591\$200null
Q3-20218591\$22010%
Q3-20214371\$80null

This problem requires the usage of SQL window functions. I would approach it the following way:

This SQL query partitions the data by and calculates the total purchases and amount spent for each individual customer in each quarter of 2021. The quarterly growth is calculated by subtracting the total spent in the previous quarter from the total spent in the current quarter, divided by the total spent in the previous quarter.

To practice another window function question on DataLemur's free interactive SQL code editor, solve this Amazon BI Engineer interview question:

### SQL Question 2: Employee Salaries Higher Than Their Manager

Imagine there was a table of Mastercard employee salary data. Write a SQL query to find the employees who earn more than their own manager.

#### Mastercard Example Input:

employee_idnamesalarydepartment_idmanager_id
1Emma Thompson38001
2Daniel Rodriguez2230110
3Olivia Smith800018
4Noah Johnson680028
5Sophia Martinez1750110
8William Davis70002NULL
10James Anderson40001NULL

#### Example Output:

employee_idemployee_name
3Olivia Smith

This is the output because Olivia Smith earns \$8,000, surpassing her manager, William Davis who earns 7,800.

Try this interview question directly within the browser on DataLemur:

First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.

If the code above is confusing, you can find a step-by-step solution here: Employees Earning More Than Their Boss.

### SQL Question 3: What's the difference between and clause?

The clause serves as a filter for the groups created by the clause, similar to how the clause filters rows. However, is applied to groups rather than individual rows.

For example, say you were a data analyst at Mastercard trying to understand how sales differed by region:

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 \$400,000.

### SQL Question 4: Filter Mastercard Customer Records

As an analyst at Mastercard, you have access to a database that contains customer information. You have been asked to retrieve records of customers who are from USA, have at least two different cards with us, and have made five or more transactions in the past month.

##### Example Input:
customer_idcustomer_namecountry
100John DoeUSA
101Jane SmithUSA
102Roberto MartinezMexico
103Francois DupontFrance
##### Example Input:
card_idcustomer_idcard_type
5001100Mastercard Credit
5002100Mastercard Debit
5003101Mastercard Credit
5004102Mastercard Credit
5005102Mastercard Debit
##### Example Input:
transaction_idcard_idtransaction_dateamount
9000150012022-08-01 12:00:00100.00
9000250012022-08-02 14:00:00250.00
9000350022022-08-15 10:00:00600.00
9000450032022-08-15 14:00:00100.00
9000550052022-08-20 11:00:0050.00

In this SQL query, we first join the , and tables based on the matching and . Then, we filter based on the three conditions: customer's country being USA, customer having at least two different types of cards, and customer having made at least five transactions over the past month.

Notice the subqueries used to count the unique for each customer and to count the transactions made over the past month. The subqueries are vital in applying these conditions to each individual customer, rather than to the data as a whole.

### SQL Question 5: When would you use the function?

The COALESCE() function can be used to replace NULL values with a specified value. For instance, if you are a Data Analyst at a company and are working on a customer analytics project to identify the most engaged customers, you may have access to the following data in the table:

customer_idemail_engagementsms_engagement
101moderately_activenot_opted_in
201un-subscribedNULL
301NULLnot_opted_in
401not_activevery_active
501very_activevery_active
601NULLNULL

Before you could procede with your analysis, you would need to remove the NULLs, and replace them with the default value for email engagement (not_active), and the default sms_engagement level (not_opted_in).

To do this, you'd run the following query:

This would get you the following output:

customer_idemail_engagementsms_engagement
101moderately_activenot_opted_in
102un-subscribednot_opted_in
103not_activenot_opted_in
104not_activevery_active
105very_activevery_active
106not_activenot_opted_in

### SQL Question 6: Find the total amount transacted each day.

Given a table named that registers each transaction made with a Mastercard, find the total amount of money transacted each day. The table has the following columns:

• (integer)
• (integer)
• (timestamp)
• (decimal)

Also, consider a different time zone for the transaction (EST).

##### Example Input:
transaction_idcard_numbertransaction_dateamount
4561234567892023-02-11 00:00:00250.75
9819876543212023-02-11 11:59:59350.00
3251234567892023-02-12 05:45:00125.00
1709876543212023-02-12 19:30:00260.10
2151234567892023-02-13 16:00:00400.50
##### Example Output:
datetotal_amount
2023-02-11600.75
2023-02-12385.10
2023-02-13400.50

This query groups transactions by date (considering EST time zone) and calculates the sum of transactions for each day, resulting in a total amount of money transacted each day.

### SQL Question 7: How do you locate records in one table that are absent from another?

To identify records in one table that do not appear in another, you can use a LEFT JOIN and examine NULL values in the right-side table.

Say for example you had exported Mastercard's CRM (Customer Relationship Management) database into PostgreSQL, and had a table of sales leads, and a second table of companies.

Here's an example of how a query could find all sales leads that are not associated with a company:

This query brings back all rows from the sales leads table, along with any matching rows from the companies table. If there is no matching row in the companies table, NULL values will be returned for all of the right table's columns.

We then filter out out any rows where the column is , leaving only the sales leads that are NOT associated with a company.

### SQL Question 8: Filter Records Matching Specific Pattern in Customer Data

Mastercard wants to monitor the activity for a certain category of card users. The users under the category "MC_GOLD" have an underscore (_) at the start of their user_id. They want you to filter all the transactions records for the month of June from their database for this category of users who made transactions of more than USD 500.

Provide a list of user_ids and the total transaction amounts, ordered by transaction amount in descending order.

Here's the sample table for the database:

###### Example Input:
transaction_iduser_idtransaction_dateamount_usd
1234_MC_GOLD_00106/05/2022 10:30:00600
5678_MC_GOLD_00206/15/2022 16:30:00320
2467MC_PLATINUM_00306/20/2022 11:00:00780
2355_MC_GOLD_00106/25/2022 14:30:00680
7654MC_PLATINUM_00406/30/2022 09:45:00520
###### Example Output:
user_idtotal_amount
_MC_GOLD_0011280
_MC_GOLD_002320

The SQL query to solve this problem would be:

In this query, the LIKE function is used with the ESCAPE keyword to match the pattern with 'MC_GOLD%'. We limit our results to the month of June and transactions over \$500. Then we group by user_id and make a sum of the transaction amounts. Finally, we sort the results in descending order of the total transaction amount to get the output.

### SQL Question 9: Average Transaction Value by Customers and Payment Type

Mastercard has two tables, one is the table that contains information about the customers, and the other is the table that contains information about the transactions carried out by customers.

Write a SQL query to join these two tables and find the average transaction value for each customer based on the payment type.

##### Sample Input:
customer_idfirst_namelast_name
1JohnDoe
2JaneSmith
3BobJohnson
##### Sample Input:
transaction_idcustomer_idpayment_typetransaction_value
1011Credit Card250.00
1022Debit Card300.00
1031Credit Card450.00
1043Debit Card200.00
1052Credit Card150.00
1061Debit Card300.00
##### Example Output:
first_namelast_namepayment_typeavg_transaction_value
JohnDoeCredit Card350.00
JaneSmithDebit Card300.00
BobJohnsonDebit Card200.00
JaneSmithCredit Card150.00
JohnDoeDebit Card300.00

This query joins the table with the table using the common key. It then groups the joined table by first name, last name, and payment type and calculates the average transaction value for each group. The result is a table that shows the average transaction value for each customer by payment type.

Since join questions come up routinely during SQL interviews, try this Snapchat JOIN SQL interview question:

### Mastercard SQL Interview Tips

The best way to prepare for a Mastercard SQL interview is to practice, practice, practice. In addition to solving the earlier Mastercard SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Meta, Google and payment & fintech companies like Mastercard.

Each DataLemur SQL question has hints to guide you, fully explained answers along with a discussion board to see how others solved it and best of all, there's an online SQL code editor so you can right in the browser run your SQL query answer and have it executed.

To prep for the Mastercard SQL interview you can also be wise to solve SQL problems from other payment & fintech companies like:

However, if your SQL skills are weak, don't worry about jumping right into solving questions – strengthen your SQL foundations with this SQL interview tutorial.

This tutorial covers topics including rank window functions and GROUP BY – both of these show up frequently in Mastercard interviews.

### Mastercard Data Science Interview Tips

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

In addition to SQL interview questions, the other topics tested in the Mastercard Data Science Interview are:

Discover how Mastercard's AI and Advanced Analytics solutions are driving business growth and innovation!

Discover how Mastercard is revolutionizing the world of finance with AI and advanced analytics - read now!

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

I think the best way to prep for Mastercard Data Science interviews is to read the book Ace the Data Science Interview.

It covers 201 interview questions sourced from Microsoft, Amazon & startups. The book's also got a crash course on Product Analytics, SQL & ML. And finally it's helped a TON of people, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.

While the book is more technical, it's also important to prepare for the Mastercard behavioral interview. Start by understanding the company's cultural values.