logo

8 Nike SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

At Nike, SQL is to predict trends, and manage inventory databases for accurate stocks and distribution planning. So, it shouldn't surprise you that Nike asks SQL query questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.

Thus, to help you prepare, we've curated 8 Nike SQL interview questions – how many can you solve?

Nike SQL Interview Questions

8 Nike SQL Interview Questions

SQL Question 1: Average Monthly Sales per Product

For Nike's auditing purposes, they want to analyze their monthly performance. Write a SQL query to find the average sales amount (in USD) per product on a monthly basis using data from the last year. Assume that each product ID corresponds to a single type of product sold by Nike.

Here are your sample tables:

Example Input:
sales_idproduct_idsales_dateamount
100110001/12/2021$200
100220001/18/2021$150
100330002/23/2021$120
100410002/05/2021$180
100520005/31/2021$160
100630006/17/2021$240
100710007/22/2021$210
100820010/03/2021$130
100930012/13/2021$220
101010012/22/2021$200
Example Output:
monthproduct_idavg_amount
01100$200
01200$150
02100$180
02300$120
05200$160
06300$240
07100$210
10200$130
12100$200
12300$220

Answer:


This query takes sales from 2021, and then groups them by month and product_id. The average amount of each product sold in each month is calculated with the AVG function. The result is ordered by month and product_id.

To solve a similar window function question on DataLemur's free interactive SQL code editor, solve this Google SQL Interview Question: Google SQL Interview Question

SQL Question 2: Department Salaries

Suppose you had a table of Nike employee salaries, along with which department they belonged to. Write a query to compare the average salary of employees in each department to the company's average salary for March 2024. Return the comparison result as 'higher', 'lower', or 'same' for each department. Display the department ID, payment month (in MM-YYYY format), and the comparison.

Code your solution to this interview question and run your code right in DataLemur's online SQL environment:

Department vs. Company Salary

The solution is LONG – 30+ lines of SQL. You can find a detailed solution here: Department Salaries.

SQL Question 3: What does adding 'DISTINCT' to a SQL query do?

The keyword removes duplicates from a query.

Suppose you had a table of Nike customers, and wanted to figure out which cities the customers lived in, but didn't want duplicate results.

table:

namecity
AkashSF
BrittanyNYC
CarlosNYC
DiegoSeattle
EvaSF
FayeSeattle

You could write a query like this to filter out the repeated cities:


Your result would be:

city
SF
NYC
Seattle

Nike SQL Interview Questions

SQL Question 4: Calculate the Average Shoe Sizes sold on Nike

As a Data analyst at Nike, you are tasked with understanding the footwear market to provide insightful reports for the product team. They want to understand the average shoe size sold in different months.

Using the table, write a SQL query to calculate the average shoe size sold per month.

Example Input:
sales_idcustomer_idsale_dateshoe_idshoe_size
1001200106/02/2022 00:00:00300019
1002200206/13/2022 00:00:003000210
1003200306/29/2022 00:00:00300038
1004200407/14/2022 00:00:003000411
1005200507/20/2022 00:00:00300059
Example Output:
monthaverage_shoe_size
69.00
710.00

Answer:


This SQL query extracts the month from the column and groups the data based on these months. The average shoe size sold in every month is then calculated with the function.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for requiring data aggregation to find product statistics or this Amazon Average Review Ratings Question which is similar for needing monthly data averages.

SQL Question 5: DBMS transactions are expected to follow the ACID properties. What are they, and what does each property mean?

A DBMS (database management system), in order to ensure transactions are relaible and don't ruin the integrity of the data, tries to mantain the following ACID properties: Atomicity, Consistency, Isolation, and Durability.

To make this concept more concrete, here is what each of the ACID properties would mean in the context of banking transactions:

  • Atomicity: a transaction is either completed fully, or not complete at all. For example, if a customer is transferring money from one account to another, the transaction should either transfer the full amount or none at all.
  • Consistency: a transaction will only be completed if it follows all database constraints and checks. For example, if a customer is withdrawing money from an account, the transaction should only be completed if the account has sufficient funds available, otherwise the transaction is rejected
  • Isolation: ensures that concurrent transactions are isolated from each other, so that the changes made by one transaction cannot be seen by another transaction. This isolation prevents race conditions, like two customers trying to withdraw money from the same account at the same time.
  • Durability: ensures that once a transaction has been committed and completed, the changes are permanent. A reset / shutdown of the database shouldn't erase someone's savings accounts!

