8 JLL SQL Interview Questions (Updated 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?

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:

Example Input:

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

Example Output:

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 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:

Example Input:

Example Output:

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

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.

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

Sample Input

Example Output

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:

Example Output:

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:

Example Input:

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:

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.

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:

• BlackRock SQL questions
• CBRE Group SQL questions
• Blackstone SQL questions

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

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:

• Probability & Statistics Questions
• Python or R Coding Questions
• Product Analytics Questions
• Machine Learning Questions
• Behavioral & Resume-Based Questions

You should also familiarize yourself deeply with JLL's Azara Data Insights platform, because your work will likely be supporting this platform in some way!

How To Prepare for JLL Data Science Interviews?

Besides understanding the company values, and JLLs products, the next best way to prepare for JLL Data Science interviews is to read the book 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.