8 Intercontinental Exchange SQL Interview Questions (Updated 2024)
Intercontinental Exchange employees write SQL queries daily for analyzing trading data for insights and managing customer information in their financial databases. That's why Intercontinental Exchange typically asks SQL questions during interviews for Data Science and Data Engineering positions.
Thus, to help you prep, here’s 8 Intercontinental Exchange SQL interview questions – can you answer each one?
8 Intercontinental Exchange SQL Interview Questions
SQL Question 1: Analyzing Trading Volumes Across Securities and Dealers
Intercontinental exchange, as a global network of exchanges for commodity and equity trading, maintains an extensive database of trading information. Suppose you are asked to analyze the trading volume (the total quantity of securities traded) across different securities and dealers.
Table: Example Input
| trade_id | security_id | dealer_id | trade_date | quantity |
|---|---|---|---|---|
| 1 | 20 | 100 | 2022-10-01 | 500 |
| 2 | 30 | 100 | 2022-10-01 | 1000 |
| 3 | 20 | 101 | 2022-10-01 | 200 |
| 4 | 30 | 101 | 2022-10-01 | 1500 |
| 5 | 20 | 100 | 2022-10-02 | 700 |
| 6 | 30 | 100 | 2022-10-02 | 500 |
| 7 | 20 | 101 | 2022-10-02 | 800 |
| 8 | 30 | 101 | 2022-10-02 | 1000 |
In the above table, each row represents a trade. The field is the quantity of the security (identified by ) traded by the dealer (identified by ) on .
Your task is to write a SQL query that calculates the cummulative trading volume per security, for each dealer, sorted by dates. The output should include columns for dealer_id, security_id, trade_date and the calculated cummulative volume. For this problem, assume that trade dates are sequential and that there are no missing dates.
Answer
In the above SQL query, window function is used to calculate the cumulative sum of quantity (trading volume) for each and combination, ordered by . By not specifying an end to the window frame with , the query calculates the sum at each row (inclusive) from the first row of the window frame.
For more window function practice, solve this Uber SQL problem within DataLemur's online SQL code editor:
SQL Question 2: Department Salaries
You're given a table of Intercontinental Exchange employee and department salaries. Write a SQL query to find departments where the departments average salary is higher than the company's average salary.
Write a SQL query for this problem and run your code right in DataLemur's online SQL environment:
The answer is LONG – 30+ lines of SQL. You can find a detailed solution with hints here: Department vs. Company Salary.
Read about Intercontinental Exchange's history!
SQL Question 3: What's the difference between a foreign and primary key?
To explain the difference between a primary key and foreign key, let's start with an example Intercontinental Exchange sales database:
:
+------------+------------+------------+------------+ | order_id | product_id | customer_id| quantity | +------------+------------+------------+------------+ | 1 | 303 | 1 | 2 | | 2 | 404 | 1 | 1 | | 3 | 505 | 2 | 3 | | 4 | 303 | 3 | 1 | +------------+------------+------------+------------+
In this table, could be a primary key. It is unique for each row in the table and cannot contain null values.
and could both be foreign keys. They reference the primary keys of other tables, such as a Products table and a Customers table, respectively. This establishes a relationship between the table and the other tables, such that each row in the sales database corresponds to a specific product and a specific customer.
SQL Question 4: Financial Transactions Analysis
Intercontinental Exchange (ICE) is a Fortune 500 company that operates global exchanges and clearinghouses and provides mortgage technology, data, and listings services. They deal with vast quantities of data about market participants' transactions.
Below, consider two tables in the PostgreSQL database: and .
table:
| transaction_id | company_id | transaction_date | transaction_amount |
|---|---|---|---|
| 1 | 251 | 2022-01-01 | 100,000 |
| 2 | 362 | 2022-02-15 | 50,000 |
| 3 | 362 | 2022-04-22 | 75,000 |
| 4 | 584 | 2022-01-18 | 125,000 |
| 5 | 251 | 2022-03-08 | 80,000 |
table:
| company_id | company_name | industry_sector |
|---|---|---|
| 251 | Apple Inc. | Technology |
| 362 | Exxon Mobil | Energy |
| 584 | The Coca-Cola Company. | Food |
The business query ICE wants to solve is: For each industry sector, what are the total transaction amounts and count of transactions completed in Q1 of the year 2022 (i.e., from 2022-01-01 to 2022-03-31)?
Assuming the values in the date column are of 'date' data type, the following SQL query will solve the problem:
This query will join the table with the table using the column. It will then filter transactions made in Q1 2022, count the number of transactions, and calculate the total amount for each in the table.
SQL Question 5: What are the various forms of normalization?
Normalization is the process of organizing fields and tables of a database to minimize redundancy and dependency. While there are technically 5 levels (normal forms), the 3 most important normal forms you need to know about for SQL interviews at Intercontinental Exchange are:
- First Normal Form (1NF): This should fix remove a table's duplicate columns. Also, each column should contain only a single value (no lists or containers of data), and finally each row of table should have a unique identifier as well.
- Second Normal Form (2NF): A table is in its second normal form if it meets all requirements of the first normal form and places the subsets of columns in separate tables. The relationships between tables are created using primary/foreign keys.
- Third Normal Form (3NF): The table should be in the second normal form. There should be no dependency on another non-key attribute (meaning a primary key should be the only thing required to identify the row).
SQL Question 6: Calculate the Clickthrough Conversion Rates
Intercontinental Exchange is a company that provides marketplaces for trading and clearing a broad array of financial and commodity contracts. As part of their ongoing efforts to improve and optimize their online platform, they are interested in the click-through conversion rate of users from viewing an exchange product to adding the exchange product to a follow-up list.
To facilitate this, 2 data tables are provided: and . The table logs each event when a user views an exchange product, and the table records each event a user adds an exchange product to a follow-up list for further monitoring.
The task is to calculate the monthly click-through conversion rate, which is defined as the number of exchange products added to follow-up lists per product view.
Example Input:
| view_id | user_id | view_date | exchange_product_id |
|---|---|---|---|
| 6452 | 587 | 06/08/2022 00:00:00 | 60005 |
| 7635 | 432 | 06/10/2022 00:00:00 | 71053 |
| 5126 | 671 | 06/18/2022 00:00:00 | 60005 |
| 6789 | 198 | 07/26/2022 00:00:00 | 71053 |
| 4137 | 798 | 07/05/2022 00:00:00 | 71053 |
Example Input:
| followup_id | user_id | add_date | exchange_product_id |
|---|---|---|---|
| 6521 | 587 | 06/08/2022 00:00:00 | 60005 |
| 7892 | 432 | 06/10/2022 00:00:00 | 71053 |
| 5321 | 671 | 07/26/2022 00:00:00 | 60005 |
| 6892 | 198 | 07/26/2022 00:00:00 | 71053 |
| 4257 | 798 | 07/05/2022 00:00:00 | 71053 |
Answer:
This query first calculates the monthly views and additions for each exchange product in the CTEs and . Then it joins these two tables on month and exchange product id and calculates the clickthrough conversion rate as the number of additions divided by the number of views. The keyword 'NULLIF' guard against division by zero errors.
To practice a similar problem about calculating rates, try this TikTok SQL question within DataLemur's interactive coding environment:
SQL Question 7: Can you describe the difference between a clustered and a non-clustered index?
Here is an example of a clustered index on the column of a table of Intercontinental Exchange customer transactions:
This creates a clustered index on the column, which will determine the physical order of the data rows in the table.
Here is an example of a non-clustered index on the column of the same table:
This will create a non-clustered index on the column, which will not affect the physical order of the data rows in the table.
In terms of query performance, a clustered index is usually faster for searches that return a large number of records, while a non-clustered index is faster for searches that return a small number of records. However, updates to a clustered index are slower, as they require the data rows to be physically rearranged, while updates to a non-clustered index are faster, as they only require the index data structure to be updated.
SQL Question 8: Identify customers from a specific location
Intercontinental Exchange, as a financial market company, had customers globally. They often organise marketing events in specific cities and would like to invite their local customers. For this purpose, they need to find all clients based in a specific location.
You are provided with the following table named detailing their information:
Example Input:
| customer_id | first_name | last_name | city | register_date | |
|---|---|---|---|---|---|
| 1875 | John | Doe | john.doe@ice.com | New York | 01/30/2021 |
| 2352 | Jane | Smith | jane.smith@ice.com | Atlanta | 03/15/2021 |
| 2698 | Peter | Parker | peter.parker@ice.com | London | 04/20/2021 |
| 4529 | Jenny | White | jenny.white@ice.com | New York | 07/26/2021 |
| 5321 | Bill | Gates | bill.gates@ice.com | Seattle | 08/31/2021 |
The task is to write a SQL query to find all customers who are located in New York.
Answer:
Typically, we would use the keyword in PostgreSQL to filter our query based on a specific pattern in a string. However, for this case, we're looking for an exact match, so we can simply use the operator.
This SQL query selects all records from the customers table where the city field matches 'New York'.
Example Output:
| customer_id | first_name | last_name | city | register_date | |
|---|---|---|---|---|---|
| 1875 | John | Doe | john.doe@ice.com | New York | 01/30/2021 |
| 4529 | Jenny | White | jenny.white@ice.com | New York | 07/26/2021 |
Intercontinental Exchange SQL Interview Tips
The key to acing a Intercontinental Exchange SQL interview is to practice, practice, and then practice some more!
Beyond just solving the earlier Intercontinental Exchange SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).
Each SQL question has multiple hints, step-by-step solutions and crucially, there is an online SQL coding environment so you can instantly run your SQL query and have it graded.
To prep for the Intercontinental Exchange SQL interview it is also useful to solve interview questions from other stock exchange & brokerage companies like:
But if your SQL skills are weak, forget about jumping right into solving questions – go learn SQL with this DataLemur SQL Tutorial.
This tutorial covers things like SUM/AVG window functions and filtering data with WHERE – both of which come up frequently in SQL interviews at Intercontinental Exchange.
Intercontinental Exchange Data Science Interview Tips
What Do Intercontinental Exchange Data Science Interviews Cover?
Beyond writing SQL queries, the other question categories covered in the Intercontinental Exchange Data Science Interview include:
- Statistics and Probability Questions
- Python Pandas or R Coding Questions
- Data Case Study Questions
- Machine Learning Questions
- Resume-Based Behavioral Questions
How To Prepare for Intercontinental Exchange Data Science Interviews?
I'm sorta biased, but I believe the best way to prep for Intercontinental Exchange Data Science interviews is to read the book Ace the Data Science Interview.
The book has 201 interview questions sourced from FAANG, tech startups, and Wall Street. It also has a crash course on Stats, ML, & Data Case Studies. And finally it's vouched for by the data community, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.