SQL Question 6: Calculate the Average Price of Each Product Type

Nike, a multinational corporation that is engaged in the design, development, manufacturing, and worldwide marketing and sales of footwear, apparel, equipment, and accessories, wants to better understand their sales for each type of product they sell.

Your task is to write a SQL query that can calculate the average price of each product type that has been sold.

Example Input:
sale_idproduct_idproduct_typesale_price
1001A001Shoes120
1002B001Apparel70
1003A002Shoes150
1004C001Accessories30
1005B002Apparel90
1006A003Shoes130
Example Output:
product_typeavg_price
Shoes133.33
Apparel80
Accessories30

Answer:


In this PostgreSQL query, we are selecting the and the average (calculated using AVG function) from the table. We group the result by to receive the average price for each unique product type. is an aggregate function that returns the average value of a numeric column.

SQL Question 7: Could you describe the function of UNION in SQL?

{#Question-7}

The UNION operator combines the results from multiple SELECT statements into a single result set.

Here's an example of using the UNION operator to combine the results of two SELECT statements that retrieve data from tables of Nike's Facebook ads and their Google ads:


This statement would return a combined result set of both Facebook and Google ads that have a spend greater than $200. The result set would include the following columns: ad_id, ad_name, ad_type, impressions, clicks, and spend.

Note that the two SELECT statements within the UNION must have the same number of columns and the columns must have similar data types. In this example, both SELECT statements have the same number of columns and the data types of the columns are all compatible.

SQL Question 8: Calculate The Total Sales and Average Discount Per Year and Season

Assume that Nike wants to conduct an analysis on their yearly and seasonal sales. They want to identify the total sales volume, the average discount offered, and the rounded figure of average discount per footwear type each year and season. The seasons are categorized as follows: Winter (December, January, February), Spring (March, April, May), Summer (June, July, August), Fall (September, October, November).

Example Input:
sale_idsale_datefootwear_typepricediscount
23552022-01-15Running Shoes855
36122022-03-10Basketball Shoes12010
17552022-06-20Running Shoes907
67922022-09-12Training Shoes1058
48152022-05-04Basketball Shoes1159

Answer:


The above SQL query first calculates total sales and average discount per year and season in common table expression (CTE). Then it queries from this CTE to get results with rounded average discounts. The clause is used to isolate the seasons, and is used to extract year and month from . The clause is used to partition the data into each unique combination of year, season and footwear type. The and functions are used to get sales volume and average discount, respectively. Then rounding off the average discount is done using function.

To practice a very similar question try this interactive Wayfair Y-on-Y Growth Rate Question which is similar for calculating yearly sales data or this Amazon Highest-Grossing Items Question which is similar for identifying top performing products.

Nike SQL Interview Tips

The key to acing a Nike SQL interview is to practice, practice, and then practice some more! Beyond just solving the earlier Nike SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Microsoft, Google, and Facebook. DataLemur SQL and Data Science Interview Questions

Each problem on DataLemur has multiple hints, full answers and best of all, there's an interactive coding environment so you can instantly run your SQL query and have it checked.

To prep for the Nike SQL interview it is also useful to practice interview questions from other apparel companies like:

However, if your SQL coding skills are weak, don't worry about going right into solving questions – go learn SQL with this DataLemur SQL Tutorial.

DataLemur SQL Tutorial for Data Science

This tutorial covers SQL concepts such as LEAD/LAG and math functions in SQL – both of these pop up frequently in Nike SQL assessments.

Nike Data Science Interview Tips

What Do Nike Data Science Interviews Cover?

Beyond writing SQL queries, the other types of problems tested in the Nike Data Science Interview are:

Nike Data Scientist

How To Prepare for Nike Data Science Interviews?

I'm sorta biased, but I think the optimal way to prepare for Nike Data Science interviews is to read the book Ace the Data Science Interview.

The book solves 201 interview questions taken from Facebook, Google, & Amazon. It also has a crash course on Stats, SQL & ML. And finally it's helped thousands of people land their dream job in data, which is how it's gotten to be an Amazon best-seller with over 1000+ 5-star reviews.

Ace the Data Science Interview

Also check out this job listing for a Lead Data Scientist at Nike to help you see what skilles you need for the role.