10 CBRE Group SQL Interview Questions (Updated 2024)
Data Analysts and Data Engineers at CBRE Group code up SQL queries as a core part of their job. They use SQL for analyzing real estate market trends and predictions, and managing vast property databases. For this reason CBRE Group usually asks SQL interview questions.
Thus, to help you prep, here’s 10 CBRE Group SQL interview questions – can you solve them?
10 CBRE Group SQL Interview Questions
SQL Question 1: Analyze Property Rents using SQL Window Function
CBRE Group deals with real estate services and investment. As an analyst in CBRE, you've been handed a task to analyze the monthly average rent for the different types of properties over time. Specifically, you're required to compute the moving average of the rent for each property type in each quarter starting from Q2. The moving average should be based on the current quarter and the previous quarter.
Given a dataset of the property rents, your task is to write a SQL query to compute this moving average.
Example Input:
| rent_id | property_id | date | property_type | rent |
|---|---|---|---|---|
| 1001 | 200 | 2022-01-15 | office | 3000 |
| 1002 | 201 | 2022-01-17 | residential | 1500 |
| 1003 | 200 | 2022-04-15 | office | 3200 |
| 1004 | 201 | 2022-04-17 | residential | 1600 |
| 1005 | 202 | 2022-07-15 | retail | 2400 |
| 1006 | 200 | 2022-07-17 | office | 3500 |
| 1007 | 201 | 2022-10-15 | residential | 1700 |
Example Output:
| quarter | property_type | avg_rent |
|---|---|---|
| Q2 | office | 3100 |
| Q2 | residential | 1550 |
| Q3 | office | 3350 |
| Q3 | residential | 1650 |
| Q3 | retail | 2400 |
| Q4 | residential | 1650 |
Answer:
This query first extracts the quarter from the date and casts it as an integer to form a window for each property type. In the window function, it averages the rent for the current row and the previous row, simulating a moving average over two quarters. Note that the window is ordered by the quarter to ensure that the averages are computed in the correct temporal sequence. The result is a list of the moving averages for each property type for each quarter, starting from Q2.
For more window function practice, solve this Uber SQL Interview Question on DataLemur's interactive coding environment:
SQL Question 2: Top 3 Salaries
Given a table of CBRE Group employee salaries, write a SQL query to find the top three highest paid employees in each department.
CBRE Group Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Solve this interview question interactively 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 code above is confusing, you can find a detailed solution with hints here: Top 3 Department Salaries.
Learn more about CBRE and read about their Global Data Center Trends from 2024!
SQL Question 3: What's a self-join, and when would you use one?
A self-join is a type of join in which a table is joined to itself. To perform a self-join, you need to specify the table name twice in the clause, and give each instance of the table a different alias. You can then join the two instances of the table using a clause, and use a clause to specify the relationship between the rows.
For example, say you were doing an HR analytics project and needed to analyze how much all CBRE Group employees in the same department interact with each other. Here's a self-join query you could use to retrieve all pairs of CBRE Group employees who work in the same department:
This query returns all pairs of CBRE Group employees who work in the same department, and excludes pairs where the employee's id is the same (since this would represent the same CBRE Group employee being paired with themselves).
SQL Question 4: Property and Lease Analysis
CBRE Group is a multinational commercial real estate and property investment company. The company has numerous properties around the world, which it leases to various businesses. Your task is to design a database for managing these properties and their leases, and then answer the following question: What is the average monthly rent across all properties in New York City?
Consider two tables: and .
The table stores information about each property, including its location and the total number of leaseable square footage. The table, on the other hand, includes lease agreement details such as the property involved, the tenant, the duration of the lease, and the monthly rent.
Example Input:
| property_id | location | total_sq_ft |
|---|---|---|
| 101 | New York City | 50000 |
| 102 | Los Angeles | 30000 |
| 103 | New York City | 45000 |
| 104 | Chicago | 40000 |
Example Input:
| lease_id | property_id | tenant | lease_start_date | lease_end_date | monthly_rent |
|---|---|---|---|---|---|
| 5001 | 101 | Tenant A | 01/01/2002 | 12/31/2002 | 5000 |
| 5002 | 101 | Tenant B | 01/01/2003 | 12/31/2004 | 6000 |
| 5003 | 102 | Tenant C | 01/01/2003 | 12/31/2004 | 4000 |
| 5004 | 103 | Tenant D | 01/01/2005 | 12/31/2007 | 5500 |
Your task is to calculate the average monthly rent of all properties located in New York City.
Answer:
Here is the SQL query to calculate the average monthly rent for properties in New York City:
This query first joins the table to the table based on the . It then filters to only include properties located in New York City and finally calculates the average rent of the filtered leases.
SQL Question 5: When would you use the / commands in SQL?
Note: interviews at CBRE Group often aren't trying to test you on a specific flavor of SQL. As such, you don't need to exactly know that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle – you just need to know the general concept!
Your answer should mention that the / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.
Here's a PostgreSQL example of using EXCEPT to find all of CBRE Group's Facebook video ads with more than 50k views that aren't also being run on YouTube:
If you want to retain duplicates, you can use the EXCEPT ALL operator instead of EXCEPT. The EXCEPT ALL operator will return all rows, including duplicates.
SQL Question 6: Calculating the Average Rent of Properties in Each City
CBRE Group specializes in real estate services and investment. Assume you are given a table representing properties managed by CBRE Group. The table contains information about the , , and .
Your task is to write a SQL query to find the average rent of properties in each city managed by the CBRE Group.
Example Input:
| property_id | city | rent |
|---|---|---|
| 1 | Los Angeles | 2500 |
| 2 | New York | 3500 |
| 3 | Los Angeles | 3000 |
| 4 | San Francisco | 4000 |
| 5 | New York | 3700 |
| 6 | San Francisco | 5000 |
| 7 | Los Angeles | 2000 |
Answer:
The function calculates the average rent per city. is used to separate the data into different groups, each representing a city in this case. The clause sorts the output by the average rent in descending order.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating maximum values grouped by a category or this Amazon Average Review Ratings Question which is similar for calculating average values grouped by a category.
SQL Question 7: Give a few ways in SQL that you can identify duplicate records in a table?
One way to find duplicatesis to use a clause and then use to find groups
You could also use the operator:
SQL Question 8: Calculate CBRE Group's digital ad click-through and conversion rates
As a data analyst for the CBRE Group, your task is to analyze the company's marketing performance. Given a database of digital ad impressions, clicks and conversions, calculate the click-through-rate (CTR) and conversion rate (Conversion Rate) for each ad. The CTR is defined as the total number of clicks divided by the total number of impressions. The conversion rate is the number of conversions divided by the number of impressions.
Example Input:
| ad_id | impressions | clicks | conversions |
|---|---|---|---|
| 1 | 1000 | 50 | 5 |
| 2 | 2000 | 150 | 10 |
| 3 | 3000 | 300 | 20 |
| 4 | 5000 | 500 | 25 |
| 5 | 8000 | 800 | 30 |
Answer:
This PostgreSQL query calculates CBRE Group's digital ad CTR and conversion rates. We accomplish this by dividing the number of clicks and conversions by the number of impressions for each ad, and multiplying by 100 to get a percent value. The final result is a table that helps us understand how well our digital ads are performing.
Example Output:
| ad_id | impressions | clicks | conversions | click_through_rate | conversion_rate |
|---|---|---|---|---|---|
| 1 | 1000 | 50 | 5 | 5.00 | 0.50 |
| 2 | 2000 | 150 | 10 | 7.50 | 0.50 |
| 3 | 3000 | 300 | 20 | 10.00 | 0.67 |
| 4 | 5000 | 500 | 25 | 10.00 | 0.50 |
| 5 | 8000 | 800 | 30 | 10.00 | 0.38 |
To solve a similar SQL problem on DataLemur's free online SQL code editor, try this Meta SQL interview question:
SQL Question 9: Average Real Estate Listing Prices
Given the properties listed by CBRE Group, write an SQL query to find the average list price for each property type (e.g., residential, commercial) in each city for the year 2022.
Example Input:
| listing_id | city | list_date | property_type | price |
|---|---|---|---|---|
| 1012 | New York | 01/15/2022 | Residential | 2300000 |
| 1045 | Los Angeles | 03/02/2022 | Commercial | 4500000 |
| 1078 | New York | 05/20/2022 | Residential | 3300000 |
| 1099 | Los Angeles | 07/14/2022 | Commercial | 6000000 |
| 1056 | New York | 09/06/2022 | Commercial | 4200000 |
Example Output:
| city | property_type | avg_price |
|---|---|---|
| New York | Residential | 2800000 |
| Los Angeles | Commercial | 5250000 |
| New York | Commercial | 4200000 |
Answer:
This SQL block will filter listings from the year 2022, then group them by city and property type. The AVG function is then used on the price column to calculate average listing price for each group.
SQL Question 10: What are the various types of joins used in SQL?
Joins in SQL allow you to combine data from different tables based on a shared key or set of keys.
Four JOIN types are available in SQL. For an example of each one, say you had sales data exported from CBRE Group's Salesforce CRM stored in a PostgreSQL database, and had access to two tables: and .
- : retrieves rows from both tables where there is a match in the shared key or keys. For example, an INNER JOIN between the Sales table and the Customers table would retrieve only the rows where the customer_id in the Sales table matches the customer_id in the table.
- : retrieves all rows from the left table (in this case, the sales table) and any matching rows from the right table (the table). If there is no match in the right table, NULL values will be returned for the right table's columns.
- : retrieves all rows from the right table (in this case, the customers table) and any matching rows from the left table (the sales table). If there is no match in the left table, NULL values will be returned for the left table's columns.
- : 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.
Preparing For The CBRE Group SQL Interview
The key to acing a CBRE Group SQL interview is to practice, practice, and then practice some more!
In addition to solving the earlier CBRE Group SQL interview questions, you should also solve the 200+ tricky sql questions on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups.
Each DataLemur SQL question has multiple hints, full answers and most importantly, there's an online SQL code editor so you can right in the browser run your query and have it checked.
To prep for the CBRE Group SQL interview you can also be wise to solve SQL problems from other real estate and REIT companies like:
In case your SQL skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this DataLemur SQL tutorial.
This tutorial covers topics including CASE/WHEN statements and filtering strings using LIKE – both of which come up frequently during CBRE Group SQL interviews.
CBRE Group Data Science Interview Tips
What Do CBRE Group Data Science Interviews Cover?
In addition to SQL query questions, the other topics covered in the CBRE Group Data Science Interview include:
- Statistics and AB Testing Questions
- Python or R Coding Questions
- Product Analytics Questions
- Machine Learning and Predictive Modeling Questions
- Behavioral Interview Questions
How To Prepare for CBRE Group Data Science Interviews?
To prepare for CBRE Group Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from FAANG (FB, Apple, Amazon, Netflix, Google)
- a refresher on SQL, Product-Sense & ML
- over 1000+ reviews on Amazon & 4.5-star rating