logo

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?

Northern Trust SQL Interview Questions

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.

Answer:

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: 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:

Employees Earning More Than Their Manager

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.

Northern Trust SQL Interview Questions

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 1millionineitherindividualaccountsortotalaccountassets,livedin"Illinois"state,andjoinedaftertheyear2015.Additionally,youshouldalsoretrieveclientswhosetotaltransactionamountinthelast6monthsismorethan1 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
324655272022-03-01$75,000

The question will involve using the SQL commands SELECT, FROM, WHERE and AND to filter the clients based on the required criteria.

Answer:


This query joins the clients and transactions tables on the field. It then filters for clients who live in Illinois and joined after 2015, and who have either more than 1millionintheiraccountorhavemadetransactionstotalingmorethan1 million in their account or have made transactions totaling more than 200,000 in the last 6 months.

SQL Question 5: What does do?

The SQL command merges the results of multiple statements and keeps only those rows that are present in all sets.

For example, say you were doing an HR Analytics project for Northern Trust, and had access to Northern Trust's employees and contractors data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all contractors who also show up in the employees table:


SQL Question 6: Calculate The Average Transaction Amount Per Account

As a financial institution, Northern Trust might be interested in finding out the average transaction volume per account over a certain period of time for their analysis.

Let's assume that we have a table named "transactions" that tracks all the transactions for each account. Here is some sample data:

Example Input
transaction_idaccount_idtransaction_datetransaction_amount
1001AB0101/05/2022 00:00:002000
1002AB0201/06/2022 00:00:005000
1003AB0101/07/2022 00:00:007000
1004AB0301/08/2022 00:00:0010000
1005AB0201/09/2022 00:00:005000

To answer this question, we could use the AVG function in PostgreSQL in conjunction with the GROUP BY clause to find out the average transaction amount of an account_id. Our SQL query will look something like this:

Answer:


This query first groups the table by account_id, and then for each group, it calculates the average transaction amount. The result will be a new table where each row corresponds to a unique account_id and has the corresponding average transaction amount.

For example, the average transaction amount for account 'AB01' is calculated by (2000+7000)/2 = 4500, as there are two transactions for this account with amounts 2000 and 7000. Similarly, the average for 'AB02' is (5000+5000)/2 = 5000. And for 'AB03' there is only one transaction with amount 10000 so the average is this amount itself.

Example Output:
account_idavg_transaction_amount
AB014500
AB025000
AB0310000

To practice a very similar question try this interactive Uber User's Third Transaction Question which is similar for dealing with transactions and SQL functions or this Stripe Repeated Payments Question which is similar for dealing with transaction times and SQL queries.

SQL Question 7: Can you explain the difference between and ?

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 Northern Trust 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 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%

Answer:


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: Signup Activation Rate SQL Question

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

Answer:


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.

Answer:


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: Spotify JOIN SQL question

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. DataLemur Questions

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.

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:

Northern Trust Data Scientist

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

Ace the Data Science Interview by Nick Singh Kevin Huo