logo

8 JLL SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

JLL employees write SQL queries daily for analyzing real estate market trends and managing property-related datasets. For this reason JLL frequently asks SQL coding questions during interviews for Data Science, Data Engineering and Data Analytics jobs.

Thus, to help you study, here's 8 JLL SQL interview questions – can you answer each one?

JLL SQL Interview Questions

8 JLL SQL Interview Questions

SQL Question 1: Calculate Average Rental Price by Property Type and City

JLL manages a large number of properties internationally. One common analysis that they may need to perform is determining the average rental price of different types of properties in various cities.

Let's assume that 'properties' and 'rentals' are two tables in JLL's database. The 'properties' table contains information about each property, including property_id, type (residential, commercial, etc.), and city. The 'rentals' table has information about rental transactions, such as rental_id, property_id, start_date, and rent_price.

Example Input:
property_idtypecity
101ResidentialChicago
102CommercialNew York
103ResidentialLondon
104CommercialChicago
105ResidentialNew York
Example Input:
rental_idproperty_idstart_daterent_price
50110106/08/20222000
50210206/10/20224000
50310307/18/20221500
50410407/26/20223500
50510507/05/20222200

The task is to write a SQL query that calculates the average rent by property type and city for each month.

Example Output:
MonthCityProperty TypeAverage Rent
6ChicagoResidential2000
6New YorkCommercial4000
7LondonResidential1500
7ChicagoCommercial3500
7New YorkResidential2200

Answer:


This query first joins 'rentals' and 'properties' on 'property_id'. Then the data is grouped by month, city, and property type, and the average rental price is calculated for each group. The results are ordered by month, city, and property type for easy readability.

Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur

SQL Interview Questions on DataLemur

SQL Question 2: Top Department Salaries

Given a table of JLL employee salary data, write a SQL query to find the top three highest paid employees within each department.

JLL Example Input:

employee_idnamesalarydepartment_id
1Emma Thompson38001
2Daniel Rodriguez22301
3Olivia Smith20001
4Noah Johnson68002
5Sophia Martinez17501
8William Davis68002
10James Anderson40001

Example Input:

department_iddepartment_name
1Data Analytics
2Data Science

Example Output:

department_namenamesalary
Data AnalyticsJames Anderson4000
Data AnalyticsEmma Thompson3800
Data AnalyticsDaniel Rodriguez2230
Data ScienceNoah Johnson6800
Data ScienceWilliam Davis6800

Test your SQL query for this problem directly within the browser on DataLemur:

Top 3 Department Salaries

Answer:

We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.


If the solution above is hard to understand, you can find a detailed solution with hints here: Top 3 Department Salaries.

SQL Question 3: What's the major difference between and ?

The clause works similarly to the clause, but it is used to filter the groups of rows created by the clause rather than the rows of the table themselves.

For example, say you were analyzing JLL sales data:


This query retrieves the total sales for all products in each region, and uses the clause to only sales made after January 1, 2023. The rows are then grouped by region and the clause filters the groups to include only those with total sales greater than $500k.

JLL SQL Interview Questions

SQL Question 4: Calculate Average Lease Amount by Property Type for JLL

As a data analyst at JLL, a leading professional services firm that specializes in real estate and investment management, you're tasked to find the average lease amount per property type in the past year. This would help the company to better understand the trends and dynamics in the local real estate market.

Sample Input

property_idproperty_type
1Commercial
2Residential
3Industrial
4Commercial
5Residential

Sample Input

lease_idproperty_idlease_start_datelease_amount
101101/01/2020$5000
102215/01/2020$3000
103101/02/2020$5500
104315/02/2020$6000
105201/03/2020$2800
106415/03/2020$5200
107501/04/2020$3500

Example Output

property_typeavg_lease_amount
Commercial$5233.33
Residential$3150.00
Industrial$6000.00

Answer:


Explanation:

The query first joins the leases and properties tables on the property_id column. It then applies a WHERE condition to filter only leases that started in the past year (from '01/01/2020' to '31/12/2020'). Finally, the calculation for the average lease amount by property type is performed using the AVG function and displayed as avg_lease_amount. The results are grouped by property_type, thus giving us the average lease amount for each type of property from last year.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating top entities based on properties or this Wayfair Y-on-Y Growth Rate Question which is similar for analyzing data over a time period.

SQL Question 5: Could you describe a self-join and provide a scenario in which it would be used?

