logo

10 Newmark Group SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

Newmark Group employees use SQL for analyzing real estate market trends and managing property transaction datasets. Because of this, Newmark Group almost always asks SQL questions in interviews for Data Analyst, Data Science, and BI jobs.

Thus, to help prep you for the Newmark Group SQL interview, here’s 10 Newmark Group SQL interview questions in this article.

Newmark Group SQL Interview Questions

10 Newmark Group SQL Interview Questions

SQL Question 1: Identify the Most Active Customers

Newmark Group wants to identify their most active customers to understand their purchasing behavior and preferences. An activity can be considered making a purchase. The company defines their "whale" users as customers who have made more than 100 purchases in a single month. Using this definition, write a SQL query to identify the customers who fit this criterion for the month of June.

Here's the input data representing the table:


Answer:

Here's the query:


Execution of above query on given "purchases" table will give us a list of user_ids along with their purchase counts for the month of June in 2022 who have made more than 100 purchases,i.e. are the "whale" users for the Newmark Group.

To practice a related customer analytics question on DataLemur's free online SQL coding environment, try this Microsoft SQL Interview problem: Microsoft SQL Interview Question: Super Cloud Customer

SQL Question 2: Employees Earning More Than Managers

Given a table of Newmark Group employee salary information, write a SQL query to find employees who earn more than their direct boss.

Newmark Group Example Input:

employee_idnamesalarydepartment_idmanager_id
1Emma Thompson38001
2Daniel Rodriguez2230110
3Olivia Smith800018
4Noah Johnson680028
5Sophia Martinez1750110
8William Davis70002NULL
10James Anderson40001NULL

Example Output:

employee_idemployee_name
3Olivia Smith

This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.

Solve this problem directly within the browser on DataLemur:

Employees Earning More Than Their Manager

Answer:

First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.


If the code above is tough, you can find a step-by-step solution here: Highly-Paid Employees.

Read about Newmarks Group's global supply challenges that they're facing in 2024!

SQL Question 3: What's the difference between an inner and a full outer join?

A full outer join returns all rows from both tables, including any unmatched rows, whereas an inner join only returns rows that match the join condition between the two tables.

For a tangible example, suppose you had a table of Newmark Group orders and Newmark Group customers.

Here's a SQL inner join using the orders and customers tables:


This query will return rows from the orders and customers tables that have matching values. Only rows with matching values will be included in the results.

Here is an example of a using the orders and customers tables:


This query will return all rows from both the orders and customers tables, including any rows that do not have matching values. Any rows with null values for either table will be included in the results.

Newmark Group SQL Interview Questions

SQL Question 4: Analyzing Sales Revenue with Window Functions

Newmark Group, a real estate advisory company, needs to analyze their sales data to inform their revenue strategies. They would like to calculate the total sales revenue per month from each property type that they deal with, and rank these property types according to the collected revenue every month. Additionally, they would like to find out that cumulative sales revenue of each property type up to the particular month.

The tables and which are provided contains following information:

Example Input:
sale_idproperty_idsale_datesale_price
101101/02/2022500000
102201/22/2022350000
103301/30/2022600000
104202/01/2022400000
105103/01/2022550000
Example Input:
property_idproperty_type
1Apartment
2Semi-detached House
3Detached House

Question is:

Write a SQL query to find out for each month, the total sales revenue for each property type along with their ranking based on the revenue, and total cumulative revenue for each property type until that month.

Answer:


In this query, a common table expression (CTE) is used to calculate the total monthly revenues for each property type. Then, these revenues are ranked per month. The second part of the query computes the cumulative revenue for each property type until that month using a window function. The result should display the total monthly revenue with corresponding ranks and cumulative revenue till date for each property type sorted by month and rank.

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 5: When would you use the constraint?

A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.

Say for example you had sales analytics data from Newmark Group's CRM (customer-relationship management) tool.


