Wells Fargo Data Analyst Interview and Answers

My Wells Fargo Data Analyst Interview Experience (1–3 Years)

CTC: 16 LPA

As a data enthusiast and SQL aficionado, I recently tackled some challenging SQL and Python questions in a Wells Fargo interview for a Data Analyst position. The experience was both rewarding and insightful. Here’s how I approached these questions.

---

SQL Questions

1. Identify Inactive Accounts

To identify accounts inactive for more than 12 months:

sql

SELECT AccountID, CustomerID, Balance 
FROM Accounts 
WHERE LastTransactionDate < DATEADD(YEAR, -1, GETDATE());

This query filters accounts where the LastTransactionDate is older than one year.

---

2. Top 3 Accounts by Transaction Volume Per Month

Using ROW_NUMBER() to rank accounts by total transaction volume for each month:

sql

WITH MonthlyVolume AS (
    SELECT 
        AccountID, 
        SUM(Amount) AS TotalVolume, 
        MONTH(TransactionDate) AS TransactionMonth,
        YEAR(TransactionDate) AS TransactionYear
    FROM Transactions
    GROUP BY AccountID, MONTH(TransactionDate), YEAR(TransactionDate)
),
RankedAccounts AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY TransactionYear, TransactionMonth ORDER BY TotalVolume DESC) AS Rank
    FROM MonthlyVolume
)
SELECT AccountID, TotalVolume, TransactionMonth, TransactionYear
FROM RankedAccounts
WHERE Rank <= 3;

This query calculates monthly transaction volumes and selects the top three accounts per month.

---

3. Average Loan Amount for Approved Applications in the Last Six Months

To calculate the average loan amount for approved applications submitted in the past six months:

sql
SELECT AVG(LoanAmount) AS AverageLoanAmount
FROM LoanApplications
WHERE ApprovalStatus = 'Approved' 
  AND ApplicationDate >= DATEADD(MONTH, -6, GETDATE());

The DATEADD function ensures the query only considers recent applications.

---

4. Clustered vs. Non-Clustered Index

A clustered index determines the physical order of data in a table, meaning there can only be one per table. For instance, a clustered index on CustomerID organizes rows by CustomerID.
A non-clustered index, however, is a separate structure that contains pointers to the table's data, allowing for multiple indexes. Use a clustered index for primary keys and non-clustered indexes for frequently queried columns.

---

5. Self-Join Scenario

A self-join is useful for comparing rows within the same table. For example, finding employees earning more than their managers:

sql
SELECT e1.EmployeeID, e1.Salary 
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID
WHERE e1.Salary > e2.Salary;

This approach compares an employee’s salary to their manager’s.

---

Python Questions

6. Convert JSON to a DataFrame

To process a JSON file into a structured DataFrame:

python
import pandas as pd
import json

def json_to_dataframe(file_path):
    with open(file_path, 'r') as file:
        data = json.load(file)
    df = pd.DataFrame(data)
    return df

# Example usage:
df = json_to_dataframe('customers.json')
print(df.head())

---

7. Calculate Moving Average

To compute a moving average for a numerical column:

python
def moving_average(data, column, window_size):
    data[f'{column}_MovingAvg'] = data[column].rolling(window=window_size).mean()
    return data

# Example usage:
df = moving_average(df, 'Sales', 3)
print(df.head())

---

8. Data Validation and Cleaning

Python libraries like pandas simplify validation and cleaning:

python
import pandas as pd

def clean_data(df):
    df = df.drop_duplicates()  # Remove duplicates
    df = df.fillna(method='ffill')  # Handle null values by forward filling
    return df

# Example usage:
cleaned_df = clean_data(raw_df)
print(cleaned_df.info())

---

9. Detect Outliers Using IQR

To identify outliers using the IQR method:

python
def detect_outliers_iqr(data, column):
    Q1 = data[column].quantile(0.25)
    Q3 = data[column].quantile(0.75)
    IQR = Q3 - Q1
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
    return data[(data[column] < lower_bound) | (data[column] > upper_bound)]

# Example usage:
outliers = detect_outliers_iqr(df, 'Amount')
print(outliers)

---

Reflections and Key Takeaways

This interview experience highlighted the importance of solid SQL skills and Python proficiency in real-world data analysis. The key is to:

1. Understand the Problem: Break it down into smaller steps.
2. Optimize Solutions: Ensure queries and scripts are efficient.
3. Demonstrate Versatility: Use various SQL functions and Python libraries effectively.

This hands-on challenge boosted my confidence and clarified how to apply theoretical knowledge to solve practical problems.