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?
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:
Assume they maintain a transactions database named with the following columns:
You need to write a SQL query that will identify these VIP or 'whale' customers.
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 |
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:
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:
Below are some sample inputs and outputs:
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 |
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 |
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:
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:
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.
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 |
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:
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.
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.
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.
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 |
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 |
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:
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.
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.
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 |
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 |
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 |
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:
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.
Besides SQL interview questions, the other topics covered in the TOTVS Data Science Interview are:
To prepare for TOTVS Data Science interviews read the book Ace the Data Science Interview because it's got: