PayPal Data Analyst Interview Experience: CTC

A data analyst interview can be a crucial step in landing your dream job in the analytics field. Employers look for candidates with strong analytical skills, proficiency in tools like SQL, Excel, and Python, and the ability to interpret data to drive business decisions. In this guide, we’ll cover the most common data analyst interview questions, essential skills, and tips to help you prepare effectively.

Table of Contents

Question 1: Final Account Balance

Write a SQL query to retrieve the final account balance for each account by calculating the net amount from deposits and withdrawals.

Input Table:
• transactions table:
– transaction_id (integer)
– account_id (integer)
– amount (decimal)
– transaction_type (varchar)

WITH data AS (
        SELECT account_id,
            SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE 0 END) AS deposit,
            SUM(CASE WHEN transaction_type = 'withdrawal' THEN amount ELSE 0 END) AS withdrawal
        FROM transactions
        GROUP BY account_id
    )
SELECT account_id, deposit - withdrawal AS balance
FROM data;

+------------+---------+
| account_id | balance |
+------------+---------+
|        101       |     600 |
|        102       |     900 |
|        103       |     550 |
+------------+---------+

Live Query

https://www.db-fiddle.com/f/e65DU8aPERuQjaQpfGiowQ/0

Explanation

This query retrieves the final balance of accounts by subtracting the total withdrawals from total deposits.

We used CASE statements with GROUP BY account_id in a CTE to find the total deposits and withdrawals for each account. This ensures that we calculate the total deposits and withdrawals in separate columns for each account.

In the main query we selected account_id and subtracted the total withdrawals from total deposits, this give us the balance for each account.

Question 2: Average Transaction Amount per User

Write a SQL query to compute the average transaction amount for each user and rank the users in descending order based on their average transaction amount.

Input Table:
• transactions table:
– transaction_id (integer)
– user_id (integer)
– transaction_date (date)
– amount (decimal)

SELECT
    user_id,
    ROUND(AVG(amount), 2) AS avg_amount,
    DENSE_RANK() OVER (ORDER BY AVG(amount) DESC) AS rnk
FROM
    transactions
GROUP BY
    user_id;

+---------+------------+-----+
| user_id | avg_amount | rnk |
+---------+------------+-----+
|     102 |     850.00       |   1 |
|     104 |     850.00       |   1 |
|     101 |     400.00       |   2 |
|     103 |     350.00       |   3 |
+---------+------------+-----+

Live Query

https://onecompiler.com/mysql/43by3q3ca

Explanation

This query calculates the average transaction amount per user and ranks users based on their average transaction amount in descending order.

We selected the user_id and amount from transactions table and used avg() to find the average transaction amount per user we also used round() with 2 decimal points for better representation of the average amount.

To rank the users, we are using DENSE_RANK() it assigns ranking without skipping numbers if there is a tie which helps in avoiding any confusion in the result. Ordering by AVG(amount) DESC ensures users with higher average transactions get a higher rank.

We grouped the result by user_id, this ensures that result shows average amount and ranking for respective user_id.

Additional info: Unlike RANK(), which skips numbers when there’s a tie, DENSE_RANK() ensures continuous ranking without gaps, making it easier to interpret the results.

Question 3: Unique Money Transfer Relationships

Write a SQL query to determine the number of unique two-way money transfer relationships, where a two-way relationship is established if a user has sent money to another user and also received money from the same user.

Input Table:
• payments table:
– payer_id (integer)
– recipient_id (integer)
– amount (integer)

SELECT COUNT(*) FROM (
    SELECT DISTINCT 
        LEAST(payer_id, recipient_id) AS payer_id, 
        GREATEST(payer_id, recipient_id) AS recipient_id
    FROM payments p1 
    WHERE EXISTS (
        SELECT 1 FROM payments p2 
        WHERE p1.payer_id = p2.recipient_id 
        AND p1.recipient_id = p2.payer_id
    )
) AS temp;

+------------------+
| unique_2_way_rel |
+------------------+
|                4 |
+------------------+

