HSBC Group Data Engineer Interview Questions, Process, and Tips

Q1. What is imputer function in PySpark
Ans. 
Imputer function in PySpark is used to replace missing values in a DataFrame.
• Imputer is a transformer in PySpark ML library.

• It replaces missing values in a DataFrame with either mean, median, or mode of the column.

• It can be used with both numerical and categorical columns.

• Example: imputer = Imputer(inputCols=['col1', 'col2'], outputCols=['col1_imputed', 'col2_imputed'], strategy='mean')

• Example: imputed_df = imputer.fit
Answered by AI
Add your answer
Q2. How do you handles null values in PySpark
Ans. 
Null values in PySpark are handled using functions such as dropna(), fillna(), and replace().
• dropna() function is used to drop rows or columns with null values

• fillna() function is used to fill null values with a specified value or method

• replace() function is used to replace null values with a specified value

• coalesce() function is used to replace null values with the first non-null value in a list of columns
Answered by AI
Add your answer
Q3. SQL query for getting 2nd highest salary from each department
Ans. 
SQL query to retrieve the second highest salary from each department
• Use the RANK() function to assign a rank to each salary within each department

• Filter the results to only include rows with a rank of 2

• Group the results by department to get the second highest salary for each department
Answered by AI
View 1 more answer
Q4. How to delete duplicate rows from a table
Ans. 
To delete duplicate rows from a table, use the DISTINCT keyword or GROUP BY clause.
• Use the DISTINCT keyword to select unique rows from the table.

• Use the GROUP BY clause to group the rows by a specific column and select the unique rows.

• Use the DELETE statement with a subquery to delete the duplicate rows.

• Create a new table with the unique rows and drop the old table.
Answered by AI
Add your answer
Q5. What are the window functions you have used?
Ans. 
Window functions are used to perform calculations across a set of rows that are related to the current row.
• Commonly used window functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTILE.

• Window functions are used in conjunction with the OVER clause to define the window or set of rows to perform the calculation on.

• Window functions can be used to calculate running totals, moving average...read more
Answered by AI
Add your answer
Q6. Merge two unsorted lists such that the output list is sorted. You are free to use inbuilt sorting functions to sort the input lists
Ans. 
Merge two unsorted lists into a sorted list using inbuilt sorting functions.
• Use inbuilt sorting functions to sort the input lists

• Merge the sorted lists using a merge algorithm

• Return the merged and sorted list
Answered by AI
Add your answer

Q1. 1. What is udf in Spark? 2. Write PySpark code to check the validity of mobile_number column
Ans. 
UDF stands for User-Defined Function in Spark. It allows users to define their own functions to process data.
• UDFs can be written in different programming languages like Python, Scala, and Java.

• UDFs can be used to perform complex operations on data that are not available in built-in functions.

• PySpark code to check the validity of mobile_number column can be written using regular expressions and the `regexp_extract` func...read more
Answered by AI
Add your answer

Q1. 1. Salary discussion 2. Why do you want to join HSBC? 3. Tell me a challenge that you faced and how did you overcome it?
Add your answer

Was based on behaviour and cognitive interview round

Q1. Pure technical round based on your skills
Add your answer
Q2. SQL basic and detailed level interview like Joins, Index, performance tunning, Partition etc
Add your answer

Q1. Asked to Fill the Form ad basic details and Current and Expected CTC
Add your answer