# 11 Northern Trust SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

At Northern Trust, SQL is used day-to-day for analyzing financial data for trends and insights, and managing databases to improve asset management strategies. That's why Northern Trust almost always evaluates jobseekers on SQL problems during interviews for Data Science, Data Engineering and Data Analytics jobs.

So, to help you study, we've curated 11 Northern Trust SQL interview questions – can you answer each one?

## 11 Northern Trust SQL Interview Questions

### SQL Question 1: Analyzing Average Transaction Amount Using SQL Window Functions

Imagine you are a data analyst at Northern Trust. You have access to a table called that records every transaction for all the customers of the bank. The schema of the table is as follows:

##### Example Input:
transaction_idaccount_idtransaction_datetransaction_typetransaction_amount
123123401/01/2022 00:00:00Deposit1000
124123401/02/2022 00:00:00Withdrawal100
125567801/01/2022 00:00:00Deposit2000
126567801/01/2022 00:00:00Withdrawal500
127567801/02/2022 00:00:00Deposit1500

For each account, you are tasked to analyze the average deposit and withdrawal amounts on a per-month basis. Please write a SQL query for this scenario.

Here is suitable PostgreSQL query to solve the problem:

In this SQL statement, a window function is used to calculate the average deposit and withdrawal for each account on a monthly basis. The clause ensures that the average is computed within each account, each month, and each transaction type (either deposit or withdrawal). Finally, the result is ordered by account_id, the month of transaction, and transaction type for easier reading and analysis.

The result will include each account_id, month, transaction type (either deposit or withdrawal) and the corresponding average transaction amount for those categories.

To solve a similar window function question on DataLemur's free interactive coding environment, try this Google SQL Interview Question:

### SQL Question 2: Well Paid Employees

Given a table of Northern Trust employee salary information, write a SQL query to find employees who make more than their own boss.

#### Northern Trust 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. You can solve 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 solution above is confusing, you can find a detailed solution with hints here: Employees Earning More Than Managers. ### SQL Question 3: What's the difference between a foreign and primary key? To clarify the distinction between a primary key and a foreign key, let's examine employee data from Northern Trust's HR database: : +------------+------------+------------+------------+ | employee_id| first_name | last_name | manager_id | +------------+------------+------------+------------+ | 1 | Aubrey | Graham | 3 | | 2 | Marshal | Mathers | 3 | | 3 | Dwayne | Carter | 4 | | 4 | Shawn | Carter | | +------------+------------+------------+------------+ In this table, serves as the primary key. It uniquely identifies each employee and cannot be null. functions as a foreign key, linking to the of the employee's manager. This establishes a relationship between Northern Trust employees and their managers, allowing for easy querying to find an employee's manager or see which employees report to a specific manager. The table may also have multiple foreign keys that reference primary keys in other tables. For example, and foreign keys could be used to connect each employee to their respective department and location. ### SQL Question 4: Finding High Net Worth Clients Northern Trust is a financial services company. For this scenario, you are tasked with finding all clients who have more than $1 million in either individual accounts or total account assets, lived in "Illinois" state, and joined after the year 2015. Additionally, you should also retrieve clients whose total transaction amount in the last 6 months is more than$200,000. Here's an example of sample data: ##### Example Input: client_idclient_namestatejoin_dateaccount_balance 4356John DoeIllinois2018-07-26$1,500,000
9872Jane SmithNew York2016-06-14$800,000 2351Robert JohnsonIllinois2017-03-01$750,000
5527Laura DavisIllinois2018-05-15$1,250,000 ##### Example Input: transaction_idclient_idtransaction dateamount 876543562022-01-23$120,000
987198722022-02-13$50,000 658923512022-02-20$220,000

### SQL Question 8: Analyzing Click-through Rates for Marketing Campaigns

Northern Trust has been running a series of marketing campaigns to attract more clients to their digitized financial services. They want to analyze the effectiveness of their campaigns. For each email campaign, they track whether a client clicked on the link in the email (a "click") and whether that click led to the client signing up for a new service (a "conversion").

The marketing team has asked you to calculate the click-through rate (CTR), which is the number of clicks divided by the number of emails sent (expressed as a percentage), and the conversion rate, which is the number of conversions divided by the number of clicks (expressed as a percentage), for each campaign.

You are provided with two data tables:

#### Example Input:

campaign_idemails_sent
1015000
1027500
1036200