Live Query

https://onecompiler.com/mysql/43by48vpv

Explanation

We aim to identify two-way money transfer relationships – cases where a user has both sent and received money from another user.

We performed a self-join on the payments table to find the payer recipient pairs that have transactions in both directions. Using p1.payer_id = p2.recipient_id and p1.recipient_id = p2.payer_id. This ensures if user a has sent money to user b, there is also a corresponding record where user b has sent money to user a.

To avoid duplicate counting of the same relationship, (e.g. 1, 2 and 2, 1). We normalize the pair representation using least and greatest, least always picks the smaller id first and greatest always picks the larger id first. This ensures (1, 2) and (2, 1) are treated as the same pair.

Finally in the outer query we are counting the resulting records.

Question 4: Determining High-Value Customers

Write a SQL query to identify users who, in the last month, have either sent payments over 1000 or received payments over 5000, excluding those flagged as fraudulent.

SELECT t.user_id,
    SUM(CASE WHEN t.transaction_type = 'sent' THEN t.amount ELSE 0 END) AS sent,
    SUM(CASE WHEN t.transaction_type = 'received' THEN t.amount ELSE 0 END) AS received
FROM transactions t INNER JOIN users u USING (user_id)
WHERE u.is_fraudulent = FALSE AND t.transaction_date >= '2025-02-01' AND t.transaction_date <= '2025-02-28'
GROUP BY t.user_id
HAVING sent > 1000 OR received > 5000;

+---------+---------+----------+
| user_id | sent       | received |
+---------+---------+----------+
|       1    | 1500.00 |  7000.00 |
|       2    | 1200.00 |  6000.00 |
|       5    |  800.00  |  5200.00 |
+---------+---------+----------+

Live Query

https://onecompiler.com/mysql/43c89d65h

Explanation

This query aims to find the user who have eighter sent >$1000 or received >$5000 in the last month (Feb) assuming current date is 2025-03-19.

We have joined the transactions and users table using user_id, while joining the tables we have used condition u.is_fraudulent = FALSE to exclude the users who are flagged as fraudulent and a date filter to exclude the transactions not made in the month of February.

To find the total sent and received amount we have used a combination of sum and case statement and grouped by user_id, this gives total amount sent and received by each user.

Once grouped we are using HAVING sent > 1000 OR received > 5000 to exclude the user who have neither sent over $1000 or received over $5000.

Question 5: Analyzing User Transaction Data

Write a SQL query that calculates the total and average transaction amount for each user, including only those users who have made at least two transactions.

Input Tables:
• Users table:
– user_id (integer)
– signup_date (date)

• Transactions table:
– transaction_id (integer)
– user_id (integer)
– transaction_date (date)
– transaction_amount (decimal)

Select user_id, 
sum(transaction_amount) as total_tr_amount,
round(avg(transaction_amount),2) as avg_tr_amount
From  Transactions
Group by user_id
Having count(user_id)>1;

+---------+-----------------+---------------+
| user_id | total_tr_amount | avg_tr_amount |
+---------+-----------------+---------------+
|       1    |          300.50       |        150.25       |
|       2    |          450.75       |        225.38       |
|       3    |          750.25       |        375.13       |
|       4    |          500.00       |        250.00       |
+---------+-----------------+---------------+

Live Query

https://onecompiler.com/mysql/43cq8ucga

This query aims to retrieve total and average amount of transaction for each user_id with a condition that calculation should be done only for the users who have made at least 2 transactions.

We selected user_id, sum(transaction_amount) and avg(transaction_amount) then we grouped this by user_id and used a having filter count(user_id)>1, this ensures that only those users are included in the calculation who have made at least 2 transactions.

We hope this article was helpful for you and you learned a lot about data analyst interview from it. If you have friends or family members who would find it helpful, please share it to them or on social media.

Join our social media for more.

Join us on Telegram

Follow our WhatsApp Channel

Also Read:

Vishal

Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.

Spread the love