A self-join is a type of JOIN where a table is joined to itself. To execute a self-join, you must include the table name twice in the FROM clause and assign a different alias to each instance. You can then join the two copies of the table using a JOIN clause, and use a WHERE clause to specify the relationship between the rows.

One common use case for self-joins is to compare data within a single table. For example, you might use a self-join to compare the salaries of employees within the same department, or to identify pairs of products that are frequently purchased together (like in this real Walmart SQL interview question)[https://datalemur.com/questions/frequently-purchased-pairs].

For a more concrete example, imagine you had website visitor data for JLL, exported from the company's Google Analytics account. To help the web-dev team, you needed to analyze pairs of pages for UX or navigational issues. As part of that analysis, you wanted to produce all pairs of URLs, but needed to exclude pairs where both URLs were the same since that is not a valid pair.

You could use the following self-join:


This query retrieves the url of each page () along with the url of the page that referred to it (). The self-join is performed using the , which identifies the id of the page that referred the visitor to the current page, and excludes any pages that referred to themselves (i.e., data anomalies since pages shouldn't refer to themself).

Read about how JLL uses 3D imaging and data visualization to bring their products and services to the next level!

SQL Question 6: Pattern Match Customer Records

As a property management company, JLL needs to handle customer data effectively. Suppose you are provided with the customer records database. You need to find all customers whose names start with the letter 'A' and are located in 'New York'.

Please write an SQL query to filter these records using the SQL keyword LIKE.

The following provides example input and expected output:

Example Input:
customer_idnameagecitystate
1Alice25New YorkNY
2Bob30San FranciscoCA
3Andrew35New YorkNY
4Ann40Los AngelesCA
5Amber28New YorkNY
Example Output:
customer_idnameagecitystate
1Alice25New YorkNY
3Andrew35New YorkNY
5Amber28New YorkNY

Answer:


The above query uses the wild card character in conjunction with the operator in SQL to filter out all customer records whose name start with 'A'. The operator is used to further filter the results to include only those customers who are located in 'New York'.

SQL Question 7: What's the operator do, and can you give an example?

is used to combine the output of multiple statements into one big result!

For a concrete example, say you were doing an HR Analytics project for JLL and needed to analyze both JLL's employees and contractors who were hired after 2022 started. You could use in the following way:


SQL Question 8: Analyzing Customer and Transactions Data

JLL has two key tables in its database: and . The table stores information about each customer, and the table stores information about each real estate transaction made by the customers. The columns in the table include , , , and , whereas the table includes , , , , and .

You are required to write a SQL query that will return a list of customers and the total amount they have spent on real estate transactions. The result should include , , and and should be ordered by in descending order.

Example Input:
customer_idfirst_namelast_nameemail
1001JohnDoejohn.doe@example.com
1002JaneSmithjane.smith@example.com
1003BobJohnsonbob.johnson@example.com
Example Input:
transaction_idtransaction_datecustomer_idproperty_idtransaction_amount
20012022-01-1510013001200000.00
20022022-02-1510013002250000.00
20032022-04-2210023003175000.00
20042022-07-1010033004225000.00

Answer:


This query initially performs an inner join on the and tables based on the . The grouped output provides a list of customers (first and last names) along with their total spent amount on real estate transactions. The results are presented in descending order of .

Because join questions come up so often during SQL interviews, practice this Spotify JOIN SQL question: Spotify JOIN SQL question

Preparing For The JLL 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. Beyond just solving the earlier JLL SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups. DataLemur Question Bank

Each SQL question has hints to guide you, fully explained answers along with a discussion board to see how others solved it and most importantly, there is an online SQL code editor so you can easily right in the browser your SQL query and have it executed.

To prep for the JLL SQL interview you can also be helpful to practice SQL problems from other real estate and REIT companies like:

But if your SQL skills are weak, forget about diving straight into solving questions – strengthen your SQL foundations with this DataLemur SQL tutorial.

SQL tutorial for Data Analytics

This tutorial covers things like WHERE vs. HAVING and rank window functions – both of these pop up often in SQL job interviews at JLL.

JLL Data Science Interview Tips

What Do JLL Data Science Interviews Cover?

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

JLL Data Scientist

How To Prepare for JLL Data Science Interviews?

I'm a bit biased, but I think the best way to prepare for JLL Data Science interviews is to read the book I wrote: Ace the Data Science Interview.

The book has 201 interview questions sourced from Facebook, Google, & Amazon. It also has a refresher covering SQL, Product-Sense & ML. And finally it's helped thousands of people land their dream job in data, which is why it's got over 1000+ 5-star reviews on Amazon.

Ace the Data Science Interview