8 TOTVS SQL Interview Questions (Updated 2024)
At TOTVS, SQL is used frequently for analyzing and managing customer data for their portfolio of enterprise software solutions. That's why TOTVS almost always evaluates jobseekers on SQL problems in interviews for Data Analyst, Data Science, and BI jobs.
Thus, to help you practice for the TOTVS SQL interview, we've curated 8 TOTVS SQL interview questions – able to solve them?
8 TOTVS SQL Interview Questions
SQL Question 1: Identifying VIP Customers for TOTVS
TOTVS is a Brazilian software company that specializes in the development of enterprise software. They have a large customer base frequently purchasing their software packages and also utilise their aftersale services. They consider a customer as a VIP or 'whale' customer if they meet both of the following two conditions:
- They give more than 50 transactions per month.
- The total value of their transactions exceeds $10,000 in a month.
Assume they maintain a transactions database named with the following columns:
- : unique id for each transaction
- : a unique identification of the customer
- : the date when the transaction took place
- : the value of each transaction
You need to write a SQL query that will identify these VIP or 'whale' customers.
Example Input:
| transaction_id | user_id | transaction_date | amount_value |
|---|---|---|---|
| 1 | A | 07/01/2022 | 500 |
| 2 | B | 07/02/2022 | 1500 |
| 3 | A | 07/10/2022 | 200 |
| 4 | B | 07/20/2022 | 1000 |
| 5 | C | 07/25/2022 | 2000 |
Answer:
The following SQL query in PostgreSQL will return the user_ids for the VIP customers.
This query first groups the transactions by user_id and for the specified month (in this case July), it counts the number of transactions and sums the value of transactions for each user. Then, from these results, it selects the customers who have more than 50 transactions and whose total transaction value exceeds $10,000.
To practice a super-customer analysis question on DataLemur's free online SQL code editor, try this Microsoft SQL Interview problem:
SQL Question 2: Calculate Average Sales Price Per Month Per Product
Consider you work for TOTVS and have been assigned the task of analyzing sales data. Write a SQL query to calculate the average sales price for each product on a monthly basis. For this analysis, use the "sales" table, which has the following schema:
- (integer, primary key)
- (integer)
- (date)
- (numeric)
Below are some sample inputs and outputs:
Example Input:
| sale_id | product_id | sale_date | price |
|---|---|---|---|
| 1251 | 101 | 2022-01-15 | 200.00 |
| 1420 | 102 | 2022-01-20 | 100.00 |
| 1265 | 101 | 2022-02-15 | 250.00 |
| 1540 | 102 | 2022-02-10 | 150.00 |
| 1595 | 101 | 2022-02-25 | 210.00 |
| 1856 | 102 | 2022-03-05 | 120.00 |
Example Output:
| month | product_id | avg_price |
|---|---|---|
| 1 | 101 | 200.00 |
| 1 | 102 | 100.00 |
| 2 | 101 | 230.00 |
| 2 | 102 | 150.00 |
| 3 | 102 | 120.00 |
Answer:
The following PostgreSQL query should solve the task:
In this query, we first extract the month from the sale_date using PostgreSQL's EXTRACT function. We then group the data by this extracted month and product_id to calculate the average sale price for each product for each month. We order the result by month and product_id to make it easier to read.
To practice another window function question on DataLemur's free online SQL coding environment, try this Amazon BI Engineer interview question:
SQL Question 3: What do the / operators do, and can you give an example?
The / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.
Note that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle (but don't stress about knowing which DBMS supports what exact commands since the interviewers at TOTVS should be lenient!).
For a tangible example in PostgreSQL, suppose you were doing an HR Analytics project for TOTVS, and had access to TOTVS's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all contractors who never were a employee using this query:
SQL Question 4: Average software usage time for users
TOTVS is a Brazilian software company, so an appropriate question might be to determine the average time users are spending in a certain software module each day. This can help the company understand usage patterns and potential performance bottlenecks.
Consider a table with columns , , , and . The task is to find the average usage duration per day for each software module.
Example Input:
| log_id | user_id | usage_date | module_id | usage_duration |
|---|---|---|---|---|
| 1 | 345 | 05/12/2022 | 2301 | 60 |
| 2 | 234 | 05/12/2022 | 2301 | 45 |
| 3 | 131 | 06/12/2022 | 2301 | 30 |
| 4 | 345 | 06/12/2022 | 2402 | 55 |
| 5 | 131 | 07/12/2022 | 2402 | 40 |
Answer:
To extract the average usage_duration for each module per day, one approach is to group by and before applying the function. Here is how it can be done in PostgreSQL:
This will give an output like:
Example Output:
| module_id | usage_date | avg_duration |
|---|---|---|
| 2301 | 05/12/2022 | 52.5 |
| 2301 | 06/12/2022 | 30 |
| 2402 | 06/12/2022 | 55 |
| 2402 | 07/12/2022 | 40 |
This represents the average duration users spent in each module on each day. If all days are considered together, the result will reflect the average usage time per module per day.
To practice a very similar question try this interactive Snapchat Sending vs. Opening Snaps Question which is similar for calculating time spent on application or this Amazon Server Utilization Time Question which is similar for calculating total usage duration.
SQL Question 5: When doing database schema design, what's an example of two entities that have a one-to-one relationship? What about one-to-many relationship?
In database schema design, a one-to-one relationship between two entities is where each entity is associated with only one instance of the other entity. For example, the relationship between a car and a license plate is one-to-one, because each car can only have one licensce plate, and each licensce plate belongs to exactly one car.
On the other hand, a one-to-many relationship is where one entity can be associated with multiple instances of the 2nd entity. For example, a teacher can teach multiple classes, but each class is associated with only one teacher.
SQL Question 6: Analysis of click-through-rates for digital products in TOTVS
As a data analyst for TOTVS, a company that sells digital products, you are tasked with determining the click-through conversion rates of customers from viewing products to adding them to the shopping cart. You are given two sets of data, one containing customer views for each product and another with items added to the shopping cart by the customers. The purpose of this analysis is to help the marketing team evaluate the effectiveness of the user interface in pushing customers to the point of adding products to their shopping carts.
Sample Input:
| view_id | user_id | view_date | product_id |
|---|---|---|---|
| 1173 | 123 | 06/10/2022 00:00:00 | 50001 |
| 1804 | 265 | 06/15/2022 00:00:00 | 69852 |
| 2935 | 362 | 06/20/2022 00:00:00 | 50001 |
| 2561 | 192 | 07/23/2022 00:00:00 | 69852 |
| 2873 | 981 | 07/30/2022 00:00:00 | 69852 |
Sample Input:
| addition_id | user_id | addition_date | product_id |
|---|---|---|---|
| 2935 | 123 | 06/10/2022 00:00:00 | 50001 |
| 4025 | 265 | 06/15/2022 00:00:00 | 69852 |
| 2035 | 362 | 06/25/2022 00:00:00 | 50001 |
| 1547 | 192 | 07/23/2022 00:00:00 | 69852 |
| 7123 | 981 | 07/30/2022 00:00:00 | 69852 |
Answer:
This SQL query would provide the click-through conversion rate for each product
This query will provide the click-through conversion rate by first making separate counts of views and cart additions by product. It then joins these two tables together on the product_id field, and calculates the conversion rate as the number of cart additions divided by the number of views per each product.
To practice a related problem on DataLemur's free interactive SQL code editor, try this SQL interview question asked by Facebook:
SQL Question 7: What are the different kinds of joins in SQL?
A join in SQL combines rows from two or more tables based on a shared column or set of columns.
Four types of JOINs exist in SQL. To demonstrate each one, say you had a table of TOTVS orders and TOTVS customers.
INNER JOIN: When there is a match in the shared key or keys, rows from both tables are retrieved. In this example, an between the Orders and Customers tables would retrieve rows where the in the Orders table matches the in the Customers table.
LEFT JOIN: A retrieves all rows from the left table (in this case, the Orders table) and any matching rows from the right table (the Customers table). If there is no match in the right table, NULL values will be returned for the right table's columns.
RIGHT JOIN: A retrieves all rows from the right table (in this case, the Customers table) and any matching rows from the left table (the Orders table). If there is no match in the left table, NULL values will be returned for the left table's columns.
FULL OUTER JOIN: A retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be returned for the columns of the non-matching table.
SQL Question 8: Joining and Analyzing Customer and Product Tables
As a data analyst for TOTVS, you are tasked with understanding more about our customers' product purchases. You are supposed to write a SQL query to investigate our customer and product tables.
Join the customer table with the product purchase table on the common column to get detailed information about each customer's purchase. Additionally, we would like to know the total number of different products purchased by each customer.
Please write a SQL query to solve this task.
Example Input:
| customer_id | name | sign_up_date | |
|---|---|---|---|
| 101 | John Doe | johndoe@example.com | 01/01/2021 |
| 102 | Jane Doe | janedoe@example.com | 02/02/2021 |
| 103 | Mary Johnson | maryj@example.com | 03/03/2021 |
Example Input:
| purchase_id | customer_id | product_id | quantity | purchase_date |
|---|---|---|---|---|
| 201 | 101 | 1001 | 1 | 01/01/2022 |
| 202 | 101 | 1002 | 2 | 01/02/2022 |
| 203 | 102 | 1003 | 1 | 01/03/2022 |
| 204 | 102 | 1001 | 1 | 01/04/2022 |
| 205 | 103 | 1002 | 1 | 01/05/2022 |
Example Output:
| customer_id | name | number_of_products | |
|---|---|---|---|
| 101 | John Doe | johndoe@example.com | 2 |
| 102 | Jane Doe | janedoe@example.com | 2 |
| 103 | Mary Johnson | maryj@example.com | 1 |
Answer:
This SQL query first joins the table to the table on the field.
Then, it groups the result by , , and , and counts the distinct s to calculate how many different products each customer bought.
Finally, it orders the result by the number of products each customer bought in descending order.
Because join questions come up routinely during SQL interviews, take a stab at an interactive Spotify JOIN SQL question:
How To Prepare for the TOTVS SQL Interview
The key to acing a TOTVS SQL interview is to practice, practice, and then practice some more!
In addition to solving the earlier TOTVS SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like FAANG tech companies and tech startups.
Each interview question has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an online SQL code editor so you can instantly run your SQL query answer and have it graded.
To prep for the TOTVS SQL interview it is also a great idea to practice SQL questions from other tech companies like:
However, if your SQL foundations are weak, don't worry about jumping right into solving questions – strengthen your SQL foundations with this DataLemur SQL tutorial.
This tutorial covers SQL topics like Union vs. UNION ALL and creating summary stats with GROUP BY – both of which pop up frequently during TOTVS SQL assessments.
TOTVS Data Science Interview Tips
What Do TOTVS Data Science Interviews Cover?
Besides SQL interview questions, the other topics covered in the TOTVS Data Science Interview are:
- Statistics and AB Testing Questions
- Python or R Coding Questions
- Open-Ended Data Case Studies
- Machine Learning Questions
- Behavioral Interview Questions
How To Prepare for TOTVS Data Science Interviews?
To prepare for TOTVS Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from tech companies like Netflix, Google, & Airbnb
- a refresher on SQL, AB Testing & ML
- over 900+ reviews on Amazon & 4.5-star rating