11 Public Storage SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

At Public Storage, SQL is used day-to-day for analyzing customer demographics and rental patterns, and optimizing the inventory management of storage units. That's why Public Storage almost always evaluates jobseekers on SQL questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.

So, to help you prep, here’s 11 Public Storage SQL interview questions – able to solve them?

Public Storage SQL Interview Questions

11 Public Storage SQL Interview Questions

SQL Question 1: Identify High-frequency Renters from Public Storage

Public Storage is a company providing self-storage units - spaces which customers can rent to store their belongings. In this context, power users could be defined as those customers who rent storage units most frequently.

Your task is to write a SQL query to identify these power users. For the sake of this question, let's assume any customer who rents storage units more than twice in a month is considered a power user.

customers Example Input:
customer_idname
1Amy
2Bob
3Charlie
4David
5Eva
rentals Example Input:
rental_idcustomer_idrent_dateunit_id
112022-08-01101
222022-08-01102
312022-08-02103
432022-08-03104
522022-08-04105
612022-08-05106
742022-08-05107
852022-08-06108
932022-08-07109
1012022-08-08101

Answer:

SELECT c.customer_id, c.name FROM customers c JOIN ( SELECT customer_id, EXTRACT(MONTH FROM rent_date) as month, COUNT(*) as rental_count FROM rentals GROUP BY customer_id, month HAVING COUNT(*) > 2 ) r ON c.customer_id = r.customer_id

In the subquery, we group by both customer and month, counting the number of rentals for each month. In the outer query, we join this result with the customers table to include the customer names. The HAVING clause filters to include only those rows where the rental_count for the month is more than 2. This will give us the list of customers who have rented more than twice in any given month.

To practice a similar VIP customer analysis question on DataLemur's free online SQL coding environment, try this Microsoft Azure Cloud SQL Interview Question: Microsoft SQL Interview Question: Super Cloud Customer

SQL Question 2: Highly-Paid Employees

Given a table of Public Storage employee salary data, write a SQL query to find employees who earn more money than their direct boss.

Public Storage employees Example Input:

employee_idnamesalarydepartment_idmanager_id
1Emma Thompson38001
2Daniel Rodriguez2230110
3Olivia Smith800018
4Noah Johnson680028
5Sophia Martinez1750110
8William Davis70002NULL
10James Anderson40001NULL

Example Output:

employee_idemployee_name
3Olivia Smith

This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.

You can solve this interview question and run your code right in DataLemur's online SQL environment:

Employees Earning More Than Their Manager

Answer:

First, we perform a SELF-JOIN where we treat the first employee table (mgr) as the managers' table and the second employee table (emp) as the employees' table. Then we use a WHERE clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.

SELECT emp.employee_id AS employee_id, emp.name AS employee_name FROM employee AS mgr INNER JOIN employee AS emp ON mgr.employee_id = emp.manager_id WHERE emp.salary > mgr.salary;

If the solution above is hard to understand, you can find a step-by-step solution with hints here: Employee Salaries Higher Than Their Manager.

SQL Question 3: Can you describe the different types of joins in SQL?

A join in SQL combines rows from two or more tables based on a shared column or set of columns.

Four types of JOINs exist in SQL. To demonstrate each one, say you had a table of Public Storage orders and Public Storage customers.

INNER JOIN: When there is a match in the shared key or keys, rows from both tables are retrieved. In this example, an INNER JOIN between the Orders and Customers tables would retrieve rows where the customer_id in the Orders table matches the customer_id in the Customers table.

LEFT JOIN: A LEFT JOIN retrieves all rows from the left table (in this case, the Orders table) and any matching rows from the right table (the Customers table). If there is no match in the right table, NULL values will be returned for the right table's columns.

RIGHT JOIN: A RIGHT JOIN retrieves all rows from the right table (in this case, the Customers table) and any matching rows from the left table (the Orders table). If there is no match in the left table, NULL values will be returned for the left table's columns.

FULL OUTER JOIN: A FULL OUTER JOIN retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be returned for the columns of the non-matching table.

Public Storage SQL Interview Questions

SQL Question 4: Calculate the monthly average rental price per unit type.

As a Data Analyst at Public Storage, you are asked to analyze the company's rental income by calculating the average price per month for each type of storage unit. Consider the hypothetical scenario where the data of all transactions are stored in one table named rental_prices. Each row in this table represents a single rental transaction. Each storage unit type has a unique identifier unit_id.

Please write a PostgreSQL query that calculates the monthly average rental price for each type of storage unit. The table includes rental_price, transaction_date (in 'YYYY-MM-DD HH:MI:SS' format) and unit_id.

rental_prices Example Input:
transaction_idtransaction_dateunit_idrental_price
12022-06-08 00:00:00A150
22022-06-10 00:00:00B160
32022-06-18 00:00:00B160
42022-07-26 00:00:00B160
52022-07-05 00:00:00A155
Example Output:
monthunit_idaverage_price
6A150.00
6B160.00
7A155.00
7B160.00

Answer:

SELECT EXTRACT(MONTH FROM transaction_date) as month, unit_id, AVG(rental_price) as average_price FROM rental_prices GROUP BY month, unit_id ORDER BY month, unit_id;

This query calculates the average price for each storage unit type by month from the rental_prices table. The EXTRACT(MONTH FROM transaction_date) function is used to extract the month from the date of each transaction.

To practice another window function question on DataLemur's free interactive coding environment, solve this Amazon BI Engineer interview question: Amazon Business Intelligence SQL Question

Read Public Storage's report on the three months ended March 31st.

SQL Question 5: What are stored procedures, and why are they useful?

Stored procedures are like functions in Python – they can accept input params and return values, and are used to encapsulate complex logic.

For example, if you worked as a Data Analyst in support of the Marketing Analytics team at Public Storage, a common task might be to find the conversion rate for your ads given a specific time-frame. Instead of having to write this query over-and-over again, you could write a stored procedure like the following:

CREATE FUNCTION get_conversion_rate(start_date DATE, end_date DATE, event_name TEXT) RETURNS NUMERIC AS $BODY$ BEGIN RETURN (SELECT COUNT(*) FROM events WHERE event_date BETWEEN start_date AND end_date AND event_name = 'conversion') / (SELECT COUNT(*) FROM events WHERE event_date BETWEEN start_date AND end_date AND event_name = 'impression'); END; $BODY$ LANGUAGE 'plpgsql';

To call this stored procedure, you'd execute the following query:

SELECT get_conversion_rate('2023-01-01', '2023-01-31', 'conversion');

SQL Question 6: Filter Customers Based on Rental Period and Payments

You are given a Public Storage database with a table named customers containing customer details and a rentals table containing rental information. You need to list all customers who have been renting for more than six months and are up-to-date with their payments.

The customers table is defined as follows:

customers Example Input:
customer_idfirst_namelast_nameemail
101JohnDoejohndoe@example.com
102JaneDoejanedoe@example.com
103SamSmithsamsmith@example.com
104SaraJohnsonsarajohnson@example.com
105JamesBrownjamesbrown@example.com

The rentals table is defined as follows:

rentals Example Input:
rental_idcustomer_idstorage_unit_idstart_dateend_daterental_status
1101101/01/2021NULLPaid
2102206/01/2022NULLDue
3103301/01/2022NULLPaid
4104401/01/202012/31/2020Paid
5105112/01/2022NULLPaid

In this case, the start_date and end_date are in the format 'MM/DD/YYYY'. If the end_date is NULL, it means that the customer is still renting the storage unit. The rental_status indicates whether the customer has paid their most recent bill. If the rental_status is 'Paid', it means the customer is up to date with their payments.

Answer:

Here is the PostgreSQL query which can be used to solve this problem:

SELECT c.customer_id, c.first_name, c.last_name, c.email FROM customers c JOIN rentals r ON c.customer_id = r.customer_id WHERE r.rental_status = 'Paid' AND r.start_date <= NOW() - INTERVAL '6 months' AND r.end_date IS NULL;

This query first joins customers and rentals on customer_id. It then filters the joined data where rental_status is 'Paid', start_date is more than six months ago, and end_date is NULL, indicating that the rental is ongoing. The query then selects the customer_id, first_name, last_name, and email from the filtered data.

SQL Question 7: What's denormalization, and when does it make sense to do it?

Denormalization is the process of modifying a database schema in a way that deviates from the typical rules of normalization (1NF, 2NF, 3NF, etc.). There's a few reasons to denormalize a database:

Improved performance: Joins are slow AF, especially when dealing with the massive datasets that are typically used at Public Storage. Denormalization can improve the performance of a database by reducing the number of joins that are required to retrieve data. This can be particularly useful when the database is being used for querying and reporting purposes, as joins can be expensive and slow.

Scalability: By reducing the amount of data that needs to be read and processed to execute a query, denormalization can enhance the scalability of a database. This can be useful when the database is anticipated to handle a large number of read-only queries (such as in OLAP use cases).

Ease of use: Denormalization can also make it easier for users to work with a database by providing them with a more intuitive and straightforward data model.

Because denormalization can create more complex update and delete operations, and pose potential data integrity issues, consider denormalization only if joins are causing performance bottlenecks.

SQL Question 8: Average Volume of Storage Units Rented

As a data analyst at Public Storage, you're tasked with finding out the average volume (in cubic feet) of storage units rented each month. You have access to the following two tables:

rentals Example Input:
rental_idcustomer_idstorage_idstart_dateend_date
20001789100012022-06-012022-06-30
20002654100022022-06-152022-07-15
20003321100032022-07-012022-07-28
20004987100012022-07-012022-07-15
20005456100022022-07-02NULL
storage_units Example Input:
storage_idvolume_cft
10001150
10002200
10003350

Create a SQL query to determine the average volume of storage units rented each month.

Answer:

