logo

8 TOTVS SQL Interview Questions (Updated 2024)

Updated on

March 2, 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_iduser_idtransaction_dateamount_value
1A07/01/2022500
2B07/02/20221500
3A07/10/2022200
4B07/20/20221000
5C07/25/20222000

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: Microsoft SQL Interview Question: Super Cloud Customer

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_idproduct_idsale_dateprice
12511012022-01-15200.00
14201022022-01-20100.00
12651012022-02-15250.00
15401022022-02-10150.00
15951012022-02-25210.00
18561022022-03-05120.00
Example Output:
monthproduct_idavg_price
1101200.00
1102100.00
2101230.00
2102150.00
3102120.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: Amazon Business Intelligence SQL 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:


TOTVS SQL Interview Questions

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_iduser_idusage_datemodule_idusage_duration
134505/12/2022230160
223405/12/2022230145
313106/12/2022230130
434506/12/2022240255
513107/12/2022240240

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_idusage_dateavg_duration
230105/12/202252.5
230106/12/202230
240206/12/202255
240207/12/202240

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_iduser_idview_dateproduct_id
117312306/10/2022 00:00:0050001
180426506/15/2022 00:00:0069852
293536206/20/2022 00:00:0050001
256119207/23/2022 00:00:0069852
287398107/30/2022 00:00:0069852
Sample Input:
addition_iduser_idaddition_dateproduct_id
293512306/10/2022 00:00:0050001
402526506/15/2022 00:00:0069852
203536206/25/2022 00:00:0050001
154719207/23/2022 00:00:0069852
712398107/30/2022 00:00:0069852

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: Facebook Click-through-rate SQL Question

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_idnameemailsign_up_date
101John Doejohndoe@example.com01/01/2021
102Jane Doejanedoe@example.com02/02/2021
103Mary Johnsonmaryj@example.com03/03/2021
Example Input:
purchase_idcustomer_idproduct_idquantitypurchase_date
2011011001101/01/2022
2021011002201/02/2022
2031021003101/03/2022
2041021001101/04/2022
2051031002101/05/2022
Example Output:
customer_idnameemailnumber_of_products
101John Doejohndoe@example.com2
102Jane Doejanedoe@example.com2
103Mary Johnsonmaryj@example.com1

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

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.

DataLemur SQL Course

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:

TOTVS Data Scientist

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

Acing Data Science Interview