logo

9 NEXTDC SQL Interview Questions (Updated 2024)

Updated on

March 2, 2024

At NEXTDC, SQL is typically used for analyzing data center operations. That's why NEXTDC frequently asks SQL query questions during interviews for Data Science, Data Engineering and Data Analytics jobs.

To help you ace the NEXTDC SQL interview, we've curated 9 NEXTDC SQL interview questions – can you answer each one?

9 NEXTDC SQL Interview Questions

SQL Question 1: Analyze data center power usage patterns

Suppose NEXTDC wants you to analyze their data center's power usage statistics. Given the table , write a SQL query to find the average power usage of each data center for each month in the year 2021. Also, find out the percentage change in the average power usage as compared to the previous month.

The table has the following columns:

  • (integer) - unique identifier
  • (varchar) - data center name
  • (date) - the date the data was logged
  • (integer) - the amount of power used that day (in kWh)
Example Input:
iddata_centerusage_datepower_used
1DC12021-01-01500
2DC12021-01-02520
3DC12021-02-01600
4DC22021-01-01750
5DC22021-02-01800
6DC12021-02-02610
7DC22021-01-02770
8DC22021-02-02820
Example Output:
monthdata_centeraverage_power_usedpercentage_change
1DC1510NULL
2DC160518.6
1DC2760NULL
2DC28106.6

Answer:


In this query, we start by grouping power usage by month and data center name. We use the AVG function to calculate the average power usage for each month.

Next, we use a window function to calculate the percentage change in power usage compared to the previous month. We use the LAG function to get the previous month's usage and subtract it from the current month's usage. To convert this difference into a percentage, we divide it by the previous month's usage and multiply the result by 100.

The COALESCE and NULLIF functions are used to handle cases where there is no previous month's power usage (i.e., for the first month). If there's no previous month's data, the percentage change is set as NULL. This query should return a row for each data center for each month, with columns for month, average power use, and percentage change from the previous month.

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

SQL Question 2: Filter Customer Data Based on Multiple Conditions

Given a table containing customer information of NEXTDC. The information provided includes , , , , and .

Write a SQL query that returns the names of customers who live in 'QLD', have the service type 'Premium' and have been on board since 2018 or later. Also, these customers should have a contract length of more than 2 years.

Consider the markdown formatted tables below as example input and output.

Example Input:
customer_idnameservice_typestateonboard_yearcontract_length
101JohnStandardQLD20192
102MaryPremiumVIC20203
103StevePremiumQLD20183
104JanePremiumQLD20173
105MarkPremiumQLD20213
Example Output:
name
Steve
Mark

Answer:


In this query, we use the clause to filter the customers' data based on multiple boolean conditions. We use to ensure all conditions are met. This query will return the names of customers who live in 'QLD', signed up for the 'Premium' service, joined in 2018 or later, and have a contract length of more than 2 years.

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

