8 Citigroup SQL Interview Questions (Updated 2024)
Citigroup employees use SQL for analyzing financial transactions for fraud detection and optimizing database systems. So, it shouldn't surprise you that Citigroup almost always asks SQL query questions during interviews for Data Analyst, Data Science, and BI jobs.
As such, to help you prep for the Citigroup SQL interview, here's 8 Citigroup SQL interview questions – scroll down to start solving them!
8 Citigroup SQL Interview Questions
SQL Question 1: Customer Transaction Analysis
Citigroup is interested in the money inflow and outflow of its customers. Specifically, they want to analyze the cumulative total deposit, withdrawal, and net balance for each customer at the end of each month. Below are the tables containing customer details and transaction details:
Example Input:
| customer_id | name |
|---|---|
| 100 | John Doe |
| 200 | Jane Smith |
| 300 | David Johnson |
Example Input:
| transaction_id | customer_id | transaction_date | amount | type |
|---|---|---|---|---|
| 1010 | 100 | 01/05/2021 | 500 | Deposit |
| 2020 | 100 | 01/15/2021 | 200 | Withdrawal |
| 3030 | 200 | 01/10/2021 | 700 | Deposit |
| 4040 | 300 | 02/05/2021 | 300 | Deposit |
| 5050 | 200 | 02/15/2021 | 100 | Withdrawal |
| 6060 | 100 | 02/20/2021 | 200 | Deposit |
In the above table, the indicates whether the transaction is a Deposit (money inflow) or a Withdrawal (money outflow), and the indicates the transaction amount.
Answer:
This SQL query uses window function to perform cumulative sum over the transactions for each customer ordered by date. Deposits are added to the balance, and withdrawals are subtracted from the balance. The DATE_TRUNC('month', transaction_date) function is used to group transactions by month.
Pro Tip: Window functions are a popular SQL interview topic, so practice all the window function problems on DataLemur
SQL Question 2: Second Highest Salary
Given a table of Citigroup employee salaries, write a SQL query to find the 2nd highest salary amongst all the employees.
Citigroup Example Input:
| employee_id | salary |
|---|---|
| 1 | 2500 |
| 2 | 800 |
| 3 | 1000 |
| 4 | 1200 |
Example Output:
| second_highest_salary |
|---|
| 1200 |
Try this interview question and run your code right in DataLemur's online SQL environment:
Answer:
You can find a detailed solution here: 2nd Highest Salary.
SQL Question 3: What is a SQL constraint?
A UNIQUE constraint ensures that all values in a column are different. It is often used in conjunction with other constraints, such as NOT NULL, to ensure that the data meets certain conditions.
For example, if you had Citigroup sales leads data stored in a database, here's some constraints you'd use:
In this example, the UNIQUE constraint is applied to the "email" and "phone" fields to ensure that each Citigroup lead has a unique email address and phone number. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two leads had the same email address or phone number.
SQL Question 4: Calculate the Average Account Balance
As a part of the finance team at Citigroup, you are required to analyze customers' usage of their accounts. Can you write a SQL query to calculate the average account balance for each type of account (Checking, Savings, Credit etc.) for the year 2022?
Example Input:
| account_id | user_id | account_type | balance | year |
|---|---|---|---|---|
| 4321 | 123 | Checking | 35000 | 2022 |
| 4862 | 265 | Savings | 87000 | 2022 |
| 6872 | 362 | Credit | 20000 | 2022 |
| 4325 | 192 | Checking | 45000 | 2022 |
| 5917 | 981 | Credit | 35000 | 2021 |
Example Input:
| user_id | first_name | last_name |
|---|---|---|
| 123 | John | Doe |
| 265 | Jane | Smith |
| 362 | Tom | Brown |
| 192 | Alice | Johnson |
| 981 | Bob | Taylor |
Answer:
This query calculates the average balance for each type of account. It filters out the accounts that are not from the year 2022 with the statement. The statement groups all the same types of accounts together, and the function calculates the average balance for each group of account types.
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages or this Alibaba Compressed Mean Question which is similar for working with finance data.
SQL Question 5: What's the operator do, and can you give an example?
The UNION operator combines the results from multiple SELECT statements into a single result set.
Here's an example of using the UNION operator to combine the results of two SELECT statements that retrieve data from tables of Citigroup's Facebook ads and their Google ads:
This statement would return a combined result set of both Facebook and Google ads that have a spend greater than $200. The result set would include the following columns: ad_id, ad_name, ad_type, impressions, clicks, and spend.
Note that the two SELECT statements within the UNION must have the same number of columns and the columns must have similar data types. In this example, both SELECT statements have the same number of columns and the data types of the columns are all compatible.
Read about Citi Group's digital and data platforms to understand how they interact with their customers.
SQL Question 6: Determine the Click-Through-Rate for Citigroup's Marketing Emails
Assume you are working as a data analyst for Citigroup. Citigroup sends out marketing emails to its customers regularly. They want to analyze the click-through rate (CTR) for these marketing emails. CTR is the percentage of people who clicked on at least one link in the email after opening it.
Table Example Input
| email_id | customer_id | send_date | open_status | click_status |
|---|---|---|---|---|
| 7152 | 523 | 08/05/2022 | Opened | Clicked |
| 9827 | 214 | 08/08/2022 | Opened | Not Clicked |
| 4315 | 826 | 08/10/2022 | Opened | Clicked |
| 8238 | 450 | 08/15/2022 | Not Opened | Not Clicked |
| 3719 | 682 | 08/17/2022 | Opened | Clicked |
We would like to calculate the CTR per day.
Answer
The first WITH clause (opened_emails) counts how many emails were opened each day and the second WITH clause (clicked_emails) counts how many emails were clicked each day. The main query is to calculate the CTR by joining the two derived tables on send_date, and then dividing the clicked count by opened count for each day. The result is a table with the send_date and the CTR for each day. The CTR is calculated as the number of clicked emails divided by the number of opened emails.
To practice a similar problem about calculating rates, solve this SQL interview question from TikTok within DataLemur's interactive SQL code editor:
SQL Question 7: What does the SQL keyword do?
If you want to return records with no duplicates, you can use the keyword in your statement.
For example, if you had a table of Citigroup employees, and wanted to see what are all the unique job titles that currently worked at the company, you could write the following query:
If had the following data:
| f_name | job_title |
|---|---|
| Akash | Data Analyst |
| Brittany | Data Scientist |
| Carlos | Data Engineer |
| Diego | Data Engineer |
| Eva | Data Analyst |
Then the output from the query would be:
| job_title |
|---|
| Data Analyst |
| Data Scientist |
| Data Engineer |
SQL Question 8: Identifying Specific Customer Patterns
Citigroup is interested in identifying all the customers whose names have a specific character pattern. Given the customer records, write a SQL query to find all the customers whose names begin with 'Rob'.
Example Input:
| customer_id | first_name | last_name | location | account_type | |
|---|---|---|---|---|---|
| 001 | Robert | Smith | robert.smith@example.com | New York | Checking |
| 002 | Robinson | Cruise | robinson.cruise@example.com | Los Angeles | Savings |
| 003 | Bob | Marley | bob.marley@example.com | Miami | Checking |
| 004 | Jessica | Robinson | jessica.robinson@example.com | Chicago | Savings |
| 005 | Roberto | Carlos | roberto.carlos@example.com | Houston | Checking |
Answer:
This SQL query will pull all customer records where the first name begins with 'Rob'. The '%' symbol is a wildcard character that matches any sequence of characters. By putting it after 'Rob', we're specifying that we're interested in any string that begins with 'Rob' and has any characters after it. It could be just 'Rob' or something longer like 'Robert' or 'Robbie'. This way we find all variants of names that have 'Rob' as prefix. The output of this query will give us a list of customer records meeting the specified condition.
How To Prepare for the Citigroup SQL Interview
The best way to prepare for a Citigroup SQL interview is to practice, practice, practice.
In addition to solving the earlier Citigroup SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).
Each exercise has multiple hints, fully explained answers along with a discussion board to see how others solved it and crucially, there's an online SQL code editor so you can right in the browser run your SQL query and have it checked.
To prep for the Citigroup SQL interview it is also helpful to practice interview questions from other banking & finanacial services companies like:
But if your SQL coding skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this SQL tutorial for Data Scientists & Analysts.
This tutorial covers SQL concepts such as filtering data with WHERE and using ORDER BY – both of which pop up frequently during SQL job interviews at Citigroup.
Citigroup Data Science Interview Tips
What Do Citigroup Data Science Interviews Cover?
In addition to SQL interview questions, the other types of questions to practice for the Citigroup Data Science Interview include:
- Probability & Statistics Questions
- Python or R Coding Questions
- Product Analytics Questions
- ML Interview Questions
- Behavioral & Resume-Based Questions
How To Prepare for Citigroup Data Science Interviews?
The best way to prepare for Citigroup Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from Facebook, Google & startups
- A Crash Course covering Python, SQL & ML
- Great Reviews (1000+ 5-star reviews on Amazon)