# 10 TietoEVRY SQL Interview Questions (Updated 2024)

Updated on

March 3, 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_iduser_idtrans_timepurchase_amount
211022009/01/2022 00:00:00140.00
256315009/15/2022 00:00:0050.00
301032009/13/2022 00:00:00120.00
410270010/10/2022 00:00:00200.00
512015010/15/2022 00:00:0040.00

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_idsale_dateproduct_idunits_sold
1012022-02-01102100
1022022-02-15103200
1032022-03-20102150
1042022-03-25103250
1052022-04-01102200

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:
monthproduct_idrunning_avg_units
2102100.00
2103200.00
3102125.00
3103225.00
4102150.00

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:

The output of your query should return the first name, last name, email, and phone number of the selected employees.

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.

### 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_idstart_dateend_date
101/04/202115/05/2021
210/02/202115/02/2021
301/05/202101/07/2021
420/03/202127/03/2021
515/03/202101/04/2021
##### Example Output:
avg_duration
36

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:

emp_iddepartment
1Marketing
2Sales
3IT
4HR
5Marketing
##### Example Input:
salary_idemp_idsalary_monthsalary
61711August5000
78022August6000
52933August7000
63524August8000
45175August5500

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_idemp_nameskills
101John DoeJava,SQL,React
102Jane SmithC#,SQL,Angular
103Mary JohnsonPython,SQL,Django
104James BrownJava,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.

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:

#### 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: