8 Zalando SQL Interview Questions (Updated 2024)

Updated on

March 2, 2024

At fashion retailer Zalando, SQL is typically used for analyzing customer shopping patterns to optimize product recommendations. Unsurprisingly this is why Zalando frequently asks SQL query questions in interviews for Data Science, Analytics, and & Data Engineering jobs.

Thus, to help you ace the Zalando SQL interview, we've collected 8 Zalando SQL interview questions – able to solve them?

8 Zalando SQL Interview Questions

SQL Question 1: Calculate Monthly Average Rating for Each Product

At Zalando, the reviews of the products are crucial to understand customer satisfaction and product quality over time. For the given dataset, write a SQL query to calculate the monthly average rating for each product. You need to return a table with , , and .

Example Input:
review_iduser_idsubmit_dateproduct_idstars
11232022-06-080014
22652022-07-080025
39872022-06-150013
44532022-07-180015
52342022-07-200023
Example Output:
mthproduct_idavg_stars
60013.5
70015.0
70024.0

Answer:

To solve this problem, we can benefit from PostgreSQL window functions, particularly the function and function to get the month of the review submission date.


The clause in the query specifies how rows are divided into groups for calculating the average. So by grouping by 'product_id' and the extract month, we get the average stars for each product per month. The order by clause then sorts the output by 'product_id' and 'mth'. Please note that 'stars' has to be a numeric field for function to work.

To solve another window function question on DataLemur's free online SQL coding environment, solve this Google SQL Interview Question: Google SQL Interview Question

SQL Question 2: Inventory Management Analysis

As a data analyst at Zalando, you are given a task to examine and analyze the inventory management system of the company. You have two database tables:

  • products: This table holds information about the various products sold by Zalando.
  • inventory: This table contains information about the quantity of items in the inventory, when they were added, and their status (“Sold”, "Available", “Returned”).

Your task is to write a SQL query that returns the product id, the number of item units sold, units available, and the units returned for each product.

Example Input:
product_idproduct_nameprice
105Leather Jacket299.99
205Ankle Boots179.99
305Trench Coat499.99
Example Input:
inventory_idproduct_idstatusadd_date
1101105Sold2020-01-25 00:00:00
1102105Returned2020-01-26 00:00:00
1103205Available2021-02-21 00:00:00
1104205Sold2021-03-11 00:00:00
1105305Available2021-04-01 00:00:00

Answer:


In this SQL query, we start by joining the and tables on the column. Next, we use the SQL statement within the aggregate function to count the number of units sold, available and returned by product id.

SQL Question 3: How would you speed up a slow SQL query?

Here's some strategies that can generally speed up a slow SQL query:

  • Only query the columns you actually need
  • Index the columns used in the query
  • Use the right kind of JOIN (be explicit about inner vs. outer joins)
  • Use the right data types (for example INT vs BIGINT can save you disk space and memory)

While there's an entire industry dedicated to database performance tuning , for Data Analyst and Data Science interviews at Zalando, knowing the above basics should be more than enough to ace the SQL interview! Data Engineers on the other hand should know more about the command and understand a bit more about a query-execution plan.

Zalando SQL Interview Questions

SQL Question 4: Find the Average Number of Items per Order

Zalando is a popular European e-commerce company, specializing in fashion and lifestyle products. For a data analyst role in this company, you might be asked to find the average number of items included in each order on the platform. Let's consider the following data tables:

Example Input:
order_iduser_idorder_date
5432100107/01/2022
2468105007/02/2022
1010110207/02/2022
5050100107/03/2022
3592100507/04/2022
Example Input:
item_idorder_idproduct_idquantity
5015432105011
5025432307022
5032468205501
5041010300151
5051010100552
5065050200021
5073592150223
5083592100211

With the above two tables available, write a SQL query to find the average number of items per order.

Answer:


This query first counts the number of items in each order using a nested subquery. The outer query then calculates the average of these counts. The result will provide the average number of items included in each order.

SQL Question 5: What is the difference between a correlated subquery and non-correlated subquery?

A correlated sub-query is one that relies on the outer query and cannot be run independently. It uses the outer query to filter or transform data by referencing a column from it, while the outer query uses the results of the inner query. On the other hand, a non-correlated sub-query is not linked to the outer query and can be run on its own. It does not reference any columns from the outer query and is used to retrieve additional data for the outer query.

An example correlated sub-query:


This correlated subquery retrieves the names and salaries of Zalando employees who make more than the average salary for their department. The subquery references the department column in the main query's FROM clause (e1.department) and uses it to filter the rows of the subquery's FROM clause (e2.department).

An example non-correlated sub-query:


This non-correlated subquery retrieves the names and salaries of Zalando employees who make more than the average salary for the Data Analytics department (which honestly should be very few people since Data Analysts are awesome).

Anways, the subquery is independent of the main query and can be considered a standalone query. Its output (the average salary for the Data Analytics department) is then used in the main query to filter the rows of the table.

SQL Question 6: Most Selling Product Category

As an ecommerce company, Zalando sells various types of products like clothing, shoes, accessories, beauty products etc. The following task requires you to find the product category which has generated the highest sales for each month.

Example Input:
product_idcategoryprice
1001Shoes75
1002Clothes30
1003Accessories15
1004Beauty20
1005Clothes50
Example Input:
sale_idproduct_idquantitysale_date
110011006/01/2022 00:00:00
21002806/02/2022 00:00:00
310031506/03/2022 00:00:00
41004707/01/2022 00:00:00
510051207/02/2022 00:00:00
Example Output:
monthcategorytotal_sales
6Shoes750
6Clothes240
6Accessories225
7Beauty140
7Clothes600

Answer:


First, we do a join on the 'sales' and 'products' tables using the 'product_id' column, and then we group the records by the month of sale date and product category. The SUM aggregate function is used to calculate the total sales for each category in each month. The order of output is based on the month and total sales in descending order.

SQL Question 7: What's the difference between a clustered and non-clustered index?

Clustered indexes have a special characteristic in that the order of the rows in the database corresponds to the order of the rows in the index. This is why a table can only have one clustered index, but it can have multiple non-clustered indexes.

The main difference between clustered and non-clustered indexes is that the database tries to maintain the order of the data in the database to match the order of the corresponding keys in the clustered index. This can improve query performance as it provides a linear-access path to the data stored in the database.

SQL Question 8: Analyzing sales trends over time

Zalando needs to analyze their sales by month, to better understand their sales trends over time. Write a query to calculate the total sales made and the number of orders for each product on a monthly basis, from the beginning of 2022.

We have a table with the following schema:

Example Input:
sales_idproduct_idsale_datesale_price
101ZAL0012022-01-15100.00
102ZAL0022022-01-30150.00
103ZAL0012022-02-10100.00
104ZAL0032022-02-25200.00
105ZAL0022022-02-28150.00

The output will contain the , of the sale (in format), total for the product in the month, and the for the product in the month.

Example Output:
product_idmonthtotal_salesorder_count
ZAL001202201100.001
ZAL002202201150.001
ZAL001202202100.001
ZAL002202202150.001
ZAL003202202200.001

Answer:

Here is the SQL query block:


In this answer, function is used to convert the dates to a string representation in the format . Then and functions are used to calculate total sales and order count respectively for each product per month. We consider only the sales that were made in the year 2022 as signified by the clause. The and clauses are used to group and sort the results product-wise and then month-wise.

How To Prepare for the Zalando SQL Interview

The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. In addition to solving the above Zalando SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like FAANG and tech startups. DataLemur SQL and Data Science Interview Questions

Each SQL question has hints to guide you, step-by-step solutions and best of all, there is an interactive coding environment so you can right in the browser run your query and have it executed.

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

However, if your SQL coding skills are weak, forget about diving straight into solving questions – go learn SQL with this interactive SQL tutorial.

Interactive SQL tutorial

This tutorial covers SQL concepts such as WHERE vs. HAVING and handling date/timestamp data – both of these pop up routinely during SQL interviews at Zalando.

Zalando Data Science Interview Tips

What Do Zalando Data Science Interviews Cover?

In addition to SQL interview questions, the other topics to practice for the Zalando Data Science Interview are:

Zalando Data Scientist

How To Prepare for Zalando Data Science Interviews?

To prepare for Zalando Data Science interviews read the book Ace the Data Science Interview because it's got:

  • 201 interview questions sourced from Google, Microsoft & tech startups
  • a crash course covering SQL, Product-Sense & ML
  • over 900+ reviews on Amazon & 4.5-star rating

Ace the DS Interview

© 2024 DataLemur, Inc

Career Resources

Free 9-Day Data Interview Crash CourseFree SQL Tutorial for Data AnalyticsUltimate SQL Interview GuideAce the Data Job Hunt Video CourseAce the Data Science InterviewBest Books for Data Analysts