{#Question-3}

is used to combine the results of multiple statements into a single result set.

Suppose you were doing an HR Analytics project for NEXTDC, and needed to analyze both NEXTDC's contractors and employees. You could use in the following way:


This statement would return a combined result set of NEXTDC contractors and employees who were hired after the start of the year 2023.

NEXTDC SQL Interview Questions

SQL Question 4: Average Power Usage Per Client for NEXTDC

As a data center services provider, NEXTDC may have multiple clients. Each client may have different servers that use different amounts of power. Your task is to figure out the average power usage per client.

Example Input:
usage_idclient_idserver_idpower_usage (in kWh)
1101201350
2101202450
3102203500
4102204600
5103205700
Example Output:
client_idaverage_power_usage
101400
102550
103700

Answer:


This PostgreSQL query groups the data by and calculates the average for each group. The function in SQL computes the average of the values in a group. The statement groups the rows that have the same values in specified columns into aggregated data. The result of the query gives us the average power usage for each client.

To practice a very similar question try this interactive Amazon Server Utilization Time Question which is similar for requiring aggregation of entities based on usage metrics or this Microsoft Supercloud Customer Question which is similar for getting information related grouped by client.

SQL Question 5: How can you select records without duplicates from a table?

The clause in SQL allows you to select records that are unique, eliminating duplicates.

For example, if you had a table of NEXTDC employees, and wanted to see what are all the unique job titles that currently worked at the company, you could write the following query:


SQL Question 6: Find the Average Power Usage Per Data Center

NEXTDC is an Australian-based technology company that delivers data center outsourcing solutions, connectivity services, and infrastructure management software. As a part of the day-to-day operations, NEXTDC needs to keep track of the power usage of each of its data centers. In particular, they are interested in calculating the average power usage per data center for each month.

Example Input:
log_iddata_center_idlog_datepower_usage_in_kw
101DC102/08/2022 00:00:00800
102DC102/09/2022 00:00:00850
103DC202/10/2022 00:00:00900
104DC203/11/2022 00:00:00950
105DC103/12/2022 00:00:001000
Example Output:
monthdata_centeravg_power_usage_in_kw
2DC1825
2DC2900
3DC2950
3DC11000

Answer:


The above PostgreSQL query first extracts the month from the column. It then groups the data by both the extracted month and the , effectively grouping the data by both month and data center. Finally, it calculates the average for each group. This provides the required output, which is the average power usage for each data center, per month.

SQL Question 7: What is a database index, and what are the different types of indexes?

A database index is a data structure that improves the speed of data retrieval operations on a database table.

There are few different types of indexes that can be used in a database:

  • Primary index: a unique identifier is used to access the row directly.
  • Unique index: used to enforce the uniqueness of the indexed columns in a table.
  • Composite index: created on multiple columns of a table, is used to speed up the search process for multiple columns
  • Clustered index: determines the physical order of the data in a table

For a concrete example, say you had a table of NEXTDC customer payments with the following columns:

Here's what a clustered index on the column would look like:


A clustered index on the column would determine the physical order of the records in the table based on the . This means that the records with the earliest values would be stored together physically in the table, followed by records with later payment_date values. This speeds up queries that filter or sort the data based on the , as the records are already physically sorted in the table. For example, if you want to retrieve all the payments made in the month of June, the database can use the clustered index to quickly locate and retrieve the desired records without having to search through the entire table.

SQL Question 8: Analyze Customer Order and Product Data in NEXTDC

Write a SQL query to find out the total orders per customer and the details about the product they ordered most. You will need to join the table with the and tables.

Assuming we have the following tables:

Example Input:

order_idcustomer_idproduct_idorder_date
45120301/06/2022
46210501/15/2022
47120301/18/2022
48318701/20/2022
49210501/24/2022

Example Input:

customer_idfirst_namelast_name
1JohnDoe
2JaneDoe
3MarkSmith

Example Input:

product_idproduct_namecategory
203Product AElectronics
105Product BAppliances
187Product CFurniture

Example Output:

customer_nametotal_ordersmost_ordered_product
John Doe2Product A
Jane Doe2Product B
Mark Smith1Product C

Answer:


In this query, we join the , , and tables based on their respective IDs. We use the count function to find the total number of orders placed by each customer. We then display the customer's full name (concatenated first and last name), total number of orders, and the name of the most ordered product. The result is grouped by and and ordered in descending order of the total number of orders.

Because joins come up so often during SQL interviews, practice an interactive Spotify JOIN SQL question: Spotify JOIN SQL question

SQL Question 9: Calculate the Power Consumption

Suppose you are working for NEXTDC and you want to examine the company's power usage for its various data centers. Assume each data center consumes power based on the number of running servers and the power draw of each server. Specifically, keep in mind that older servers use more power than new ones.

You are given two tables: and .

The table has three columns: (an integer that uniquely identifies each center), (the number of servers at the center), and (a string for the city where the data center resides).

Example Input
center_idserver_countlocation
1400Sydney
2200Melbourne
3100Perth

The table has three columns: (unique identifier for each server), (number of years the server has been operating), and (the center that server resides in).

Example Input
server_idagecenter_id
121
251
332
412
543

Calculate the total power consumption for each data center, assuming the power draw of each server is watts per server. Then, round the total power for each center to the nearest whole watt.

Example Output
center_idlocationtotal_power
1Sydney3600
2Melbourne2600
3Perth1600

Answer:


In the above query, we join two tables on the column. Then, for each data center, we calculate the total power consumption by raising the server's age to power 2 and multiplying it by server count for that center. We use the function to round this total power to nearest whole watt. We use the and functions to get the total power for each data center.

To practice a very similar question try this interactive Amazon Server Utilization Time Question which is similar for calculating server usage time or this Microsoft Supercloud Customer Question which is similar for working with multivariate data.

Preparing For The NEXTDC SQL Interview

The key to acing a NEXTDC SQL interview is to practice, practice, and then practice some more! Besides solving the above NEXTDC SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google). DataLemur SQL and Data Science Interview Questions

Each problem on DataLemur has multiple hints, detailed solutions and best of all, there's an online SQL coding environment so you can right in the browser run your SQL query and have it checked.

To prep for the NEXTDC SQL interview it is also a great idea to solve SQL questions from other tech companies like:

In case your SQL skills are weak, forget about jumping right into solving questions – go learn SQL with this free SQL for Data Analytics course.

SQL tutorial for Data Scientists & Analysts

This tutorial covers SQL topics like math functions in SQL and Self-Joins – both of which come up often during NEXTDC SQL interviews.

NEXTDC Data Science Interview Tips

What Do NEXTDC Data Science Interviews Cover?

In addition to SQL query questions, the other question categories tested in the NEXTDC Data Science Interview are:

NEXTDC Data Scientist

How To Prepare for NEXTDC Data Science Interviews?

The best way to prepare for NEXTDC Data Science interviews is by reading Ace the Data Science Interview. The book's got:

  • 201 Interview Questions from companies like Google, Tesla, & Goldman Sachs
  • A Refresher covering Python, SQL & ML
  • Great Reviews (900+ reviews, 4.5-star rating)

Ace the Data Science Interview by Nick Singh Kevin Huo