logo

8 Citigroup SQL Interview Questions (Updated 2024)

Updated on

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

Citigroup SQL Interview Questions

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_idname
100John Doe
200Jane Smith
300David Johnson
Example Input:
transaction_idcustomer_idtransaction_dateamounttype
101010001/05/2021500Deposit
202010001/15/2021200Withdrawal
303020001/10/2021700Deposit
404030002/05/2021300Deposit
505020002/15/2021100Withdrawal
606010002/20/2021200Deposit

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 Interview Questions 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_idsalary
12500
2800
31000
41200

Example Output:

second_highest_salary
1200

Try this interview question and run your code right in DataLemur's online SQL environment:

2nd Highest Salary SQL Interview Question

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.

Citigroup SQL Interview Questions

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_iduser_idaccount_typebalanceyear
4321123Checking350002022
4862265Savings870002022
6872362Credit200002022
4325192Checking450002022
5917981Credit350002021
Example Input:
user_idfirst_namelast_name
123JohnDoe
265JaneSmith
362TomBrown
192AliceJohnson
981BobTaylor

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_idcustomer_idsend_dateopen_statusclick_status
715252308/05/2022OpenedClicked
982721408/08/2022OpenedNot Clicked
431582608/10/2022OpenedClicked
823845008/15/2022Not OpenedNot Clicked
371968208/17/2022OpenedClicked

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

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_namejob_title
AkashData Analyst
BrittanyData Scientist
CarlosData Engineer
DiegoData Engineer
EvaData 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_idfirst_namelast_nameemaillocationaccount_type
001RobertSmithrobert.smith@example.comNew YorkChecking
002RobinsonCruiserobinson.cruise@example.comLos AngelesSavings
003BobMarleybob.marley@example.comMiamiChecking
004JessicaRobinsonjessica.robinson@example.comChicagoSavings
005RobertoCarlosroberto.carlos@example.comHoustonChecking

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). DataLemur Question Bank

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.

DataLemur SQL Tutorial for Data Science

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

Citigroup Data Scientist

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)

Acing Data Science Interview