8 360 DigiTech SQL Interview Questions (Updated 2024)
At 360 DigiTech, SQL is often used for analyzing financial data patterns and managing vast client databases. For this reason 360 DigiTech often tests SQL query questions in interviews for Data Science, Analytics, and & Data Engineering jobs.
So, to help you ace the 360 DigiTech SQL interview, here’s 8 360 DigiTech SQL interview questions – able to answer them all?
8 360 DigiTech SQL Interview Questions
Sure, here's an example of a PostgreSQL question using a window function.
SQL Question 1: Calculate Loan Payment Rank for Each Month and Customer
360 DigiTech is a fintech company that offers loans to its customers. To understand the customers' loan payment behavior, the company is interested in finding out the cumulative amount paid by each customer every month. The database contains a table named that records all payments made by customers.
Below is a sample of the data:
Example Input:
| payment_id | customer_id | date | amount |
|---|---|---|---|
| 1 | 1001 | 01/05/2022 | 2000 |
| 2 | 1002 | 01/15/2022 | 1500 |
| 3 | 1001 | 02/10/2022 | 2500 |
| 4 | 1003 | 02/05/2022 | 1000 |
| 5 | 1001 | 03/01/2022 | 3000 |
| 6 | 1002 | 03/20/2022 | 1000 |
| 7 | 1001 | 04/05/2022 | 2000 |
| 8 | 1002 | 04/15/2022 | 1500 |
They want a report that shows the cumulative amount paid by each customer on a monthly basis. For example, if a customer makes multiple payments in a month, the amount should sum up for that month. The data for the report should be presented in the ascending order of the date when the payment was made.
Using PostgreSQL, write a SQL query that can fulfill this requirement.
Example Output:
| month | customer_id | cumulative_amount |
|---|---|---|
| 1 | 1001 | 2000 |
| 1 | 1002 | 1500 |
| 2 | 1001 | 4500 |
| 2 | 1003 | 1000 |
| 3 | 1001 | 7500 |
| 3 | 1002 | 2500 |
| 4 | 1001 | 9500 |
| 4 | 1002 | 4000 |
Answer:
This query uses the window function to create a running total of the amounts paid by each customer, partitioned by customer id and the month of the date of payment. The clause indicates that all rows from the start of the partition up to and including the current row should be used to calculate the running sum. The function is used to extract the month part from the date. Finally, the query orders the results by date in ascending order.
To practice a related window function SQL problem on DataLemur's free online SQL code editor, try this Google SQL Interview Question:
SQL Question 2: Consumer Loan Database Design and Query
360 DigiTech offers a variety of financial products, including consumer loans. For any given loan, they store information about the loan conditions, the consumer who took the loan and their repayment status.
Design a database schema for better understanding of their customers' repayment habits.
Specifically, they're interested in figuring out, for a specific month say 'August 2022', the total number of distinct customers who have delayed their loan repayment by more than 30 days.
Assume the following tables:
table:
| loan_id | consumer_id | date_issued | loan_amount |
|---|---|---|---|
| 1001 | 5000 | 2022-04-01 | 5000 |
| 1002 | 5001 | 2022-05-02 | 7500 |
| 1003 | 5002 | 2022-06-05 | 3000 |
| 1004 | 5003 | 2022-07-20 | 6000 |
table:
| loan_id | due_date | repayment_date |
|---|---|---|
| 1001 | 2022-05-01 | 2022-05-10 |
| 1001 | 2022-06-01 | 2022-06-06 |
| 1001 | 2022-07-01 | Null |
| 1002 | 2022-06-02 | 2022-06-02 |
| 1002 | 2022-07-02 | 2022-07-30 |
| 1003 | 2022-07-05 | 2022-07-08 |
| 1004 | 2022-08-20 | Null |
Answer:
Using PostgreSQL, the following query can be used to find the distinct customers with repayment delays of more than 30 days in 'August 2022':
This SQL query works by joining the table with the table on the common . It then filters rows for payments due in August where the repayment date is either NULL (meaning no payment was made) or the is more than 30 days past the . The part counts the distinct 'consumer_id's, representing the distinct customers who have delayed their repayment by more than 30 days.
SQL Question 3: Can you explain the purpose of the SQL constraint?
The UNIQUE constraint is used to ensure the uniqueness of the data in a column or set of columns in a table. It prevents the insertion of duplicate values in the specified column or columns and helps to ensure the integrity and reliability of the data in the database.
For example, say you were on the Marketing Analytics team at 360 DigiTech and were doing some automated keyword research:
Your keyword database might store SEO data like this:
In this example, the UNIQUE constraint is applied to the "keyword" field to ensure that each keyword is unique. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two rows had the same keyword.
SQL Question 4: Find Customers with Similar Addresses
360 DigiTech is interested in finding customers that have similar or like-addresses to enhance potential market analysis. The addresses are stored in the customer database with columns "customer_id", "first_name", "last_name", and "address". Find all records for customers whose addresses contain the word "Street".
Provided below is an example of a table:
Example Input:
| customer_id | first_name | last_name | address |
|---|---|---|---|
| 4578 | John | Doe | 123 Maple Street |
| 3742 | Mary | Smith | 678 Pine Avenue |
| 7685 | James | Johnson | 456 Oak Street |
| 6243 | Patricia | Williams | 321 Birch Street |
| 5296 | Robert | Jones | 135 Aspen Avenue |
For this question, we expect the output table to include only those rows where the addresses contain the word "Street".
Expected Output:
| customer_id | first_name | last_name | address |
|---|---|---|---|
| 4578 | John | Doe | 123 Maple Street |
| 7685 | James | Johnson | 456 Oak Street |
| 6243 | Patricia | Williams | 321 Birch Street |
Answer:
Your PostgreSQL query would need to utilise the LIKE keyword. Here's an example:
This query will return all rows from the customers table where the address contains "Street". The "%" sign is a wildcard character that matches any sequence of characters. So, '%Street%' will match any string that contains "Street".
SQL Question 5: What's the difference between and ?
The clause is used to filter the groups created by the clause. It's 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.
For example, say you were analyzing salaries for analytics employees at 360 DigiTech:
This query retrieves the total salary for each Analytics department at 360 DigiTech and groups the rows by the specific department (i.e. "Marketing Analytics", "Business Analytics", "Sales Analytics" teams).
The clause then filters the groups to include only 360 DigiTech departments where the total salary is greater than $1 million
SQL Question 6: Calculate statistical metrics for loan amounts
360 DigiTech provides various financial products, including loans, to its customers. One possible question could be to calculate statistical metrics on the amounts of these loans, such as the average loan amount, the square root of loan amounts, the percentage changes between subsequent loans, and rounding these amounts. Use ABS() to get the absolute value of changes, ROUND() to round loan amounts to the nearest hundred, SQRT() to calculate square root of loan amount, MOD() to find leftover when amount divided by 1000, POWER() to raise the loan amount to a certain power.
Sample Input:
| loan_id | customer_id | loan_date | loan_amount |
|---|---|---|---|
| 223 | 456 | 2021-01-03 | 2500 |
| 224 | 456 | 2021-05-01 | 3200 |
| 225 | 789 | 2021-02-14 | 4000 |
| 226 | 789 | 2021-06-01 | 4500 |
| 227 | 123 | 2021-03-25 | 1000 |
Sample Output:
| customer_id | average_loan_amount | sqrt_of_loan_amount_1st_loan | abs_change_in_loan | rounded_loan_amount_2nd_loan | modulus_1000_1st_loan | power_3_2nd_loan |
|---|---|---|---|---|---|---|
| 456 | 2850.00 | 50.00 | 700.00 | 3200 | 500 | 32768000000 |
| 789 | 4250.00 | 63.25 | 500.00 | 4500 | 0 | 91267300000 |
| 123 | 1000.00 | 31.62 | N/A | N/A | 0 | N/A |
Answer:
This query first calculates the average loan amount for each customer, then uses window functions to calculate mathematics functions like square root, absolute difference, rounding, modulus, and exponentiation on the customer's loans. The clause is used to apply calculations to specific loans of the customer ("first" and "last" loan as per order of loan_date).
SQL Question 7: How does the constraint function, and in what scenarios might it be useful?
The CHECK constraint is used to set a rule for the data in a column. If a row is inserted or updated and the data in the column does not follow the rule specified by the CHECK constraint, the operation will be unsuccessful.
For example, say you had 360 DigiTech customer data. You could use a CHECK constraint to ensure that the email column contains only properly formatted email addresses, or that the age column contains only positive integer. Here's an example of that:
SQL Question 8: Find the Daily Loan Application Count
360 DigiTech, being a digital platform, provides various financial services including loans. As a data analyst in the company, you are assigned a task of generating a report that shows the total count of loan applications each day.
For the same, you are provided with the table which has the following columns:
- : ID of the application (unique)
- : ID of the user who submitted the application
- : timestamp when the application was submitted.
The table is populated as below:
Example Input:
Write a PostgreSQL query to fetch the date and the total count of applications submitted each day.
Answer:
In this query, we are extracting the date from the and then grouping the data by this date. We count the number of for each group (which is the total count of applications submitted each day) and order the result by the application date. This query will provide the daily loan application submission count for the company.
How To Prepare for the 360 DigiTech SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the 360 DigiTech SQL interview is to solve as many practice SQL interview questions as you can!
Besides solving the earlier 360 DigiTech SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like FAANG tech companies and tech startups.
Each SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and best of all, there is an online SQL code editor so you can easily right in the browser your query and have it executed.
To prep for the 360 DigiTech SQL interview it is also wise to solve SQL problems from other tech companies like:
However, if your SQL coding skills are weak, forget about jumping right into solving questions – strengthen your SQL foundations with this free SQL tutorial.
This tutorial covers things like rank window functions and LEAD/LAG – both of these pop up often in 360 DigiTech interviews.
360 DigiTech Data Science Interview Tips
What Do 360 DigiTech Data Science Interviews Cover?
In addition to SQL interview questions, the other types of problems tested in the 360 DigiTech Data Science Interview are:
- Probability & Stats Questions
- Python or R Coding Questions
- Open-Ended Data Case Studies
- ML Interview Questions
- Resume-Based Behavioral Questions
How To Prepare for 360 DigiTech Data Science Interviews?
The best way to prepare for 360 DigiTech 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 Stats, SQL & ML
- Amazing Reviews (900+ 5-star reviews on Amazon)