logo

9 PB Fintech SQL Interview Questions (Updated 2024)

Updated on

March 2, 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 Interview Questions 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_iduser_idloan_type_idloan_dateloan_amount
1001583101/04/20225000
1002856202/10/202215000
1003233103/18/20226500
1004584204/26/202213000
1005251305/05/20228000
Example Input:
loan_type_idloan_type_name
1Personal Loan
2Car Loan
3Home 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.

PB Fintech SQL Interview Questions

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_iduser_idapplication_dateloan_amountagecreditscore
1001101/06/2022100025700
1002203/06/2022500030680
1003315/06/2022200050800
1004422/06/2022150035780
1005529/06/2022300045750
1006615/07/2022250055650
1007723/07/2022350040710
1008828/07/2022100030800
1009905/08/2022400060730
10101012/08/2022350055800

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_iduser_idinvestment_dateamount_investedsector
853284901/01/20221000Technology
876090201/03/20222500Healthcare
972697201/05/20228000Finance
402467301/07/20225000Technology
541756201/09/20222000Finance

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_idnamephone_numberemailjoin_date
101John Doe+1 800-123-4567johndoe@email.com2021-05-07
102Jane Smith+1 800-987-6543janesmith@email.com2021-12-15
103Mary Johnson+1 800-234-5678maryjohnson@email.com2023-01-01
Example Input:
rating_idcustomer_idservice_idratingrating_date
201101142023-01-01
202102152023-01-01
203103132023-01-02
204101252023-01-01
205102242023-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 join question from Spotify

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_iduser_idinvestment_dateamount_invested
10012021-01-015000
10122021-03-158000
10212021-08-223000
10332021-09-087500
Example Input:
interest_idinvestment_idmonthyearinterest_earned
0110012021250
0210112021-100
0310022021200
0410122021400
0510182021-300
0610282021100
0710292021200

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. DataLemur SQL and Data Science Interview Questions

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.

DataLemur SQL Tutorial for Data Science

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

PB Fintech Data Scientist

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)

Acing Data Science Interview