11 Nemetschek SQL Interview Questions (Updated 2024)

At Nemetschek, SQL is used across the company for analyzing complex construction, real estate, and geospatial data, as part of their work in the AEC/O industry. That's why Nemetschek almost always asks SQL problems in interviews for Data Analytics, Data Science, and Data Engineering jobs.

So, to help you ace the Nemetschek SQL interview, we've collected 11 Nemetschek SQL interview questions – how many can you solve?

11 Nemetschek SQL Interview Questions

SQL Question 1: Identify Top Clientele Based on License Usage

Nemetschek is a software company that sells a wide variety of software licenses to clients. In order to keep track of who their most important users are, they would like to identify users that have the most licenses. Transactions from the last financial quarter need to be analyzed to identify these important users. License usage is determined by the number of unique active software license keys a user has purchased and is presently using.

For this purpose, you need to write a SQL query that will yield information on the top 10 users based on the total number of unique active licenses they own.

The table is as follows:

Example Input:

The table is as follows:

Example Input:

Answer:

In this query, the COUNT() function is used to count the number of unique license_key associated with each user_id for the given time period. The results are ordered in descending order of and limited to the top 10 users. Please note that only active licenses are considered (those whose is later than the current date).

To solve a similar power-user data analysis problem question on DataLemur's free interactive SQL code editor, try this recently asked Microsoft SQL interview question:

SQL Question 2: Analyze Monthly Product Ratings

In Nemetschek, one of your tasks could be analyzing product ratings to help track and improve product performance. Given a dataset of review information, please write a SQL query to calculate the average star rating for each product per month. Consider the submit_date is in format.

The table is defined as follows:

You should return a table in the following format:

Answer:

This query first creates a Common Table Expression (CTE) where it extracts the month from the submit_date and selects the required columns. Then it calculates the average ratings for each product per month using a window function. This window is partitioned by month and product, and ordered by month. This will give the average ratings for each product for every month. The results are finally ordered by product.

p.s. Window functions show up super often during SQL interviews, so practice the 27+ window function questions on DataLemur

SQL Question 3: What are the various types of joins used in SQL?

In SQL, a join is used to combine rows from different tables based on a shared key or set of keys, resulting in a single merged table of data.

There are four distinct types of JOINs: , , , and .

(INNER) JOIN: Retrieves records that have matching values in both tables involved in the join.

LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.

RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.

FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.

SQL Question 4: Customer Sales Analysis for Nemetschek

Nemetschek is a software company that specializes in design and management solutions for the AEC (Architecture, Engineering, Construction) industry. For this question, let's assume that the company wants to analyze their software sales. Your task is designing tables and writing a PostgreSQL query that will retrieve the total number of licenses sold per software product, the total sales value and the average sales price.

Scenario: The company operates on a licensing model where customers can buy multiple licenses of various software products. They would like to track how many licenses of each product have been sold, the total sales generated, and also the average price of these sold licenses.

Here are the two tables you would have:

Example Input:

Example Input:

The table records each sale, with the software product sold, the customer to whom it was sold, how many licenses were purchased in that sale, and the total price of the sale.

Your task is to write a query that would produce the following report:

Example Output:

Answer:

This query first joins the and tables on the field. The function is then used to calculate the total number of licenses sold per software product and their total sales value. The function calculates the average price of a license for each software product. The results are grouped by the software name.

SQL Question 5: What's the purpose of a foreign key?

A foreign key is a field in a database table that links to the primary key of another table, establishing a connection between the two tables. For example, let's analyze Nemetschek's Google Ads campaigns data:

:

+------------+------------+------------+------------+ | ad_id | campaign_id| keyword | click_count| +------------+------------+------------+------------+ | 1 | 201 | Nemetschek reviews | 120 | | 2 | 202 | Nemetschek pricing | 150 | | 3 | 101 | buy Nemetschek | 65 | | 4 | 101 | Nemetschek alternatives | 135 | +------------+------------+------------+------------+

is a foreign key that connects to the of the corresponding Google Ads campaign. This establishes a relationship between the ads and their campaigns, enabling easy querying to find which ads belong to a specific campaign or which campaigns a specific ad belongs to.

The table may also have multiple foreign keys that reference primary keys in other tables. For example, and foreign keys could be used to link each ad to its ad group and the Google Ads account that the campaigns belong to, respectively.

SQL Question 6: Filter Customers Database by Purchase History

Nemetschek, a software company, maintains a record of their customers and their purchases. You are tasked to filter their customer database to identify all customers from 'Germany' who have purchased 'Software' more than once in the year '2022'.

For this task, you are given two tables - and .

Example Input:

Example Input:

Answer:

This PostgreSQL query first uses a subquery that selects all customers from the table that have more than one Software purchase in 2022. The command is used as it allows conditional filtering post-aggregation. The result is then joined with the table to attribute customer information. The final filtered table consists of German customers who have purchased more than one software product in the year 2022.

SQL Question 7: What are the similarities and differences between a clustered index and non-clustered index?

A clustered index is an index where the order of the rows in the database corresponds to the order of the rows in the index. Because of this, a table can only have one clustered index, but it can have multiple non-clustered indexes.

The main difference between the two is that the database tries to keep the data in the same order as the corresponding keys in the clustered index. This can improve the performance of most query operations, as it provides a linear-access path to the data stored in the database.

