10 TietoEVRY SQL Interview Questions (Updated 2024)
TietoEVRY almost always asks SQL problems in interviews for Data Science and Data Engineering positions. To help you study for the TietoEVRY SQL interview, we'll cover 10 TietoEVRY SQL interview questions – able to solve them?
10 TietoEVRY SQL Interview Questions
SQL Question 1: Identify the Top Engaged Users at TietoEVRY
TietoEVRY is a Nordic digital service company that provides a wide range of business solutions. It's important to identify the most engaged, or "VIP", users to learn more about customer interaction and behavior.
Suppose TietoEVRY defines its VIP users as those who made the highest number of purchases in the last month. Write an SQL query to identify the top 10 users by their transaction count in the last month.
Assume we have a transaction table like this:
Example Input
| trans_id | user_id | trans_time | purchase_amount |
|---|---|---|---|
| 2110 | 220 | 09/01/2022 00:00:00 | 140.00 |
| 2563 | 150 | 09/15/2022 00:00:00 | 50.00 |
| 3010 | 320 | 09/13/2022 00:00:00 | 120.00 |
| 4102 | 700 | 10/10/2022 00:00:00 | 200.00 |
| 5120 | 150 | 10/15/2022 00:00:00 | 40.00 |
Answer:
This query filters the transactions made in the last month, groups them by , counts the number of transactions made by each user, and finally orders the users by the number of transactions in descending order. The restricts the output to the top 10 users who made the most transactions.
To solve a super-customer analysis question on DataLemur's free online SQL code editor, try this Microsoft SQL Interview problem:
SQL Question 2: Average Monthly Sales
The sales team at TietoEVRY wants to understand the company's sales data better to identify trends and patterns. They have asked you to write a SQL query to calculate the average monthly sales per product. You don't just want the raw total; you want a running average, meaning that for each month, you want the average of that month and all prior months in the dataset.
Assume you have a table with the following schema.
Example Input:
| sale_id | sale_date | product_id | units_sold |
|---|---|---|---|
| 101 | 2022-02-01 | 102 | 100 |
| 102 | 2022-02-15 | 103 | 200 |
| 103 | 2022-03-20 | 102 | 150 |
| 104 | 2022-03-25 | 103 | 250 |
| 105 | 2022-04-01 | 102 | 200 |
The corresponds to the product sold, the is the date the units got sold, and is the number of units sold on that date.
We need a SQL query to display the month, product_id, and the running average of the units sold till that month.
Example Output:
| month | product_id | running_avg_units |
|---|---|---|
| 2 | 102 | 100.00 |
| 2 | 103 | 200.00 |
| 3 | 102 | 125.00 |
| 3 | 103 | 225.00 |
| 4 | 102 | 150.00 |
Answer:
You can use PostgreSQL's window functions to solve this task. Here's a query that should accomplish what the sales team is asking:
This query works by first partitioning the data by . Then, within each partition, it orders the data by and calculates the running average of to that point. The result is a list of each product's average sales per month since the start of the records. This can help the sales team understand how sales of each product have varied over time.
Pro Tip: Window functions are a popular SQL interview topic, so practice every window function problem on DataLemur
SQL Question 3: In SQL, are NULLs treated the same as zero's and blank spaces?
In SQL, zero's are numerical values which can be used in calculations and comparisons just like any other number. A blank space, also known as an empty string, is a character value and can be used in character manipulation functions and comparisons.
NULLs aren't the same as zero's or blank spaces. NULLs represent unkonwn, missing, or not applicable values. They are not included in calculations and comparisons involving NULL values always result in NULL.
SQL Question 4: Filtering Employee Records
TietoEVRY is a company that provides a range of IT services. For this question, assume you're working with their employee records, particularly focusing on the departments they work in and their years of experience.
In this scenario, the company wants to know the names and contact information of employees who either work in the 'IT' department or 'Finance' department and have more than 5 years of experience. The data available are in the below tables:
Example Input:
Example Input:
The output of your query should return the first name, last name, email, and phone number of the selected employees.
Answer:
The PostgreSQL query to solve this would look similar to:
This query first performs an INNER JOIN to combine information from both the and tables. The WHERE clause then filters the results to only include rows where the employee's department is either IT or Finance and where they have more than 5 years of experience.
Example Output:
SQL Question 5: What is a cross-join?
A cross-join, also known as a cartesian join, is like a mad scientist's laboratory experiment gone wild. It takes two tables and mixes them together to create a crazy new table with every possible combination of rows from the original tables.
Here's an example:
If you have 20 products and 10 colors, that's 200 rows right there! Cross-joins are great for generating all possible combinations, but they can also create really big tables if you're not careful. Just like a mad scientist, use your powers wisely!
SQL Question 6: Calculate the Average Project Duration in Days
TietoEVRY is a Finnish IT software and service company providing IT and product engineering services. Assume you hold the role of a database analyst in their employee project management team. Your task is to find the average duration in days it takes to complete a project in the last financial year.
Sample data is as follows:
Example Input:
| project_id | start_date | end_date |
|---|---|---|
| 1 | 01/04/2021 | 15/05/2021 |
| 2 | 10/02/2021 | 15/02/2021 |
| 3 | 01/05/2021 | 01/07/2021 |
| 4 | 20/03/2021 | 27/03/2021 |
| 5 | 15/03/2021 | 01/04/2021 |
Example Output:
| avg_duration |
|---|
| 36 |
Answer:
This query works by first calculating the duration for each project using function to get the difference in days between the and . function is then applied to get the average project duration in days. The clause is used to filter the projects based on their start date for the last financial year (starts from 01-04-2021 and ends on 31-03-2022).
To practice a very similar question try this interactive Facebook Average Post Hiatus (Part 1) Question which is similar for calculating duration between two dates or this Amazon Server Utilization Time Question which is similar for calculating total time duration.
SQL Question 7: Can you describe the meaning of a constraint in SQL in layman's terms?
Think of SQL constraints like the rules of a game. Just like a game needs rules to keep things fair and fun, a database needs constraints to keep things organized and accurate.
There are several types of SQL constraints like:
NOT NULL: This constraint is like a bouncer at a nightclub - it won't let anything NULL through the door. UNIQUE: This constraint is like a VIP list - only special, one-of-a-kind values get in. PRIMARY KEY: This constraint is like an elected official - it's made up of NOT NULL and UNIQUE values and helps identify each row in the table. FOREIGN KEY: This constraint is like a diplomatic ambassador - it helps establish relationships between tables. CHECK: This constraint is like a referee - it makes sure everything follows the rules. DEFAULT: This constraint is like a backup plan - it provides a default value if no other value is specified.
So, whether you're playing a game or organizing a database, constraints are an important part of the process!
SQL Question 8: Average Salary of Employees Across Various Departments
TietoEVRY is a company that provides software and services, including IT consulting, and has employees in several departments. You are required to calculate the average salary of employees for each department. Assume that the employees' salary data is updated every month.
For this question, consider the following and tables:
Example Input:
| emp_id | department |
|---|---|
| 1 | Marketing |
| 2 | Sales |
| 3 | IT |
| 4 | HR |
| 5 | Marketing |
Example Input:
| salary_id | emp_id | salary_month | salary |
|---|---|---|---|
| 6171 | 1 | August | 5000 |
| 7802 | 2 | August | 6000 |
| 5293 | 3 | August | 7000 |
| 6352 | 4 | August | 8000 |
| 4517 | 5 | August | 5500 |
Answer:
This SQL query joins the and tables on the field. It then groups the results by the field and calculates the average salary for each department. This allows us to see the average salary across all departments for the month of August.
SQL Question 9: Find Employees with Specific Skills
TietoEVRY is a leading digital services and software company. As an HR manager at TietoEVRY, you have been tasked with finding all employees who have experience with a specific technology or skill. You need to be able to search for these employees based on any part of the skill or technology name, not necessarily the whole word.
The company HR records each employee's id, name, and the skills they have in a SQL database. The skills are stored as a comma separated string in the column of the table.
Example Input:
| emp_id | emp_name | skills |
|---|---|---|
| 101 | John Doe | Java,SQL,React |
| 102 | Jane Smith | C#,SQL,Angular |
| 103 | Mary Johnson | Python,SQL,Django |
| 104 | James Brown | Java,C#,React |
The question is to write a SQL query that finds the id and name of all employees who have a skill that contains the term "SQL" in it. You can assume that there are no leading or trailing spaces around each skill in the comma separated list.
Answer:
This query uses the keyword to filter employees whose field contains the term "SQL" anywhere in it. The '%' character is used as a wildcard, so it matches any sequence of characters before or after "SQL". The result of the query is a list of id and name pairs for all matching employees.
SQL Question 10: What's the operator do, and can you give an example?
is used to combine the output of multiple statements into one big result!
For a concrete example, say you were a Data Analyst supporting the Sales Analytics team at TietoEVRY, and data on potential sales leads lived in both Salesforce CRM and Hubspot. To write a query to analyze leads created after 2023 started, across both CRMs, you could use in the following way:
"
Preparing For The TietoEVRY SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the TietoEVRY SQL interview is to solve as many practice SQL interview questions as you can!
Besides solving the earlier TietoEVRY SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like Microsoft, Google, Amazon, and tech startups.
Each interview question has hints to guide you, full answers and crucially, there's an online SQL coding environment so you can instantly run your SQL query answer and have it graded.
To prep for the TietoEVRY SQL interview it is also a great idea to solve interview questions from other tech companies like:
In case your SQL query skills are weak, forget about going right into solving questions – go learn SQL with this free SQL tutorial.
This tutorial covers SQL topics like finding NULLs and filtering data with WHERE – both of these come up often in TietoEVRY SQL assessments.
TietoEVRY Data Science Interview Tips
What Do TietoEVRY Data Science Interviews Cover?
In addition to SQL interview questions, the other topics to prepare for the TietoEVRY Data Science Interview are:
- Probability & Stats Questions
- Python or R Programming Questions
- Business Sense and Product-Sense Questions
- Machine Learning Questions
- Resume-Based Behavioral Questions
How To Prepare for TietoEVRY Data Science Interviews?
The best way to prepare for TietoEVRY Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from Facebook, Google & startups
- A Crash Course covering Python, SQL & ML
- Great Reviews (900+ reviews, 4.5-star rating)