9 Mastercard SQL Interview Questions (Updated 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_id | customer_id | purchase_date | amount |
|---|---|---|---|
| 9153 | 568 | 03/15/2021 | $120 |
| 1478 | 859 | 04/24/2021 | $200 |
| 3491 | 568 | 06/20/2021 | $180 |
| 2608 | 859 | 07/08/2021 | $220 |
| 7083 | 437 | 09/27/2021 | $80 |
| 3297 | 568 | 12/18/2021 | $100 |
We want to output:
Example Output:
| quarter | customer_id | total_transactions | total_spent | quarterly_growth |
|---|---|---|---|---|
| Q1-2021 | 568 | 1 | $120 | null |
| Q2-2021 | 568 | 1 | $180 | 50% |
| Q4-2021 | 568 | 1 | $100 | -44.44% |
| Q2-2021 | 859 | 1 | $200 | null |
| Q3-2021 | 859 | 1 | $220 | 10% |
| Q3-2021 | 437 | 1 | $80 | null |
Answer:
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_id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 | |
| 2 | Daniel Rodriguez | 2230 | 1 | 10 |
| 3 | Olivia Smith | 8000 | 1 | 8 |
| 4 | Noah Johnson | 6800 | 2 | 8 |
| 5 | Sophia Martinez | 1750 | 1 | 10 |
| 8 | William Davis | 7000 | 2 | NULL |
| 10 | James Anderson | 4000 | 1 | NULL |
Example Output:
| employee_id | employee_name |
|---|---|
| 3 | Olivia 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:
Answer:
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_id | customer_name | country |
|---|---|---|
| 100 | John Doe | USA |
| 101 | Jane Smith | USA |
| 102 | Roberto Martinez | Mexico |
| 103 | Francois Dupont | France |
Example Input:
| card_id | customer_id | card_type |
|---|---|---|
| 5001 | 100 | Mastercard Credit |
| 5002 | 100 | Mastercard Debit |
| 5003 | 101 | Mastercard Credit |
| 5004 | 102 | Mastercard Credit |
| 5005 | 102 | Mastercard Debit |
Example Input:
| transaction_id | card_id | transaction_date | amount |
|---|---|---|---|
| 90001 | 5001 | 2022-08-01 12:00:00 | 100.00 |
| 90002 | 5001 | 2022-08-02 14:00:00 | 250.00 |
| 90003 | 5002 | 2022-08-15 10:00:00 | 600.00 |
| 90004 | 5003 | 2022-08-15 14:00:00 | 100.00 |
| 90005 | 5005 | 2022-08-20 11:00:00 | 50.00 |
Answer:
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_id | email_engagement | sms_engagement |
|---|---|---|
| 101 | moderately_active | not_opted_in |
| 201 | un-subscribed | NULL |
| 301 | NULL | not_opted_in |
| 401 | not_active | very_active |
| 501 | very_active | very_active |
| 601 | NULL | NULL |
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_id | email_engagement | sms_engagement |
|---|---|---|
| 101 | moderately_active | not_opted_in |
| 102 | un-subscribed | not_opted_in |
| 103 | not_active | not_opted_in |
| 104 | not_active | very_active |
| 105 | very_active | very_active |
| 106 | not_active | not_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_id | card_number | transaction_date | amount |
|---|---|---|---|
| 456 | 123456789 | 2023-02-11 00:00:00 | 250.75 |
| 981 | 987654321 | 2023-02-11 11:59:59 | 350.00 |
| 325 | 123456789 | 2023-02-12 05:45:00 | 125.00 |
| 170 | 987654321 | 2023-02-12 19:30:00 | 260.10 |
| 215 | 123456789 | 2023-02-13 16:00:00 | 400.50 |
Example Output:
| date | total_amount |
|---|---|
| 2023-02-11 | 600.75 |
| 2023-02-12 | 385.10 |
| 2023-02-13 | 400.50 |
Answer:
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_id | user_id | transaction_date | amount_usd |
|---|---|---|---|
| 1234 | _MC_GOLD_001 | 06/05/2022 10:30:00 | 600 |
| 5678 | _MC_GOLD_002 | 06/15/2022 16:30:00 | 320 |
| 2467 | MC_PLATINUM_003 | 06/20/2022 11:00:00 | 780 |
| 2355 | _MC_GOLD_001 | 06/25/2022 14:30:00 | 680 |
| 7654 | MC_PLATINUM_004 | 06/30/2022 09:45:00 | 520 |
Example Output:
| user_id | total_amount |
|---|---|
| _MC_GOLD_001 | 1280 |
| _MC_GOLD_002 | 320 |
Answer:
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_id | first_name | last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
Sample Input:
| transaction_id | customer_id | payment_type | transaction_value |
|---|---|---|---|
| 101 | 1 | Credit Card | 250.00 |
| 102 | 2 | Debit Card | 300.00 |
| 103 | 1 | Credit Card | 450.00 |
| 104 | 3 | Debit Card | 200.00 |
| 105 | 2 | Credit Card | 150.00 |
| 106 | 1 | Debit Card | 300.00 |
Example Output:
| first_name | last_name | payment_type | avg_transaction_value |
|---|---|---|---|
| John | Doe | Credit Card | 350.00 |
| Jane | Smith | Debit Card | 300.00 |
| Bob | Johnson | Debit Card | 200.00 |
| Jane | Smith | Credit Card | 150.00 |
| John | Doe | Debit Card | 300.00 |
Answer:
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:
- Statistics Interview Questions
- Coding Questions in Python or R
- Product Data Science Interview Questions
- ML Interview Questions
- Behavioral & Resume-Based Questions
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.