# 10 CBRE Group SQL Interview Questions (Updated 2024)

Updated on

June 30, 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_idproperty_iddateproperty_typerent
10012002022-01-15office3000
10022012022-01-17residential1500
10032002022-04-15office3200
10042012022-04-17residential1600
10052022022-07-15retail2400
10062002022-07-17office3500
10072012022-10-15residential1700
##### Example Output:
quarterproperty_typeavg_rent
Q2office3100
Q2residential1550
Q3office3350
Q3residential1650
Q3retail2400
Q4residential1650

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

Solve this interview question interactively on DataLemur:

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.

### 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_idlocationtotal_sq_ft
101New York City50000
102Los Angeles30000
103New York City45000
104Chicago40000
##### Example Input:
lease_idproperty_idtenantlease_start_datelease_end_datemonthly_rent
5001101Tenant A01/01/200212/31/20025000
5002101Tenant B01/01/200312/31/20046000
5003102Tenant C01/01/200312/31/20044000
5004103Tenant D01/01/200512/31/20075500

Your task is to calculate the average monthly rent of all properties located in New York City.

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_idcityrent
1Los Angeles2500
2New York3500
3Los Angeles3000
4San Francisco4000
5New York3700
6San Francisco5000
7Los Angeles2000

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:
11000505
2200015010
3300030020
4500050025
5800080030

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:
110005055.000.50
22000150107.500.50
330003002010.000.67
450005002510.000.50
580008003010.000.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_idcitylist_dateproperty_typeprice
1012New York01/15/2022Residential2300000
1045Los Angeles03/02/2022Commercial4500000
1078New York05/20/2022Residential3300000
1099Los Angeles07/14/2022Commercial6000000
1056New York09/06/2022Commercial4200000
##### Example Output:
cityproperty_typeavg_price
New YorkResidential2800000
Los AngelesCommercial5250000
New YorkCommercial4200000

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:

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