SELECT EXTRACT(MONTH FROM r.start_date) AS month, AVG(s.volume_cft) AS avg_volume_cft FROM rentals r INNER JOIN storage_units s ON r.storage_id = s.storage_id GROUP BY month ORDER BY month;

In this query, we first join the rentals and storage_units tables using the storage_id shared between the tables. Then, we group by the month portion of the start_date column in the rentals table and calculate the average volume of the storage units rented during each month. The results are sorted by the month.

To practice a very similar question try this interactive Amazon Maximize Prime Item Inventory Question which is similar for requiring calculations on item volumes or this Google Average Review Ratings Question which is similar for needing average calculation per month.

SQL Question 9: Retrieve Customer Records from City

Imagine you are a data analyst at Public Storage. Your manager has asked you to retrieve customer data for a specific branch. The branch is not identified by the branch_id, but rather by the city. Your task is to list all the customers whose records contain the name of the city, e.g., 'San Francisco'.

customer_info Example Input:
customer_idfirst_namelast_namebranch_idcity
7546JohnDoe123Los Angeles
2045JaneSmith456San Francisco
6213EmilyBrown789San Diego
9047RobertWhite123San Francisco
8673MaryGreen456Los Angeles
branch Example Input:
branch_idbranch_namecity
123Central L.A.Los Angeles
456Downtown SFSan Francisco
789Old Town SDSan Diego

To return the results, you will need to write an SQL query.

Answer:

SELECT ci.customer_id, ci.first_name, ci.last_name FROM customer_info as ci JOIN branch as b ON ci.branch_id = b.branch_id WHERE ci.city LIKE 'San Francisco'

This query performs a join operation on the customer information and branch tables using the branch_id field. It then filters the results to return only those customer records where the city field contains the string 'San Francisco'. The results will list all the customers who have a record at the San Francisco branch of Public Storage.

SQL Question 10: Why would you use the CHECK SQL constraint?

The CHECK constraint is used to specify a condition that the data in a column must meet. If a row is inserted or updated and the data in the column doesn't meet the condition specified by the CHECK constraint, the operation will sadly fail.

For example, you might use a CHECK constraint to ensure that a column contains only positive numbers, or that a date is within a certain range.

For example, if you had a table of Public Storage employees, here's an example of how to use the CHECK constraint in a CREATE TABLE statement:

CREATE TABLE public_storage_employees ( id INT PRIMARY KEY, salary INT CHECK (salary > 0), hire_date DATE CHECK (hire_date >= '1970-01-01') );

SQL Question 11: Joining and Analyzing Public Storage Data

You are given two tables - customers and rentals. The customers table has information about each customer like their id, name, and email. The rentals table tracks all the storage unit rentals and includes the renter's id, the rental unit's id, the date of the rental, and the rental price.

Write a SQL query to find the average rental price paid by each customer, also return the customer's name and id for easy identification.

Below are the structures of the customers and rentals tables.

customers Example Input:
customer_idcustomer_nameemail
1John Doejohndoe@example.com
2Jane Smithjanesmith@example.com
3Alice Johnsonalicejohnson@example.com
rentals Example Input:
renter_idunit_idrental_daterental_price
1506/08/2022100
1606/15/2022120
2706/18/2022110
2807/26/2022100
3907/05/2022125

Answer:

SELECT c.customer_id, c.customer_name, AVG(r.rental_price) AS average_rental_price FROM customers c JOIN rentals r ON c.customer_id = r.renter_id GROUP BY c.customer_id, c.customer_name

This PostgreSQL query works by joining the customer and rentals tables on the customer id (which is the renter id in the rentals table). Then it groups by customer id and name, and applies the AVG function to compute the average rental price for each customer.

Since join questions come up frequently during SQL interviews, take a stab at this Snapchat JOIN SQL interview question: Snapchat SQL Interview question using JOINS

Public Storage SQL Interview Tips

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. Besides solving the earlier Public Storage SQL interview questions, you should also solve the 200+ tricky sql questions on DataLemur which come from companies like Microsoft, Google, and Facebook. DataLemur SQL and Data Science Interview Questions

Each DataLemur SQL question has hints to guide you, full answers and best of all, there's an online SQL code editor so you can right online code up your SQL query and have it executed.

To prep for the Public Storage SQL interview you can also be wise to practice SQL questions from other real estate and REIT companies like:

However, if your SQL query skills are weak, don't worry about diving straight into solving questions – go learn SQL with this SQL tutorial for Data Analytics.

DataLemur SQL Tutorial for Data Science

This tutorial covers things like manipulating string/text data and GROUP BY – both of which pop up frequently during Public Storage interviews.

Public Storage Data Science Interview Tips

What Do Public Storage Data Science Interviews Cover?

Besides SQL interview questions, the other types of questions tested in the Public Storage Data Science Interview include:

Public Storage Data Scientist

How To Prepare for Public Storage Data Science Interviews?

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

  • 201 interview questions taken from FAANG, tech startups, and Wall Street
  • a refresher covering Product Analytics, SQL & ML
  • over 1000+ reviews on Amazon & 4.5-star rating

Ace the DS Interview