9 PB Fintech SQL Interview Questions (Updated 2024)
At PB Fintech, India's largest insurance & lending company, SQL is used frequently for analyzing financial data to gain insights for risk assessment. For this reason PB Fintech typically asks SQL problems in interviews for Data Analytics, Data Science, and Data Engineering jobs.
Thus, to help you practice for the PB Fintech SQL interview, here’s 9 PB Fintech SQL interview questions – able to solve them?
9 PB Fintech SQL Interview Questions
SQL Question 1: Calculate the Cumulative Loan Amount for Each Customer
As a Fintech company, PB Fintech issues loans to its customers. Assuming you have access to a "loans" table with "loan_id", "customer_id", "amount" (representing the loan amount), and "loan_date" columns. Can you write a SQL query to calculate the cumulative loan amount for each customer sorted by the loan_date?
Example Input:
Expected Output:
Answer:
Explanation of the answer:
This query uses a window function to calculate a running total of the loan amount for each customer. The window function calculates a sum that starts at the first row of each partition (where a partition is defined by ) and ends at the current row (as specified by ). The result is a cumulative sum that adds up the loan amounts in the order of the loan date for each customer. Note that the at the end just ensures that the output is sorted first by customer_id and then by loan_date, which may not be necessary depending on the context or the further usage of the resulted data.
Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur
SQL Question 2: Calculate Average Loan Amount by Loan Type
As a data analyst at PB Fintech, you're designing the database schema to keep track of all loan transactions. You have two tables – 'Loans' and 'Loan_Type'. The 'Loans' table keeps a record of all loan transactions, and the 'Loan_Type' table defines all types of loans available.
Your task is to write a SQL query to calculate the average loan amount by loan type.
Example Input:
| loan_id | user_id | loan_type_id | loan_date | loan_amount |
|---|---|---|---|---|
| 1001 | 583 | 1 | 01/04/2022 | 5000 |
| 1002 | 856 | 2 | 02/10/2022 | 15000 |
| 1003 | 233 | 1 | 03/18/2022 | 6500 |
| 1004 | 584 | 2 | 04/26/2022 | 13000 |
| 1005 | 251 | 3 | 05/05/2022 | 8000 |
Example Input:
| loan_type_id | loan_type_name |
|---|---|
| 1 | Personal Loan |
| 2 | Car Loan |
| 3 | Home Loan |
Answer:
This query joins the and tables on the field. It then groups the joined table by from the table, and calculates average for each group. The result is the average loan amount for each type of loan.
SQL Question 3: Are NULLs handled the same as zero's and blank spaces in SQL?
A NULL value represents a missing or unknown value. It is different from a zero or a blank space, which are actual values. It is important to handle NULL values properly in SQL because they can cause unexpected results if not treated correctly.
SQL Question 4: Filter and Analyze Loan Data
As a data analyst at PB Fintech, you are asked to help the loan department to understand their customer profile better by analyzing the loan application data. Specifically, they want to know how many loans have been requested by customers in different age groups within a specified time period who also have a good credit score.
Example Input:
| application_id | user_id | application_date | loan_amount | age | creditscore |
|---|---|---|---|---|---|
| 1001 | 1 | 01/06/2022 | 1000 | 25 | 700 |
| 1002 | 2 | 03/06/2022 | 5000 | 30 | 680 |
| 1003 | 3 | 15/06/2022 | 2000 | 50 | 800 |
| 1004 | 4 | 22/06/2022 | 1500 | 35 | 780 |
| 1005 | 5 | 29/06/2022 | 3000 | 45 | 750 |
| 1006 | 6 | 15/07/2022 | 2500 | 55 | 650 |
| 1007 | 7 | 23/07/2022 | 3500 | 40 | 710 |
| 1008 | 8 | 28/07/2022 | 1000 | 30 | 800 |
| 1009 | 9 | 05/08/2022 | 4000 | 60 | 730 |
| 1010 | 10 | 12/08/2022 | 3500 | 55 | 800 |
The task is to write a SQL query that will return the number of loan applications submitted between June and August of 2022, for applicants aged under 35 with a credit score of at least 700.
Answer:
This query uses the WHERE clause to filter the loan applications based on the application date, age, and credit score. The BETWEEN operator is used to filter the date within the defined range. The AND operator is used to make sure all conditions are satisfied simultaneously. The COUNT function then returns the number of rows that match these conditions.
SQL Question 5: What's the difference between and clause?
The clause is used to filter rows from the result set of a , , or statement. It allows you to specify a condition that must be met for a row to be included in the result set.
The clause is used to filter groups created by the clause. It is similar to the clause, but it is used to specify conditions on the groups created by the clause, rather than on the individual rows of the table.
Say you were working on a social media analytics project for PB Fintech.
Here is an example of a SQL query that you might write which uses both the WHERE and HAVING clauses:
This query retrieves the total impressions and average conversions for each platform in the table, the date of the campaign is in January 2023. The rows are grouped by platform and the clause filters the groups to include only those with more than 5000 impressions and an average conversion rate above 0.2.
SQL Question 6: Average Investment Amount by Sector
PB Fintech is a leading fintech company operating in the field of personal investment and stock trading. Suppose you are tasked with finding the average amount invested in each sector by the users over the year. To solve this, you'll need to use the AVG function to find the average amount from the table, which contains the , , and .
Here is what the table might look like:
Example Input:
| investment_id | user_id | investment_date | amount_invested | sector |
|---|---|---|---|---|
| 8532 | 849 | 01/01/2022 | 1000 | Technology |
| 8760 | 902 | 01/03/2022 | 2500 | Healthcare |
| 9726 | 972 | 01/05/2022 | 8000 | Finance |
| 4024 | 673 | 01/07/2022 | 5000 | Technology |
| 5417 | 562 | 01/09/2022 | 2000 | Finance |
The PostgreSQL query to solve this would be:
Answer:
This query works by grouping the table by the column and applying the AVG function to the column in each group. The result is a table with each sector and its corresponding average investment amount.
To practice a very similar question try this interactive Robinhood Cities With Completed Trades Question which is similar for requiring aggregation in financial context or this Amazon Average Review Ratings Question which is similar for needing average calculations.
SQL Question 7: What does it mean to normalize a database?
To normalize a database, tables are divided into smaller, more specialized ones and relationships between them are defined via primary and foreign keys. This minimizes redundancy, making the database more flexible, scalable, and easier to maintain. Normalization also helps to ensure the accuracy of the data by reducing the likelihood of inconsistencies and errors.
SQL Question 8: Calculating Average Customer Ratings
For the company PB Fintech, you have been provided with two tables - and . The table has details about each customer with columns , , , , and . The table contains information about each customer's rating for the provided services. It has columns: , , , .
The objective is to write a SQL query which will calculate the average rating of each customer for all services in the year 2023 and join the result with the table.
Example Input:
| customer_id | name | phone_number | join_date | |
|---|---|---|---|---|
| 101 | John Doe | +1 800-123-4567 | johndoe@email.com | 2021-05-07 |
| 102 | Jane Smith | +1 800-987-6543 | janesmith@email.com | 2021-12-15 |
| 103 | Mary Johnson | +1 800-234-5678 | maryjohnson@email.com | 2023-01-01 |
Example Input:
| rating_id | customer_id | service_id | rating | rating_date |
|---|---|---|---|---|
| 201 | 101 | 1 | 4 | 2023-01-01 |
| 202 | 102 | 1 | 5 | 2023-01-01 |
| 203 | 103 | 1 | 3 | 2023-01-02 |
| 204 | 101 | 2 | 5 | 2023-01-01 |
| 205 | 102 | 2 | 4 | 2023-01-02 |
Answer:
This query will first join the and tables on the field. It then filters the records to only include those where the year of the is 2023. The function is used to calculate the average rating for each customer, which is then returned in the result along with the and .
Because join questions come up frequently during SQL interviews, practice an interactive Spotify JOIN SQL question:
SQL Question 9: Average Monthly Interest Earned by Customers
PB (PolicyBazaar) Fintech wants to analyze how much, on average, each of its customers is earning monthly in interest from the investments they've made. Help PB Fintech to project this data in a manner that each customer's monthly average interest is rounded to two decimal places and absolute value is considered in case interest rates went negative due to unforeseen market situations.
For this, consider 'investments' table where each row represents an investment made by a customer and 'interest' table that contains the interest earned from these investments over the months.
Example Input:
| investment_id | user_id | investment_date | amount_invested |
|---|---|---|---|
| 100 | 1 | 2021-01-01 | 5000 |
| 101 | 2 | 2021-03-15 | 8000 |
| 102 | 1 | 2021-08-22 | 3000 |
| 103 | 3 | 2021-09-08 | 7500 |
Example Input:
| interest_id | investment_id | month | year | interest_earned |
|---|---|---|---|---|
| 01 | 100 | 1 | 2021 | 250 |
| 02 | 101 | 1 | 2021 | -100 |
| 03 | 100 | 2 | 2021 | 200 |
| 04 | 101 | 2 | 2021 | 400 |
| 05 | 101 | 8 | 2021 | -300 |
| 06 | 102 | 8 | 2021 | 100 |
| 07 | 102 | 9 | 2021 | 200 |
Answer:
This query joins the 'investments' and 'interest' tables using the investment_id as a common link. It then groups the resultant data by user_id, month, and year. For each group, it calculates the average interest earned, takes its absolute value (using the ABS function to handle possible negative values), and rounds it to two decimal places. The results are then ordered by user_id, year, and month to provide a chronological interest earning record for each user.
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for averaging and rounding requirements or this Alibaba Compressed Mean Question which is similar for mean calculation and rounding.
How To Prepare for the PB Fintech SQL Interview
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews.
In addition to solving the earlier PB Fintech SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Google, Uber, and Microsoft.
Each DataLemur SQL question has hints to guide you, detailed solutions and crucially, there is an interactive SQL code editor so you can right in the browser run your SQL query and have it graded.
To prep for the PB Fintech SQL interview you can also be wise to practice SQL problems from other tech companies like:
But if your SQL query skills are weak, don't worry about diving straight into solving questions – go learn SQL with this SQL interview tutorial.
This tutorial covers topics including grouping by multiple columns and WHERE vs. HAVING – both of which pop up frequently in PB Fintech SQL interviews.
PB Fintech Data Science Interview Tips
What Do PB Fintech Data Science Interviews Cover?
In addition to SQL interview questions, the other topics tested in the PB Fintech Data Science Interview are:
- Statistics Interview Questions
- Python Pandas or R Coding Questions
- Business Sense and Product-Sense Questions
- ML Modelling Questions
- Behavioral Interview Questions
How To Prepare for PB Fintech Data Science Interviews?
The best way to prepare for PB Fintech Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from Google, Microsoft & tech startups
- A Refresher on SQL, AB Testing & ML
- Great Reviews (900+ reviews, 4.5-star rating)