Use the attached excel to run queries to produce the following analysis.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Use the attached excel to run queries to produce the following analysis. Please use any language possible and return your results in a document, or a slide deck. Please look to invest no more than 3 hours on these tasks

From the attached excel Sheet (Performance Data.xlsx) compute the following:

1. Renewal rent growth

2. Understanding lease terms

3. Any ‘dodgy’ behavior by users based on what the machine /software recommended and what they decided to offer to their residents

4. Renewal probability and rent growth relationships

5. Segment and rank order properties based on which renters are likely to renew most with 3-4 columns of data about these properties. Choose any columns you think are relevant to tell the story about why these properties may have people more likely to renew than others

 

In addition to the above, please perform the following task (code) as Python or SQL queries or VLOOKUPs and return your logic as pasted code

The following are 2 example database tables, with some questions below .

Table 1

 

Tenant_Code Property_Code Rent Offer_Cycle

abc_123 445 1050 01-01-2022

abc_123 445 1030 02-01-2022

edf_234 445 1000 02-01-2022

asd_999 555 750 02-01-2022

 

Table 2

 

t_code Renewal_Probability Lease_End_Date OfferCyc

abc_123 60 02-01-2022 01-01-2022

abc_123 65 12-01-2022 02-01-2022

edf_234 57 06-01-2022 02-01-2022

 

Note:

- Please refer to the tables as  ‘table_1’ and ‘table_2’ in your queries.

- All dates are formatted as MM-DD-YYYY Tasks to be completed

1. Get all distinct tenant codes from ‘table_1’

2. Get the average rent for property 445.

3. Get the average renewal probability for property 445.

4. Return a table with columns [‘Tenant_Code’, ‘Property_Code’, ‘Rent’, ‘Renewal_Probability’ and ‘Offer_Cycle’] using an INNER JOIN.

5. Assuming the current date is 06-01-2022, return the total number of leases expiring on or before the current date for the offer cycle at 02-01-2022.

 

Business Background

● This is a data export for a group of properties’ leasing data. The properties in this instance have a name, property ID, and are apartments (flats) which have renters (residents/ tenants) living inside them with a Tenant_Code

● These renters have leases signed with the company that owns them. The dataset provided has performance data of a price optimization software platform.

● The software platform analyses renewal probability of each resident at the Offered rent, and monitors Achieved Rent. The users of the software can Approve, Override the platform.

● There is an offer cycle, that is, renewal offers are sent in a batch of renewals and not everyday.

● Some residents are on MTM (month to month) leases and others are on leases between 3-18 month duration which the landlord and Beekin’s software offers and optimizes for.

● The software platform also provides a Renewal Probability for a resident based on multiple factors, and generates renewal price (offers) based on terms (duration of leases) which can range from 3 months to 18 months and the resident has a choice to pick one

● In-place Rent is current rent that a resident is paying (these are existing customers who are about to renew their leases). We also have data on contemporary market rent, which is the rent of other properties around where the resident lives.

 

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of