11 Edward Jones SQL Interview Questions (Updated 2024)
At Edward Jones Investments, SQL is used for analyzing financial data trends and optimizing portfolio management strategies based on data-driven insights. Because of this, Edward Jones LOVES to ask SQL query questions during interviews for Data Science and Data Engineering positions.
Thus, to help prep you for the Edward Jones SQL interview, here's 11 Edward Jones Investments SQL interview questions in this blog.
11 Edward Jones Investments SQL Interview Questions
SQL Question 1: Identify High-Investment Customers
Write a SQL query to identify customers who have invested more than $100,000 in stocks at "Edward Jones" in the last calendar year. For this question, assume we have two tables - one table called that records every purchase of a stock by a customer with the cost of those stocks, and a second table called that has details about the customers.
Example Input:
| customer_id | last_name | first_name |
|---|---|---|
| 101 | Smith | John |
| 102 | Johnson | Sarah |
| 103 | Brown | David |
| 104 | Taylor | Jessica |
| 105 | Miller | Michael |
Example Input:
| transaction_id | customer_id | transaction_date | stock_id | cost |
|---|---|---|---|---|
| 5050 | 101 | 2021-04-22 | 1001 | 25000.00 |
| 6060 | 102 | 2021-02-11 | 2001 | 15000.00 |
| 7070 | 102 | 2021-12-30 | 2001 | 90000.00 |
| 8080 | 103 | 2020-12-31 | 1002 | 30000.00 |
| 9090 | 104 | 2022-01-01 | 2002 | 60000.00 |
Answer:
This query joins the and tables on the field, limiting to transactions that occurred in the last calendar year. The clause groups the data by each customer. The clause then filters out those customers whose total investment (sum of the cost of all their transactions) over the specified period is less than $100,000. Finally, the clause orders the remaining customers by descending total investment, putting the highest investing customers at the top.
To practice a related super-user data analysis question on DataLemur's free online SQL code editor, try this recently asked Microsoft SQL interview question:
SQL Question 2: Top 3 Department Salaries
Given a table of Edward Jones employee salary data, write a SQL query to find the top three highest paid employees in each department.
Edward Jones Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Code your solution to this problem interactively on DataLemur:
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: What distinguishes an inner join from a full outer join?
A full outer join returns all rows from both tables, including any unmatched rows, whereas an inner join only returns rows that match the join condition between the two tables.
For a tangible example, suppose you had a table of Edward Jones orders and Edward Jones customers.
Here's a SQL inner join using the orders and customers tables:
This query will return rows from the orders and customers tables that have matching values. Only rows with matching values will be included in the results.
Here is an example of a using the orders and customers tables:
This query will return all rows from both the orders and customers tables, including any rows that do not have matching values. Any rows with null values for either table will be included in the results.
Learn about the Edward Jones analytics team and how they use SQL to enhance business operations.
SQL Question 4: Calculating the Running Total of Clients' Investments
Edward Jones is an investment firm. They want to analyze client investments and see how the total investment amount of each client changes every month. Each investment line for a client represents a new investment or an additional investment to an existing product.
The task here is to write a SQL query that calculates the running total of client's investment by product every month, ordered by month in ascending order. If a client didn't invest in a new product or add more amount to an existing product in a certain month, this month should still appear for the client with the total investment amount carried from the previous month.
Create two tables named and .
Example Input:
| client_id | client_name |
|---|---|
| 1 | John |
| 2 | Jane |
Example Input:
| investment_id | client_id | product_id | amount | investment_date |
|---|---|---|---|---|
| 1 | 1 | 101 | 5000.00 | 01/01/2022 |
| 2 | 1 | 102 | 3000.00 | 01/10/2022 |
| 3 | 1 | 101 | 2000.00 | 02/01/2022 |
| 4 | 2 | 101 | 1500.00 | 01/15/2022 |
| 5 | 2 | 101 | 1500.00 | 02/15/2022 |
Example Output:
| month | client_id | product_id | running_total |
|---|---|---|---|
| 1 | 1 | 101 | 5000.00 |
| 1 | 1 | 102 | 3000.00 |
| 1 | 1 | Total | 8000.00 |
| 1 | 2 | 101 | 1500.00 |
| 2 | 1 | 101 | 7000.00 |
| 2 | 1 | 102 | 3000.00 |
| 2 | 1 | Total | 10000.00 |
| 2 | 2 | 101 | 3000.00 |
Answer:
<WRITEUP_OF_ANSWER>:
In the query, we are using the function as a window function to calculate the running totals of investments made by each client for each product. is used to get the month part from the . The window function is partitioning the data by and , and it's ordering the data by .
To practice another window function question on DataLemur's free interactive SQL code editor, try this Google SQL Interview Question:
SQL Question 5: What are the ACID properties in a DBMS?
ACID refers to the four key properties that are essential to the reliable and correct execution of database transactions. These properties are:
Atomicity: ensures that a transaction is treated as a single operation, and either all of the changes are made or none of them are! Basically, the database version of a "-FULL SEND-"
Consistency: ensures that the data is in a consistent state before and after a transaction is completed. For example, if wiring money to a friendly Nigerian prince whose fallen on hard times, consistency ensures that the total value of funds lost in my account is the same amount that's gained in the prince's account!
Isolation: ensures that the intermediate state of a transaction is invisible to other transactions. Back to the wiring-the-prince-some-money example, isolation ensures that another transaction sees the transferred funds in my account OR the princes, but not in both accounts at the same time
Durability: ensures that once a transaction has been completed successfully, the changes made by the transaction are permanent and cannot be undone, even in the event of a system failure. Basically, no taksies backsies (even if your system has a meltdown!).
SQL Question 6: Filter Customer's Data Based on Investment Threshold and Area
Edward Jones wants to focus on customers who have invested more than $5000 in a specific area. Your task is to return a list of customers that meet this condition. Additionally, the result should also filter customers who live in the 'St. Louis' area.
Example Input:
| customer_id | name | area | investment |
|---|---|---|---|
| 123 | John Doe | St. Louis | 10000 |
| 265 | Jane Smith | New York | 5000 |
| 362 | Robert Johnson | St. Louis | 8000 |
| 192 | Julia Davis | St. Louis | 4000 |
| 981 | Charlie Brown | Los Angeles | 6000 |
Example Output:
| customer_id | name | area | investment |
|---|---|---|---|
| 123 | John Doe | St. Louis | 10000 |
| 362 | Robert Johnson | St. Louis | 8000 |
Answer:
This query filters the customers from the 'St. Louis' area who have made an investment greater than $5000. The WHERE clause is used to filter the records based on the conditions provided, and the AND operator is used to ensure that both conditions must be true for a record to be included in the result set.
SQL Question 7: Why is normalizing a database helpful?
There are several advantages to normalizing a database, including less redundancy, more flexibility, and better performance.
-
Less Redundancy: Normalization reduces redundancy by breaking down a larger, more general table into smaller, more specific tables. This reduces the amount of data that needs to be accessed for queries.
-
More Flexibility: Normalization makes it easier to modify the structure of the database, as there is less redundancy, so it allows you to make changes to one table without affecting others. This makes it easier to adapt the database to changing business needs (a very real reality at Edward Jones!)
-
Better Performance: Normalization can improve the performance of the database by reducing the amount of data that needs to be stored and accessed in a single table. This can result in faster query times and better overall performance.
SQL Question 8: Click Through Rates Calculation for Ad Campaigns
Edward Jones, a financial services firm, is running many digital ads banners in a new campaign targeting potential investors for their mutual fund products. For their campaign analytics, they are particularly interested in the click-through-rates (CTR).
CTR for a digital ad campaign is calculated as the number of clicks on the ads divided by the number of impressions (the number of times the ad was shown) times 100% to get the rate in percentage form.
Suppose we have two tables - that stores the impressions data, and that stores the data of ad clicks.
Example Input:
| impression_id | ad_id | impression_date |
|---|---|---|
| 1 | 101 | 06/10/2022 00:00:00 |
| 2 | 102 | 06/10/2022 00:00:00 |
| 3 | 101 | 06/11/2022 00:00:00 |
| 4 | 102 | 06/12/2022 00:00:00 |
| 5 | 101 | 06/13/2022 00:00:00 |
Example Input:
| click_id | ad_id | click_date |
|---|---|---|
| 1 | 101 | 06/10/2022 00:00:00 |
| 2 | 102 | 06/10/2022 00:00:00 |
| 3 | 101 | 06/12/2022 00:00:00 |
| 4 | 101 | 06/13/2022 00:00:00 |
| 5 | 101 | 06/13/2022 00:00:00 |
Answer:
This query first performs a FULL OUTER JOIN of the Impressions and Clicks table on the "ad_id" key to combine all data related to each ad.
Then it counts the number of impressions and clicks per ad from the combined data.
It goes on to calculate the Click-Through-Rate by dividing the total number of clicks by the total number of impressions times 100, with both counts typecast to to handle decimal point precision during division.
The result presents the Click-Through-Rate for each ad.
To solve a similar problem about calculating rates, try this SQL interview question from TikTok on DataLemur's online SQL coding environment:
SQL Question 9: Monthly Average Investment by Clients
Edward Jones has a keen interest in knowing the average amount their clients are investing monthly to help them with forecasting their business. They have a with columns , , , .
Example Input:
| transaction_id | client_id | transaction_date | investment_amount |
|---|---|---|---|
| 6321 | 100 | 02/11/2023 00:00:00 | 15000 |
| 76432 | 200 | 02/20/2023 00:00:00 | 45000 |
| 53262 | 300 | 02/21/2023 00:00:00 | 20000 |
| 64323 | 100 | 03/11/2023 00:00:00 | 35000 |
| 63412 | 100 | 03/20/2023 00:00:00 | 5000 |
| 46532 | 200 | 03/15/2023 00:00:00 | 25000 |
Calculate the average investment by month for each client.
Example Output:
| month | client_id | avg_investment |
|---|---|---|
| 2 | 100 | 15000.00 |
| 2 | 200 | 45000.00 |
| 2 | 300 | 20000.00 |
| 3 | 100 | 20000.00 |
| 3 | 200 | 25000.00 |
Answer:
This query works by first extracting the month from the using the function. Then, it aggregates the data by both the month and , and computes the average investment for each grouping using the function.
SQL Question 10: What are database views, and when would you use them?
Database views are created to provide customized, read-only versions of your data that you can query just like a regular table. So why even use one if they're just like a regular table?
Views are useful for creating a simplified version of your data for specific users, or for hiding sensitive data from certain users while still allowing them to access other data.
SQL Question 11: Customer Filter by Investment Type
Given a database containing the customer information, find all customers named 'Edward' who have made an investment in Bonds. We will use the keyword to match the pattern.
Here is the sample table:
Example Input:
| customer_id | first_name | last_name | investment_type | investment_amount |
|---|---|---|---|---|
| 1201 | Edward | Jones | Bonds | 10000 |
| 7025 | John | Smith | Stocks | 20000 |
| 8653 | Edward | Davis | Bonds | 15000 |
| 5621 | Diana | Chen | Mutual Funds | 30000 |
| 4875 | Edward | Johnson | Stocks | 25000 |
Answer:
Here is the PostgreSQL query:
This SQL query will fetch all records from the table where the is like 'Edward' and is 'Bonds'. It uses the keyword to find the pattern 'Edward' under the column and operator to check if is 'Bonds'. Therefore, it will return all Edwards who have invested in Bonds.
Example Output:
| customer_id | first_name | last_name | investment_type | investment_amount |
|---|---|---|---|---|
| 1201 | Edward | Jones | Bonds | 10000 |
| 8653 | Edward | Davis | Bonds | 15000 |
Edward Jones SQL Interview Tips
The key to acing a Edward Jones SQL interview is to practice, practice, and then practice some more!
Besides solving the earlier Edward Jones SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like FAANG tech companies and tech startups.
Each interview question has hints to guide you, full answers and crucially, there's an online SQL code editor so you can right in the browser run your SQL query answer and have it graded.
To prep for the Edward Jones SQL interview it is also a great idea to practice SQL questions from other banking & finanacial services companies like:
In case your SQL skills are weak, don't worry about diving straight into solving questions – go learn SQL with this interactive SQL tutorial.
This tutorial covers SQL topics like handling date/timestamp data and filtering data with boolean operators – both of which show up frequently during Edward Jones SQL assessments.
Edward Jones Investments Data Science Interview Tips
What Do Edward Jones Data Science Interviews Cover?
In addition to SQL interview questions, the other topics to prepare for the Edward Jones Data Science Interview are:
- Statistics and AB Testing Questions
- Coding Questions in Python or R
- Product & Business Analytics Questions
- ML Interview Questions
- Behavioral & Resume-Based Questions
How To Prepare for Edward Jones Data Science Interviews?
I'm sorta biased, but I believe the optimal way to prep for Edward Jones Data Science interviews is to read my book Ace the Data Science Interview.
The book has 201 data interview questions sourced from Google, Microsoft & tech startups. It also has a refresher on SQL, AB Testing & ML. And finally it's helped thousands of people land their dream job in data, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.