8 JLL SQL Interview Questions (Updated 2025)

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.

properties Example Input:

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

SELECT
    EXTRACT(MONTH FROM start_date) AS Month,
    p.city AS City,
    p.type AS "Property Type",
    AVG(rent_price) AS "Average Rent"
FROM
    rentals r
JOIN 
    properties p ON r.property_id = p.property_id
GROUP BY
    EXTRACT(MONTH FROM start_date),
    p.city,
    p.type
ORDER BY
    Month,
    p.city,
    p.type;

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 employees Example Input:

department 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.

WITH ranked_salary AS (
  SELECT
    name,
    salary,
    department_id,
    DENSE_RANK() OVER (
      PARTITION BY department_id ORDER BY salary DESC) AS ranking
  FROM employee
)


SELECT
  d.department_name,
  rs.name,
  rs.salary
FROM ranked_salary AS rs
INNER JOIN department AS d
   ON rs.department_id = d.department_id
WHERE rs.ranking <= 3
ORDER BY d.department_id, rs.salary DESC, rs.name ASC;

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 WHERE and HAVING?

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

For example, say you were analyzing JLL sales data:

SELECT region, SUM(sales)
FROM jll_sales
WHERE date > '2023-01-01'
GROUP BY region
HAVING SUM(sales) > 500000;

This query retrieves the total sales for all products in each region, and uses the WHERE clause to only sales made after January 1, 2023. The rows are then grouped by region and the HAVING 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.

properties Sample Input

leases Sample Input

Example Output

Answer:

SELECT p.property_type, AVG(l.lease_amount) as avg_lease_amount
FROM leases l
JOIN properties p ON p.property_id = l.property_id
WHERE lease_start_date BETWEEN '01/01/2020' and '31/12/2020'
GROUP BY p.property_type

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:

SELECT page1.url AS page_url, page2.url AS referred_from
FROM google_analytics AS page1
JOIN google_analytics AS page2 ON page1.referrer_id = page2.id
WHERE page1.id <> page2.id;

This query retrieves the url of each page (page1.url) along with the url of the page that referred to it (page2.url). The self-join is performed using the referrer_id field, 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:

customers Example Input:

Example Output:

Answer:

SELECT *
FROM customers
WHERE name LIKE 'A%' AND state = 'NY';

The above query uses the % wild card character in conjunction with the LIKE operator in SQL to filter out all customer records whose name start with 'A'. The AND 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 UNION operator do, and can you give an example?

UNION is used to combine the output of multiple SELECT 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 UNION in the following way:

SELECT name, job_title, hire_date, salary
FROM jll_employees
WHERE hire_date > '2022-01-01';

UNION

SELECT name, job_title, hire_date, salary
FROM jll_contractors
WHERE hire_date > '2022-01-01'

SQL Question 8: Analyzing Customer and Transactions Data

JLL has two key tables in its database: customers and transactions. The customers table stores information about each customer, and the transactions table stores information about each real estate transaction made by the customers. The columns in the customers table include customer_id, first_name, last_name, and email, whereas the transactions table includes transaction_id, transaction_date, customer_id, property_id, and transaction_amount.

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 first_name, last_name, and total_transaction_amount and should be ordered by total_transaction_amount in descending order.

customers Example Input:

transactions Example Input:

Answer:

SELECT c.first_name, c.last_name, SUM(t.transaction_amount) as total_transaction_amount 
FROM customers c 
JOIN transactions t ON c.customer_id = t.customer_id 
GROUP BY c.first_name, c.last_name 
ORDER BY total_transaction_amount DESC;

This query initially performs an inner join on the customers and transactions tables based on the customer_id. 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 total_transaction_amount.

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.