11 TANLA SQL Interview Questions (Updated 2024)
Data Analysts and Data Engineers at TANLA write SQL queries almost every single workday. They use SQL for analyzing customer interaction data for optimization and deriving insights from communication trends for business strategy formulation. That's the reason behind why TANLA frequently asks jobseekers SQL coding interview questions.
Thus, to help you prep, here’s 11 TANLA SQL interview questions – able to answer them all?
11 TANLA SQL Interview Questions
SQL Question 1: Identify Power Users at TANLA
The TANLA company has a database containing information about the number of services each customer subscribes to every month. Your task is to write a SQL query to identify power users who subscribe to the services more frequently. A "power user" is defined as a user who subscribes to at least 10 services in a month.
Here are some sample data:
Example Input:
| subscription_id | user_id | subscription_date | service_id |
|---|---|---|---|
| 1023 | 1 | 06/08/2022 | 501 |
| 2011 | 2 | 06/09/2022 | 502 |
| 3281 | 1 | 06/18/2022 | 503 |
| 2012 | 2 | 07/26/2022 | 502 |
| 1024 | 1 | 07/5/2022 | 501 |
| 1025 | 1 | 07/10/2022 | 501 |
| 2013 | 2 | 07/15/2022 | 502 |
| 1026 | 1 | 07/20/2022 | 501 |
| 1027 | 1 | 07/25/2022 | 501 |
| 3282 | 1 | 07/30/2022 | 503 |
| 2014 | 2 | 07/31/2022 | 502 |
Answer:
This query groups the records by user and the month of subscription. The 'HAVING' clause filters out any user who subscribes to fewer than 10 services in a month. The result is then ordered in descending order of the number of subscriptions.
To practice another SQL customer analytics question where you can solve it interactively and have your SQL solution automatically checked, try this Walmart Labs SQL Interview Question:
SQL Question 2: Department vs. Company Salary
You're given a table of TANLA employee and department salaries. Write a SQL query to find departments where the departments average salary is higher than the company's average salary.
Code your solution to this question interactively on DataLemur:
The solution is LONG – 30+ lines of SQL. You can find a step-by-step solution here: Department Salaries.
SQL Question 3: Can you explain the purpose of the constraint and give an example of when you might use it?
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 TANLA 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 4: Ranking Accounts By Revenue Over Time
In TANLA, there is a need to track and display which accounts are generating the most revenue over time, to incentivize top performers.
The business analytics team wants you to write a SQL query that will list all accounts, their total revenue per year, and a ranking of each account by revenue per year.
There will be two tables "Accounts" and "Transactions". The "Accounts" table will list all the accounts with their account_id. The "Transactions" table will track all transactions by "account_id", showing the revenue generated, and the date the revenue was realized.
The output of your query should be:
- Year
- Account ID
- Total Revenue that year
- Their rank that year based on the total revenue
Example Input:
| account_id |
|---|
| 100 |
| 200 |
| 300 |
Example Input:
| transaction_id | account_id | amount | transaction_date |
|---|---|---|---|
| 1 | 100 | 1000 | 2020-01-01 |
| 2 | 100 | 2000 | 2020-06-01 |
| 3 | 200 | 3000 | 2020-07-01 |
| 4 | 200 | 4000 | 2020-11-01 |
| 5 | 300 | 5000 | 2021-01-01 |
| 6 | 300 | 6000 | 2021-06-01 |
| 7 | 200 | 7000 | 2021-07-01 |
| 8 | 100 | 8000 | 2021-11-01 |
Answer:
This query will first group the transactions by their account_id and the year the transaction was made. It will then sum up the total revenue for each group.
The window function is then used to rank these groups within their years, ordered by the descending total revenue. In PostgreSQL, the EXTRACT function is used to get the year from the date.
The result will be a list of each account, along with the total revenue they made each year and their rank that year. The results are then sorted by Year and Revenue Rank.
p.s. Window functions show up super often during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 5: How do relational and non-relational databases differ?
While both types of databases are used to store data (no duh!), relational databases and non-relational (also known as NoSQL databases) differ in a few important ways, most importantly on the way data is stored. Relational databases use a data model consisting of tables and rows, while NoSQL databases use a variety of data models, including document, key-value, columnar, and graph storage formats.
This added flexibilty makes NoSQL databases great for non-tabular data (like hierarchal data or JSON data), or data where the type/format is constantly evolving. With this added flexibility, comes one big weakness – you won't get ACID-compliance. That means, unlike relational databases which are typically adhere to the ACID properties (atomic, consistent, isolated, and durable), you don't get as strong guarentees with most non-relational databases.
SQL Question 6: Filter Customer Database For Latest Transactions
You work at TANLA, a leading cloud communication provider. Your task is to write an SQL query to filter the customer transaction database for every customer’s latest transaction. Assume the date of the transactions is in the format 'YYYY-MM-DD'.
Sample 'transactions' table:
| transaction_id | customer_id | transaction_date | product_id | amount |
|---|---|---|---|---|
| 1501 | 654 | 2022-06-01 | 61001 | 200 |
| 2302 | 789 | 2022-07-05 | 71052 | 300 |
| 1293 | 456 | 2022-06-11 | 61001 | 150 |
| 2752 | 123 | 2022-07-15 | 71052 | 120 |
| 4517 | 654 | 2022-07-02 | 71052 | 280 |
Your SQL query should return a table in the below format:
| customer_id | latest_transaction | product_id | amount |
|---|---|---|---|
| 654 | 2022-07-02 | 71052 | 280 |
| 789 | 2022-07-05 | 71052 | 300 |
| 456 | 2022-06-11 | 61001 | 150 |
| 123 | 2022-07-15 | 71052 | 120 |
Answer:
This SQL query will first group the transactions table by customer ID and pick out the most recent transaction date for each customer (subquery b). It then joins this subquery back onto the original transactions table on both customer ID and transaction date, effectively filtering for each customer's most recent transaction only.
SQL Question 7: Could you clarify the difference between a left and a right join?
In SQL, both a left and right join is used to combine rows from different tables based on a shared key or set of keys, resulting in a single merged table of data. However, here's the difference:
LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
SQL Question 8: Average Time to Resolve a Claim
In Tanla, a cloud communications company, they handle a number of customer claims on a daily basis. You are given a table recording customer , , , etc. Please compute the average time to resolve a claim for every month. The result has to include , and in days. If a claim is not yet resolved, exclude it from the result.
Example Input:
| claim_id | user_id | submit_date | resolved_date |
|---|---|---|---|
| 101 | 2 | 05/05/2022 00:00:00 | 05/07/2022 14:00:00 |
| 102 | 4 | 05/15/2022 10:00:00 | 05/15/2022 16:00:00 |
| 103 | 2 | 06/07/2022 14:00:00 | 06/10/2022 11:00:00 |
| 104 | 3 | 07/01/2022 09:30:00 | |
| 105 | 4 | 07/18/2022 13:00:00 | 07/19/2022 10:00:00 |
Example Output:
| Month | User | Average_time_to_resolve_claim (days) |
|---|---|---|
| 5 | 2 | 2.58 |
| 5 | 4 | 0.25 |
| 6 | 2 | 2.88 |
| 7 | 4 | 0.87 |
Answer:
In this query, function is used to get the month part of the datetime. The function is used to get the average of time differences between submit_date and resolved_date. The difference is translated into days by dividing by 86400 (number of seconds in a day). The clause ensures that only resolved claims are considered. The result is grouped by Month and User.
The two most similar questions are:
-
"Average Review Ratings" - This problem from Amazon is about retrieving average metrics (review ratings) for each product for every month. Like the Tanla question, this requires averaging and date manipulations.
-
"User's Third Transaction" - This Uber problem involves filtering data based on a condition (third ride). It is similar to the Tanla problem, where we need to filter out unresolved claims.
So the markdown-friendly output is:
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for averaging and date manipulations or this Uber User's Third Transaction Question which is similar for filtering data based on a condition.
SQL Question 9: Calculate the Click Through Rate (CTR) for Ads
Tanla Solutions, a cloud communications company, is interested in understanding their ad campaign performance through a metric known as Click Through Rate (CTR). They define CTR as the number of clicks an ad receives divided by the number of times the ad is shown (impressions) expressed as a percentage. Given two tables and , can you write a SQL query to calculate the CTR for each ?
Example Input:
| impression_id | ad_id | impression_date |
|---|---|---|
| 1 | 100 | 06/08/2022 00:00:00 |
| 2 | 101 | 06/10/2022 00:00:00 |
| 3 | 100 | 06/18/2022 00:00:00 |
| 4 | 102 | 07/26/2022 00:00:00 |
| 5 | 101 | 07/05/2022 00:00:00 |
Example Input:
| click_id | ad_id | click_date |
|---|---|---|
| 1 | 100 | 06/09/2022 00:00:00 |
| 2 | 101 | 06/10/2022 00:00:00 |
| 3 | 101 | 06/15/2022 00:00:00 |
| 4 | 100 | 07/28/2022 00:00:00 |
| 5 | 102 | 07/05/2022 00:00:00 |
Answer:
This query first joins the table with the table on . It then groups by and for each , it computes the CTR as the number of clicks divided by the number of impressions multiplied by 100 to convert it to a percentage. Because we've used a LEFT JOIN, ads that have impressions but no clicks will still be included in the result with a CTR of 0.
To practice a related SQL problem on DataLemur's free interactive coding environment, attempt this SQL interview question asked by Facebook:
SQL Question 10: What does database normalization mean?
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 11: Join and Analyze Customer and Product Data
You are given two tables, and , that contain information about customers' purchases at TANLA. The table has columns , , , , and . The table has columns , , , and .
A third table, , links the and table together. It has columns , , and . Write a SQL query to find the total spending of each customer.
table:
| cust_id | name | gender | DOB | location |
|---|---|---|---|---|
| 1 | John Doe | Male | 12/12/1990 | New York |
| 2 | Jane Doe | Female | 07/25/1985 | Los Angeles |
| 3 | Mike Chan | Male | 05/22/1988 | Chicago |
table:
| prod_id | prod_name | category | price |
|---|---|---|---|
| 101 | iPhone | Electronics | 999.99 |
| 102 | MacBook | Electronics | 1299.99 |
| 103 | T-Shirt | Apparel | 19.99 |
table:
| cust_id | prod_id | purchase_date |
|---|---|---|
| 1 | 101 | 02/23/2022 |
| 1 | 103 | 02/23/2022 |
| 2 | 101 | 03/05/2022 |
| 3 | 102 | 01/25/2022 |
| 3 | 103 | 02/22/2022 |
Answer:
This SQL command is using two JOINS to link the , and tables. It's then finding the sum (total) of the prices of the products associated with each customer in the and table, hence indicating total customer spending.
Because join questions come up routinely during SQL interviews, take a stab at this interactive Snapchat SQL Interview question using JOINS:
TANLA SQL Interview Tips
The best way to prepare for a TANLA SQL interview is to practice, practice, practice.
In addition to solving the above TANLA SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Facebook, Google and unicorn tech startups.
Each interview question has multiple hints, step-by-step solutions and crucially, there's an interactive coding environment so you can right in the browser run your query and have it checked.
To prep for the TANLA SQL interview you can also be wise to solve SQL problems from other tech companies like:
But if your SQL coding skills are weak, don't worry about going right into solving questions – strengthen your SQL foundations with this DataLemur SQL Tutorial.
This tutorial covers things like LEAD/LAG window functions and handling date/timestamp data – both of these show up frequently in SQL job interviews at TANLA.
TANLA Data Science Interview Tips
What Do TANLA Data Science Interviews Cover?
In addition to SQL interview questions, the other question categories tested in the TANLA Data Science Interview are:
- Probability & Statistics Questions
- Python Pandas or R Coding Questions
- Data Case Study Questions
- ML Modelling Questions
- Behavioral & Resume-Based Questions
How To Prepare for TANLA Data Science Interviews?
I'm sorta biased, but I believe the best way to study for TANLA Data Science interviews is to read my book Ace the Data Science Interview.
The book solves 201 interview questions sourced from Google, Microsoft & tech startups. It also has a refresher covering SQL, Product-Sense & ML. And finally it's helped thousands of people land their dream job in data, which is why it's got over 1000+ 5-star reviews on Amazon.