logo

9 Discover Financial Services SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

Data Analysts and Data Engineers at Discover Financial Services use SQL for analyzing transaction data and optimizing customer financial services. That's why Discover Financial Services often tests jobseekers SQL coding interview questions.

Thus, to help you prep, we've curated 9 Discover Financial Services SQL interview questions – able to answer them all?

Discover Financial Services SQL Interview Questions

9 Discover Financial Services SQL Interview Questions

SQL Question 1: Identify Frequent Credit Card Users at Discover Financial

At Discover Financial Services, it is important to identify frequent users of their credit cards. These frequent users, also referred to as VIP or whale users, are crucial for the sustained growth of the business. Creating a SQL query to identify these users would be beneficial for targeted marketing and customer care strategies, which in turn would increase our customer base.

Let's consider a table formatted as follows:

Example Input:
transaction_iduser_idtransaction_datecard_idamount
800120006/10/2022 00:00:0054500$40
900212306/10/2022 00:00:0054600$100
901220006/11/2022 00:00:0054500$150
800348906/20/2022 00:00:0054800$500
805012307/10/2022 00:00:0054600$40

To identify these power users, we can write a PostgreSQL query to check who has transacted more than a certain amount in the past month:

Answer:


This query will return the , number of transactions they have had in the past month (), and the total amount transacted () for users who have transacted over $5000 in the past month.

The threshold of $5000 is just an example and can be adjusted depending on the company's specific definition of a power user.

To practice a super-customer analysis question on DataLemur's free interactive SQL code editor, try this Microsoft SQL Interview problem: Microsoft SQL Interview Question: Super Cloud Customer

SQL Question 2: Department vs. Company Salary

You're given a table of Discover Financial Services employee and department salaries. Write a SQL query to find departments where the departments average salary is higher than the company's average salary.

You can solve this interview question directly within the browser on DataLemur:

Department vs. Company Salary

The answer is LONG – 30+ lines of SQL. You can find a detailed solution with hints here: Department Salaries.

SQL Question 3: What's the difference between a correlated and non-correlated sub-query?

A correlated sub-query is one that relies on the outer query and cannot be run independently. It uses the outer query to filter or transform data by referencing a column from it, while the outer query uses the results of the inner query. On the other hand, a non-correlated sub-query is not linked to the outer query and can be run on its own. It does not reference any columns from the outer query and is used to retrieve additional data for the outer query.

An example correlated sub-query:


This correlated subquery retrieves the names and salaries of Discover Financial Services employees who make more than the average salary for their department. The subquery references the department column in the main query's FROM clause (e1.department) and uses it to filter the rows of the subquery's FROM clause (e2.department).

An example non-correlated sub-query:


This non-correlated subquery retrieves the names and salaries of Discover Financial Services employees who make more than the average salary for the Data Analytics department (which honestly should be very few people since Data Analysts are awesome).

Anways, the subquery is independent of the main query and can be considered a standalone query. Its output (the average salary for the Data Analytics department) is then used in the main query to filter the rows of the table.

Discover Financial Services SQL Interview Questions

SQL Question 4: Calculate Average Transaction Amount Per Customer Per Month

As a Data Analyst for Discover Financial Services (DFS), one of your tasks is to analyze the transaction data for DFS's credit card customers and provide insights to steering marketing strategies.

For this SQL interview question, DFS is particularly interested in understanding the average transaction amount made by each customer on every month in the current year (2022).

Your task is write a SQL query that calculates the average monthly transaction amount per customer. For each month, the output should provide the , and the .

Example Input:
transaction_idcustomer_idtransaction_datetransaction_amount
100110101/18/2022450
100210201/28/2022875
100310302/05/2022525
100410202/20/2022700
100510103/10/2022965
100610404/28/2022310
100710105/15/20221135
100810106/20/2022780

Answer:


This query first filters only transactions happened in year 2022. For each record, it uses the SQL window function with the partition by and (extracted from the ) to compute the average transaction amount per customer for each month. Finally, it orders the result by and .

To solve a similar window function interview problem which uses RANK() on DataLemur's free interactive SQL code editor, try this Amazon SQL question asked in a BI Engineer interview: Amazon Window Function SQL Interview Problem

SQL Question 5: What is the process for finding records in one table that do not exist in 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 Discover Financial Services'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 6: Filter Discover Cardholders with Specific Conditions

The customer records database of Discover Financial Services contains extensive information about their customers, including when they opened their account, their payment history, balances, if they are currently delinquent, and if they have ever defaulted on a loan. As a data analyst, your task is to filter out customers who have opened their Discover cards after 2015, have a balance of more than $1,000, are currently delinquent in their payment, and have never defaulted on a loan.

