logo

8 Intercontinental Exchange SQL Interview Questions (Updated 2024)

Updated on

June 30, 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?

Intercontinental Exchange SQL Interview Questions

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_idsecurity_iddealer_idtrade_datequantity
1201002022-10-01500
2301002022-10-011000
3201012022-10-01200
4301012022-10-011500
5201002022-10-02700
6301002022-10-02500
7201012022-10-02800
8301012022-10-021000

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:

Uber Window Function SQL Interview Question

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:

Department vs. Company Salary

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.

Intercontinental Exchange SQL Interview Questions

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_idcompany_idtransaction_datetransaction_amount
12512022-01-01100,000
23622022-02-1550,000
33622022-04-2275,000
45842022-01-18125,000
52512022-03-0880,000

table:

company_idcompany_nameindustry_sector
251Apple Inc.Technology
362Exxon MobilEnergy
584The 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:

  1. 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.
  2. 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.
  3. 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_iduser_idview_dateexchange_product_id
645258706/08/2022 00:00:0060005
763543206/10/2022 00:00:0071053
512667106/18/2022 00:00:0060005
678919807/26/2022 00:00:0071053
413779807/05/2022 00:00:0071053
Example Input:
followup_iduser_idadd_dateexchange_product_id
652158706/08/2022 00:00:0060005
789243206/10/2022 00:00:0071053
532167107/26/2022 00:00:0060005
689219807/26/2022 00:00:0071053
425779807/05/2022 00:00:0071053

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: TikTok SQL question

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_idfirst_namelast_nameemailcityregister_date
1875JohnDoejohn.doe@ice.comNew York01/30/2021
2352JaneSmithjane.smith@ice.comAtlanta03/15/2021
2698PeterParkerpeter.parker@ice.comLondon04/20/2021
4529JennyWhitejenny.white@ice.comNew York07/26/2021
5321BillGatesbill.gates@ice.comSeattle08/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_idfirst_namelast_nameemailcityregister_date
1875JohnDoejohn.doe@ice.comNew York01/30/2021
4529JennyWhitejenny.white@ice.comNew York07/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). DataLemur Questions

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.

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

Intercontinental Exchange Data Scientist

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.

Ace the DS Interview