SQL Question 8: Analyzing Click-through-rates for Nemetschek

Nemetschek, a software company that offers solutions in the realms of construction, media, and design, uses digital marketing to reach its target customers. The marketing department conducts several ad campaigns, and they maintain a detailed log of every ad clicked by users. They would like to analyze the click-through rates (CTR) of their campaigns.

The CTR is calculated as the total number of clicks that an ad receives divided by the total number of impressions (times the ad was shown) it gets, displayed as a percentage.

Using the available data in the - which stores data about each click including the ad campaign id, date, and user id - and tables - which stores data about each ad impression including the ad campaign id, date, and user id - calculate the CTR for each ad campaign.

Example Input:

Example Input:

Answer:

In this SQL query, we first join the and tables on . For each ad campaign, we count the distinct click ids and distinct impression ids. The CTR is then calculated by dividing the number of unique clicks by the number of unique impressions and multiplying by 100 to get the rate in percentage. The result is grouped by , giving us the CTR for each campaign.

To solve a related SQL problem on DataLemur's free interactive coding environment, attempt this Meta SQL interview question:

SQL Question 9: Find Customers With Specific Name Pattern

As a software company, Nemetschek maintains a database of its customers. Due to their international operations, they want to filter down the customer base to find all customers whose last names start with 'Sch' to target them for their new software launch in German-speaking countries. Can you write a SQL query that will return a list of customers whose last name starts with 'Sch' from their table?

For instance, if the last_name is 'Schmidt', it should be returned by your query.

Consider the table with the following data:

Example Input:

Answer:

Here is a PostgreSQL query that will filter all customers whose last name starts with 'Sch':

This SQL query uses the LIKE keyword in combination with a wildcard character (%) to select all customers whose last name starts with 'Sch'. The '%' is used to match any sequence of characters in SQL, so 'Sch%' will match any last name that starts with 'Sch'.

This should output:

SQL Question 10: In the context of databases, what does atomicity, consistency, isolation, and durability mean?

To ensure the reliability and integrity of data, a database management system (DBMS) strives to maintain the ACID properties: Atomicity, Consistency, Isolation, and Durability. To illustrate these concepts, consider the following examples of how ACID properties apply to banking transactions:

1. Atomicity: A transaction is either completed in full or not at all. For example, if a customer is transferring money between accounts, the transaction should either transfer the entire amount or none at all.
2. Consistency: A transaction is only allowed to complete if it follows all rules and constraints within the database. For example, if a customer is withdrawing money from an account, the transaction should only be allowed to proceed if there are sufficient funds available. Otherwise, the transaction is rejected.
3. Isolation: Concurrent transactions are kept separate from each other, so that the changes made by one transaction cannot be seen by another transaction until the first one is complete. This helps prevent conflicts, such as two customers attempting to withdraw money from the same account at the same time.
4. Durability: Once a transaction has been committed and completed, the changes made by the transaction are permanently stored in the database and will not be lost even if the database or system crashes. For example, if a customer makes a deposit, the transaction should be durable so that the deposit is not lost in the event of a system failure.

SQL Question 11: Analyzing Customer Subscription and Login Frequency

As a database manager for the company Nemetschek, you are asked by the marketing team to provide an analysis for customer subscription and login frequency.

You are given two tables:

Example Input:

Example Input:

From the subscriptions table, they want to know the type of subscription each user has. From the logins table, they want to know how many times each user has logged in. They need you to join these details together based on the user_id.

The resulting table should have a row for every user in the 'subscriptions' table, along with their subscription type and login count (even if the count is zero).

Example Output:

Answer:

In this query, we start by using a LEFT JOIN on the logins table using the user_id as the common factor. This ensures that we retrieve a row for each user in the 'subscriptions' table irrespective of whether they have logged in or not. The COUNT function is applied to count the number of logins and the output is grouped by user_id and subscription type.

Since joins come up so often during SQL interviews, try this interactive Snapchat JOIN SQL interview question:

Preparing For The Nemetschek SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Nemetschek SQL interview is to solve as many practice SQL interview questions as you can! Besides solving the above Nemetschek SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Microsoft, Google, and Facebook.

Each SQL question has hints to guide you, detailed solutions and crucially, there is an interactive coding environment so you can easily right in the browser your SQL query answer and have it executed.

To prep for the Nemetschek SQL interview it is also wise to solve SQL problems from other tech companies like:

• Autodesk SQL questions

• Dassault Systemes SQL questions

• Procore SQL questions

• Trimble SQL questions

However, if your SQL skills are weak, don't worry about jumping right into solving questions – refresh your SQL knowledge with this DataLemur SQL tutorial.

This tutorial covers topics including manipulating string/text data and filtering with LIKE – both of which show up routinely in Nemetschek interviews.

Nemetschek Data Science Interview Tips

What Do Nemetschek Data Science Interviews Cover?

In addition to SQL query questions, the other types of problems to practice for the Nemetschek Data Science Interview are:

• Statistics Interview Questions
• Python or R Programming Questions
• Data Case Study Questions
• Machine Learning and Predictive Modeling Questions
• Behavioral & Resume-Based Questions

How To Prepare for Nemetschek Data Science Interviews?

The best way to prepare for Nemetschek 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 on Python, SQL & ML
• Amazing Reviews (900+ 5-star reviews on Amazon)