Example Input:
customer_idopen_datepayment_historybalancedelinquentdefaulted
2345112/14/2014On Time950.00NoYes
8923201/17/2016Late1500.00YesNo
7839202/11/2018On Time1200.00NoNo
4762103/06/2017Late2500.00YesNo
9823110/29/2013Late750.00YesYes

Answer:


This PostgreSQL query will filter out cardholders based on the conditions given in the question. It uses WHERE clause to add the conditions, AND operator to ensure all conditions must be met, and comparison operators to set the conditions. This will return a list of customers who opened their accounts after 2015, have a balance more than 1,000, are currently delinquent, and have not defaulted on any loans.

SQL Question 7: What's a primary key?

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 Discover Financial Services ran:


The CampaignID column is used to uniquely identify each row in the table, and the constraint ensures that there are no duplicate CampaignID values. This helps to maintain the accuracy of the data by preventing duplicate rows. The primary key is also an important part of the table because it enables 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 with data on the results of the campaigns.

Read about how Discover has recently employed Google Gen AI to up thier customer experience!

SQL Question 8: Calculate the Average Balance of Accounts

As an SQL developer at Discover Financial Services, you have been tasked with calculating the average balance for each type of account (e.g., savings, checking, etc.) for the last year. This information is crucial to understand the overall financial wellness of the customers for the company.

example input:
account_idcustomer_idaccount_typeaccount_balance
84751092Savings$8,000.00
27214301Checking$2,500.00
39185862Savings$7,500.00
48193998Savings$12,000.00
70162540Checking$3,000.00
Example output:
account_typeavg_balance
Savings$9,166.67
Checking$2,750.00

Answer:


This query first categorizes the accounts based on the account type using the GROUP BY clause. It then calculates the average account balance within each group using the AVG function. The result is a table displaying the average balance per account type.

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages or this Alibaba Compressed Mean Question which is similar for usage of financial data.

SQL Question 9: Calculate the Click-Through-Rate (CTR) for Discover Financial Services

As a data analyst for Discover Financial Services, you have been given access to the database that contains information about users’ activities on their website. Your manager wants you to calculate the click-through-rate (CTR) for their digital ads. The CTR is the number of users who clicked on our ad divided by the number of times our ad is shown.

The databases contain two tables – the table, which records each time an ad is displayed, and the table, which records each time a user clicks on an ad.

Example Input:
impression_iduser_idad_idimpression_date
4401234347406/08/2022 00:00:00
3951782395206/10/2022 00:00:00
6709653295406/18/2022 00:00:00
4112909347407/26/2022 00:00:00
8862470295407/05/2022 00:00:00
Example Input:
click_iduser_idad_idclick_date
7722234347406/08/2022 00:02:00
6631782395206/10/2022 00:04:00
2953470295407/05/2022 00:05:00

Answer:


The above query starts by joining the table with the table on and . The query counts the total number of unique clicks and impressions per ad and computes the click-through rate as a percentage (total unique clicks divided by total unique impressions). It also prevents division by zero using the function.

To practice a similar problem about calculating rates, try this TikTok SQL Interview Question on DataLemur's interactive SQL code editor: SQL interview question from TikTok

How To Prepare for the Discover Financial Services SQL Interview

The key to acing a Discover Financial Services SQL interview is to practice, practice, and then practice some more! Beyond just solving the above Discover Financial Services SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Facebook, Google and unicorn tech startups. DataLemur SQL and Data Science Interview Questions

Each DataLemur SQL question has multiple hints, step-by-step solutions and best of all, there's an interactive SQL code editor so you can easily right in the browser your query and have it checked.

To prep for the Discover Financial Services SQL interview you can also be helpful to practice interview questions from other payment & credit companies like:

But if your SQL skills are weak, don't worry about diving straight into solving questions – refresh your SQL knowledge with this SQL interview tutorial.

DataLemur SQL tutorial

This tutorial covers SQL topics like Union vs. UNION ALL and handling date/timestamp data – both of these come up frequently in SQL job interviews at Discover Financial Services.

Discover Financial Services Data Science Interview Tips

What Do Discover Financial Services Data Science Interviews Cover?

Beyond writing SQL queries, the other topics tested in the Discover Financial Services Data Science Interview include:

Discover Financial Services Data Scientist

How To Prepare for Discover Financial Services Data Science Interviews?

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

  • 201 Interview Questions from FAANG, tech startups, and Wall Street
  • A Crash Course covering Python, SQL & ML
  • Great Reviews (1000+ reviews, 4.5-star rating)

Ace the Data Science Interview Book on Amazon