#### Example Input:

result_idcampaign_idclicksconversions
40110120050
40210235075
40310331055

The company wants the answer table to look like this:

#### Example Output:

campaign_idctrconversion_rate
1014.00%25.00%
1024.67%21.43%
1035.00%17.74%

This SQL query joins two tables: and on the column. For each campaign, it computes the click-through rate (CTR) by dividing the number of clicks by the number of emails sent and multiplying by 100 to convert to a percentage. The conversion rate is calculated by dividing the number of conversions by the number of clicks and multiplying by 100 to convert to a percentage. The results are formatted as text followed by the percentage symbol (%).

To practice another question about calculating rates, try this TikTok SQL question on DataLemur's online SQL code editor:

See the latest news from Northern Trust and stay up to date before the interview!

### SQL Question 9: Average Portfolio Values by Client & Quarter

As Northern Trust is a financial services company, let's assume they maintain portfolios for clients. These portfolios could fluctuate in value over time as markets rise and fall. Given this, could you provide a breakdown of the average portfolio value by client and quarter for the year 2022?

Please make use of the following example tables:

#### Example Input:

portfolio_idclient_idvaluedate
101130100000.0003/01/2022
110230102000.0006/30/2022
12024580000.0001/12/2022
13014585000.0003/14/2022
14024590000.0006/20/2022
150260120000.0001/05/2022

#### Example Output:

quarterclient_idaverage_portfolio_value
Q130100000.00
Q230102000.00
Q14582500.00
Q24590000.00
Q160120000.00

In this query, we extract the quarter from the date and group by this along with the client_id to calculate the average portfolio value for each client per quarter in 2022.

### SQL Question 10: When would you use the constraint?

A is a column or set of columns in a table that references the primary key of another table. It is used to link the two tables together, and to ensure that the data in the foreign key column is valid.

The constraint helps to ensure the integrity of the data in the database by preventing the insertion of rows in the foreign key table that do not have corresponding entries in the primary key table. It also helps to enforce the relationship between the two tables, and can be used to ensure that data is not deleted from the primary key table if there are still references to it in the foreign key table.

For example, if you have a table of Northern Trust customers and an orders table, the customer_id column in the orders table could be a that references the id column (which is the primary key) in the Northern Trust customers table.

### SQL Question 11: Join Customer and Transactions tables

As an analyst at Northern Trust, your task is to determine the total account balances for each customer. You are given two tables, and .

The table has the following schema:

CustomerIDFirstNameLastNameEmail
1JohnDoejohn.doe@example.com
2JaneSmithjane.smith@example.com
3TomThompsontom.thompson@example.com
4SusieQueuesusie.queue@example.com

The table has the following schema:

TransactionIDCustomerIDTransactionDateAmount
100112022-09-01500.00
100212022-09-15-200.00
100322022-08-01300.00
100432022-07-15200.00
100522022-10-01-100.00

Write a SQL query to obtain the total balance for each customer using the data provided in the above tables. Remember, debit transactions will be represented as negative amounts.

In the above query, we first join the and tables on . Then, we group the data by , , and and calculate the total balance for each customer by summing the from the table. The result will give us the total account balance for each customer.

Since joins come up so often during SQL interviews, try an interactive SQL join question from Spotify:

### Northern Trust SQL Interview Tips

The key to acing a Northern Trust SQL interview is to practice, practice, and then practice some more! In addition to solving the earlier Northern Trust SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Google, Facebook, Microsoft and Amazon.

Each SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an interactive coding environment so you can right in the browser run your SQL query and have it executed.

To prep for the Northern Trust SQL interview you can also be useful to solve interview questions from other banking & finanacial services companies like:

In case your SQL coding skills are weak, forget about jumping right into solving questions – improve your SQL foundations with this free SQL tutorial.

This tutorial covers SQL topics like Union vs. UNION ALL and using LIKE – both of these show up routinely during Northern Trust SQL assessments.

### Northern Trust Data Science Interview Tips

#### What Do Northern Trust Data Science Interviews Cover?

In addition to SQL interview questions, the other question categories covered in the Northern Trust Data Science Interview are:

#### How To Prepare for Northern Trust Data Science Interviews?

To prepare for Northern Trust Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions sourced from companies like Google, Tesla, & Goldman Sachs
• a crash course on Stats, SQL & ML
• over 1000+ 5-star reviews on Amazon