def query_6(count=5):
query = db.select([Offices.columns.name.label('Office Name'), Offices.columns.zipCode.label('Zip Code'),
db.func.avg(Transactions.columns.salesPrice).label('Average Sales Price')])\
.group_by(Houses.columns.zipCode).order_by(db.desc(db.func.sum(Transactions.columns.salesPrice)))
query = query.select_from((Transactions.join(
Houses, Transactions.columns.houseId == Houses.columns.houseId)).join(
Offices, Offices.columns.zipCode == Houses.columns.zipCode))
results = connection.execute(query).fetchall()[:count]
if results != []:
df = pd.DataFrame(results)
df.columns = results[0].keys()
print(
"/---------------------------------------------------------------------------------------/"
)
print(
"The top {0} best selling areas' Zip Codes are as follows:".format(
count))
print(
"/---------------------------------------------------------------------------------------/"
)
print(df)
else:
print("No Houses sold in the month {0}".format(month))
def query_1(number=5,
year=datetime.datetime.utcnow().year,
month=datetime.datetime.utcnow().month):
begin = datetime.datetime(year, month, 1)
end = datetime.datetime(year, month,
calendar.monthrange(year, month)[1], 23, 59, 59)
#print(begin, end)
query = db.select([Offices.columns.officeId.label('Office ID'), Offices.columns.name.label('Office Name'), Offices.columns.zipCode.label('ZipCode'), Offices.columns.email.label('Contact Info: Email'),Offices.columns.phone.label('Phone'),
db.func.sum(Transactions.columns.salesPrice).label('Total Sales'),
db.func.count(Transactions.columns.salesPrice).label('Sales Count')])\
.group_by(Houses.columns.zipCode)\
.where(db.and_(Transactions.columns.soldDate >= begin, end >= Transactions.columns.soldDate))\
.order_by(db.desc(db.func.sum(Transactions.columns.salesPrice)))
query = query.select_from((Transactions.join(
Houses, Transactions.columns.houseId == Houses.columns.houseId)).join(
Offices, Offices.columns.zipCode == Houses.columns.zipCode))
results = connection.execute(query).fetchall()[:number]
print(
"/---------------------------------------------------------------------------------------/"
)
print(
"The top {2} Offices that achoeved the highest sales in month: {0}, {1}"
.format(month, year, number))
print(
"/---------------------------------------------------------------------------------------/"
)
if results != []:
df = pd.DataFrame(results)
df.columns = results[0].keys()
print(df)
else:
print("No sales in month: {}".format(month))
def query_4(year=datetime.datetime.utcnow().year,
month=datetime.datetime.utcnow().month):
start = datetime.datetime(year, month, 1)
end = datetime.datetime(year, month,
calendar.monthrange(year, month)[1], 23, 59, 59)
query = db.select([Transactions.columns.houseId.label('House ID'), Houses.columns.listDate.label('List Date'), Transactions.columns.soldDate.label('Sold Date'), Houses.columns.zipCode.label('Zip Code')])\
.where(db.and_(Transactions.columns.soldDate >= start, end >= Transactions.columns.soldDate))
query = query.select_from(
Transactions.join(
Houses, Transactions.columns.houseId == Houses.columns.houseId))
results = connection.execute(query).fetchall()
if results is not None:
df = pd.DataFrame(results)
df.columns = results[0].keys()
df["On Market Days"] = abs(df["Sold Date"] - df["List Date"])
print(
"/---------------------------------------------------------------------------------------/"
)
print(
"The average number of days a house was listed on the market in Month {0}, Year {1} was: {2} Days"
.format(month, year,
np.mean(df["On Market Days"]).days + 1))
print(
"/---------------------------------------------------------------------------------------/"
)
print(pd.DataFrame(df[["House ID", "Zip Code", "On Market Days"]]))
print()
else:
print("No Hosues sold in Month: {0}, Year {1}".format(month, year))
def query_5(year=datetime.datetime.utcnow().year,
month=datetime.datetime.utcnow().month):
start = datetime.datetime(year, month, 1)
end = datetime.datetime(year, month,
calendar.monthrange(year, month)[1], 23, 59, 59)
query = db.select([Transactions.columns.agentId, Houses.columns.zipCode, Transactions.columns.salesPrice])\
.where(db.and_(Transactions.columns.soldDate >= start, end >= Transactions.columns.soldDate))
query = query.select_from(
Transactions.join(
Houses, Transactions.columns.houseId == Houses.columns.houseId))
results = connection.execute(query).fetchall()
if results is not None:
df = pd.DataFrame(results)
df.columns = results[0].keys()
print(
"/---------------------------------------------------------------------------------------/"
)
print("The Average Sales Price in Month {0} is: ${1}".format(
month, int(np.mean(df["salesPrice"]))))
print(
"/---------------------------------------------------------------------------------------/"
)
df.rename(columns={
'salesPrice': 'Sales Price',
'agentId': 'Agent ID',
'zipCode': 'Zip Code'
},
inplace=True)
print("\n", df)
else:
print("No Sales in month: {}.format(month)")
def top_agents(number = 5):
query = db.select([Transaction.columns.SellingAgentID, Agent.columns.Phone, Agent.columns.Email,
db.func.sum(Transaction.columns.SellPrice).label('Total Sales'),
db.func.count(Transaction.columns.SellPrice).label('Number of Sales')])\
.group_by(Transaction.columns.SellingAgentID).order_by(db.desc(db.func.sum(Transaction.columns.SellPrice)))
query = query.select_from(Agent.join(Transaction,
Agent.columns.AgentID == Transaction.columns.SellingAgentID))
results = connection.execute(query).fetchall()[:number]
df = pd.DataFrame(results)
df.columns = results[0].keys()
print("Top {0} agents".format(number))
print(df)
def commission_table():
query = db.select([Transaction.columns.SellingAgentID, Agent.columns.Phone, Agent.columns.Email, Transaction.columns.SellPrice])
query = query.select_from(Agent.join(Transaction,
Agent.columns.AgentID == Transaction.columns.SellingAgentID))
results = connection.execute(query).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df['SellPrice'] = df['SellPrice'].apply(commission)
commission_table = df.groupby(['SellingAgentID']).sum()
commission_table.columns = ["Total Commision"]
commission_table.sort_values(["Total Commision"])
print(commission_table)
def top_zip_code(number = 5):
query = db.select([Office.columns.Name, Office.columns.Zipcode, Office.columns.Phone,
db.func.avg(Transaction.columns.SellPrice).label('Average Sale Price')])\
.group_by(Listing.columns.Zipcode).order_by(db.desc(db.func.sum(Transaction.columns.SellPrice)))
query = query.select_from((Transaction.join(Listing, Transaction.columns.ListingID == Listing.columns.ListingID)).join(Office, Office.columns.Zipcode == Listing.columns.Zipcode))
results = connection.execute(query).fetchall()[:number]
if results != []:
df = pd.DataFrame(results)
df.columns = results[0].keys()
print("The top {0} zipcode with highest average sale prices:".format(number))
print(df)
else:
print("No Data Available")
def query_2(count=5):
query = db.select([Transactions.columns.agentId.label('Agent ID'),Agents.columns.firstName.label('Name'), Agents.columns.email.label('Contact Info: Email'), Agents.columns.phone.label('Phone'),
db.func.sum(Transactions.columns.salesPrice).label('Total Sales'),
db.func.count(Transactions.columns.salesPrice).label('Number of Sales')])\
.group_by(Transactions.columns.agentId).order_by(db.desc(db.func.sum(Transactions.columns.salesPrice)))
query = query.select_from(
Agents.join(Transactions,
Agents.columns.agentId == Transactions.columns.agentId))
results = connection.execute(query).fetchall()[:count]
df = pd.DataFrame(results)
df.columns = results[0].keys()
print(
"/---------------------------------------------------------------------------------------/"
)
print("The top {0} agents with the highest sales number:".format(count))
print(
"/---------------------------------------------------------------------------------------/"
)
print(df)
def average_price(year = datetime.datetime.utcnow().year, month = datetime.datetime.utcnow().month):
begin = datetime.datetime(year, month, 1)
end = datetime.datetime(year, month, calendar.monthrange(year, month)[1], 23, 59, 59)
query = db.select([Transaction.columns.ListingID, Transaction.columns.SellPrice])\
.where(db.and_(Transaction.columns.DateSold >= begin, end >= Transaction.columns.DateSold))
query = query.select_from(Transaction.join(Listing,
Transaction.columns.ListingID == Listing.columns.ListingID))
results = connection.execute(query).fetchall()
print("The houses are sold in month: {0}, year: {1}".format(month, year))
if results != []:
df = pd.DataFrame(results)
df.columns = results[0].keys()
print(df)
print()
print("The average price: {0} ($)".format(int(np.mean(df["SellPrice"]))))
else:
print("No Data available")
def average_day_on_market(year = datetime.datetime.utcnow().year, month = datetime.datetime.utcnow().month):
begin = datetime.datetime(year, month, 1)
end = datetime.datetime(year, month, calendar.monthrange(year, month)[1], 23, 59, 59)
query = db.select([Transaction.columns.ListingID, Listing.columns.DateListed, Transaction.columns.DateSold])\
.where(db.and_(Transaction.columns.DateSold >= begin, end >= Transaction.columns.DateSold))
query = query.select_from(Transaction.join(Listing,
Transaction.columns.ListingID == Listing.columns.ListingID))
results = connection.execute(query).fetchall()
print("Days on market of houses sold in month: {0}, year: {1}".format(month, year))
if results != []:
df = pd.DataFrame(results)
df.columns = results[0].keys()
df["On Market Day"] = abs(df["DateSold"] - df["DateListed"])
print(pd.DataFrame(df[["ListingID", "On Market Day"]]))
print()
print("The average days on market of houses sold in of houses sold in month {0}, year {1}: {2} (Days) "\
.format(month, year, np.mean(df["On Market Day"]).days + 1))
else:
print("No Data")
def query_3():
query = db.select([
Transactions.columns.agentId.label('Agent ID'),
Agents.columns.email.label('Email'), Transactions.columns.salesPrice
])
query = query.select_from(
Agents.join(Transactions,
Agents.columns.agentId == Transactions.columns.agentId))
results = connection.execute(query).fetchall()
#print(results)
df = pd.DataFrame(results)
df.columns = results[0].keys()
df['salesPrice'] = df['salesPrice'].apply(agent_commission)
dd = df.groupby(['Email']).sum()
com_table = dd.sort_values(["salesPrice"], ascending=False)
com_table.rename(columns={'salesPrice': 'Commision'}, inplace=True)
print(
"/---------------------------------------------------------------------------------------/"
)
print(com_table)
def query_office_month(number = 5, year = datetime.datetime.utcnow().year, month = datetime.datetime.utcnow().month):
begin = datetime.datetime(year, month, 1)
end = datetime.datetime(year, month, calendar.monthrange(year, month)[1], 23, 59, 59)
query = db.select([Office.columns.Name, Office.columns.Zipcode, Office.columns.Phone,
db.func.sum(Transaction.columns.SellPrice).label('Total Sales'),
db.func.count(Transaction.columns.SellPrice).label('Number of Sales')])\
.group_by(Listing.columns.Zipcode)\
.where(db.and_(Transaction.columns.DateSold >= begin, end >= Transaction.columns.DateSold))\
.order_by(db.desc(db.func.sum(Transaction.columns.SellPrice)))
query = query.select_from((Transaction.join(Listing, Transaction.columns.ListingID == Listing.columns.ListingID)).join(Office, Office.columns.Zipcode == Listing.columns.Zipcode))
results = connection.execute(query).fetchall()[:number]
print("Top {2} Offices with the most sales in month: {0}, year: {1}".format(month, year,number))
if results != []:
df = pd.DataFrame(results)
df.columns = results[0].keys()
print(df)
else:
print("No Sales")