The FOREIGN KEY constraint ensures that the data in the field of the "opportunities" table is valid, and prevents the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and can be used to ensure that data is not deleted from the accounts table if there are still references to it in the opportunities` table.

SQL Question 6: Average Property Value by City

As a data analyst at Newmark Group, a full-service real estate company, you are asked to analyze our property data. One key metric to report is the average property value by city.

Please use the provided table and write a SQL query to find the average property value in each city.

Example Input:
property_idcityproperty_value
101New York1200000
102San Francisco1500000
103San Francisco1800000
104New York1300000
105San Francisco1400000
106Los Angeles1100000
107Los Angeles1000000
108New York1400000
Example Output:
cityavg_property_value
New York1300000
San Francisco1566666.67
Los Angeles1050000

Answer:


The above SQL query calculates the average property value for each city from the property table. The GROUP BY statement is used to separate the data into groups, in this case, by city. The AVG function is then used to calculate the average property value in each of these groups.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for needing to group by and aggregate on a field or this Robinhood Cities With Completed Trades Question which is similar for requiring a successful grouping and calculation by city.

SQL Question 7: What distinguishes a left join from a right join?

Both types of joins in SQL help you retrieve data from multiple tables and merge the results into a single table.

To demonstrate the difference between a left join versus a right join, imagine you had two database tables: an table that contains data on Google Ads keywords and their bid amounts, and a table with information on product sales and the Google Ads keywords that drove those sales.

A retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the Sales table). If there is no match in the right table, values will be returned for the right table's columns.

A RIGHT JOIN retrieves all rows from the right table (in this case, the Sales table) and any matching rows from the left table (the table). If there is no match in the left table, values will be returned for the left table's columns.

SQL Question 8: Calculate the Click-Through Rate per Advertisement for Newmark Group

Newmark Group has an online advertisement campaign which directs views to their web pages. Each ad click and view of a product is logged. The company would like to understand the click-through rate (CTR) for each of its advertisements. CTR is calculated as the number of clicks the ad gets divided by the number of times the ad is shown, times 100.

Assume the relevant tables are and .

Example Input:
ad_iddisplay_date
10101/15/2023
10201/15/2023
10301/16/2023
10101/16/2023
10201/17/2023
Example Input:
ad_click_idclick_datead_id
101/15/2023101
201/15/2023101
301/15/2023102
401/16/2023101
501/17/2023102

Answer:

Here's an SQL query that could solve this problem:


This query first makes a left join of the advertisement_logs and click_logs tables on the condition that the ad_id and the date match. By doing so, we obtain all ads and their corresponding clicks if any. We then calculate the CTR as the count of distinct ad clicks divided by the count of distinct ad displays times 100. The result is grouped by ad_id so we get a CTR for each advertisement.

Example Output:

ad_idctr
10150.00
10233.33
1030.00

This output shows the click-through rate for each advertisement. For example, the CTR for ad_id 101 is 50%, which means half the time it was displayed, it was clicked.

To solve a similar problem about calculating rates, try this SQL interview question from TikTok within DataLemur's online SQL coding environment: SQL interview question from TikTok

SQL Question 9: Calculate the Average Sale Price of Each Property Type

Newmark Group provides commercial real estate services. As a data analyst, your task is to write a query to find out the average sale price for each type of property (e.g., Office, Retail, Industrial, etc.) that the company has sold in the past year.

We will start with creating a mock database table, .

Example Input:

We're aiming for an output that shows the average sale price for each type of property.

Example Output:

Answer:

The information in the table can be used to solve this problem. The PostgreSQL query is as follows:


In the above query, we are grouping the sales based on the column and calculating the average of the for each group. It will give us the average sale price for each type of property that the company sold in the past year.

SQL Question 10: What is database normalization?

To normalize a database, tables are divided into smaller, more specialized ones and relationships between them are defined via primary and foreign keys. This minimizes redundancy, making the database more flexible, scalable, and easier to maintain. Normalization also helps to ensure the accuracy of the data by reducing the likelihood of inconsistencies and errors.

Newmark Group SQL Interview Tips

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Newmark Group SQL interview is to solve as many practice SQL interview questions as you can! Beyond just solving the earlier Newmark Group SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like FAANG and tech startups. DataLemur Questions

Each problem on DataLemur has multiple hints, full answers and most importantly, there's an online SQL coding environment so you can easily right in the browser your SQL query answer and have it graded.

To prep for the Newmark Group SQL interview you can also be a great idea to practice SQL problems from other real estate and REIT companies like:

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

SQL tutorial for Data Analytics

This tutorial covers SQL concepts such as finding NULLs and cleaning text data – both of these pop up frequently in Newmark Group SQL interviews.

Newmark Group Data Science Interview Tips

What Do Newmark Group Data Science Interviews Cover?

In addition to SQL interview questions, the other types of problems to prepare for the Newmark Group Data Science Interview include:

  • Probability & Stats Questions
  • Python or R Programming Questions
  • Analytics and Product-Metrics Questions
  • Machine Learning Questions
  • Resume-Based Behavioral Questions

Newmark Group Data Scientist

How To Prepare for Newmark Group Data Science Interviews?

To prepare for Newmark Group Data Science interviews read the book Ace the Data Science Interview because it's got:

  • 201 interview questions sourced from Facebook, Google & startups
  • a crash course covering Python, SQL & ML
  • over 1000+ 5-star reviews on Amazon

Ace the Data Science Interview by Nick Singh Kevin Huo