Revenue by Customer City
MediumUnlock detailed company stats for this questionUpgrade
Given the schema shown below, write a solution to fetch the total transaction revenue per user city, ordered by descending revenue (in USD).
users products +---------------+---------+ +-----------------+---------+ +--| id | int | +-----| id | int | | | first_name | varchar | | | name | varchar | | | last_name | varchar | | +->| product_line_id | date | | | user_city | int | | | | stock | int | | | email | int | | | +-----------------+---------+ | +---------------+---------+ | | | | | | transactions | | product_lines | +---------------+---------+ | | +--------+--------+ | | id | int |<----+ +--| id | int | +---->| customer_id | int | | name | varchar| | product_id | int | +--------+--------+ | amount | int | | currency_code | varchar | | date | date | +---------------+---------+ exchange_rate +----------------------+---------+ | id | int | | source_currency_code | varchar | | target_currency_code | varchar | | rate | numeric | +----------------------+---------+
Your answer should return a result with the following format:
user_city | total_revenue -----------+------------------------ varchar | float (no need to round)
To fetch the total transaction revenue per user city, ordered by descending revenue (in USD), we can do the following:
- Merge Transactions with Users: Combine
transactionswithusersto link each transaction to a user’s city. - Convert Transaction Amounts to USD: Use
exchange_rateto convert all transaction amounts to USD. Ensure that you handle transactions already in USD correctly. - Aggregate Revenue by City: Group by user city and sum up the revenues.
- Sort by Revenue: Order the results by descending revenue.
Pythonimport pandas as pd
def find_revenue_by_city(transactions: pd.DataFrame,
users: pd.DataFrame,
exchange_rate: pd.DataFrame) -> pd.DataFrame:
# Merge Transactions with Users
transactions_with_users = pd.merge(transactions, users, left_on='customer_id', right_on='id')
# Merge non-USD transactions with exchange rates
transactions_with_exchange = pd.merge(
transactions_with_users,
exchange_rate[exchange_rate['target_currency_code'] == 'USD'],
how='left',
left_on='currency_code',
right_on='source_currency_code'
)
# Assign an exchange rate of 1.0 for USD transactions
transactions_with_exchange['rate'] = transactions_with_exchange['rate'].fillna(1.0)
# Calculate the amount in USD
transactions_with_exchange['amount_usd'] = transactions_with_exchange['amount'] * transactions_with_exchange['rate']
# Aggregate Revenue by City
revenue_by_city = transactions_with_exchange.groupby('user_city')['amount_usd'].sum()
# Sort by Revenue
revenue_by_city = revenue_by_city.sort_values(ascending=False)
# Rename columns for clarity
revenue_by_city = revenue_by_city.reset_index()
revenue_by_city.columns = ['user_city', 'total_revenue']
return revenue_by_cityRelated courses
Course
Course
Course