Calculating Profit: A Key Performance Indicator for Companies
It's time to combine the revenue and cost queries to calculate profit using CTS (Common Table Expressions). Let's start by recapitulating the concept of revenue. Revenue is the money a company makes, which in the liver's case is the money that its customers pay to Audre. To calculate revenue, we multiply each meal price by its ordered quantity.
To break this down further, let's consider an example. Neal 21 has a price of $8 and has been ordered 100 times, resulting in a revenue of $800. On the other hand, Meal 22 has a price of $5 and has been ordered 80 times, with a total purchase cost of $500. In this case, profit is calculated as the difference between revenue and costs.
Now that we've understood how profit works, let's explore why profit is such an important metric for companies. Alongside revenue and cost, profit is a key performance indicator (KPI) that helps companies measure their performance. A KPI is a metric with some value that the company uses to evaluate its performance. By using profit as a KPI, companies can determine whether they are making or losing money after accounting for all expenses.
Profit Per User: Identifying Best Users
One way to use profit as a KPI is by calculating profit per user. This helps identify the best users who bring in the most revenue. By analyzing this data, companies can understand which users are driving the most sales and make informed decisions about their business strategy.
Profit Per Meal: Identifying Most Profitable Meals
Another way to use profit is by calculating profit per meal. This helps identify the most profitable meals for a company. By examining this data, companies can determine which meals are generating the most revenue and make adjustments accordingly.
Profit Per Month: Tracking Profit Over Time
Finally, profit per month is used to track profit over time. This metric allows companies to see whether they're improving their profitability or if there are areas where they need to focus on increasing sales.
Why Isn't Revenue Used Instead?
So, why isn't revenue used instead of profit? The reason lies in the fact that profit takes into account both revenue and costs. Revenue is just one side of the equation, whereas profit provides a more comprehensive picture of a company's financial health.
Calculating Profit Using CTS
Now that we've understood the importance of profit, let's explore how to calculate it using CTS. To start, we need to combine the revenue and cost queries by storing each of them in separate CTEs (Common Table Expressions).
To do this, we write a query that calculates revenue per meal and stores it in a table called "revenue." We then group the results by meal ID and calculate the total revenue for each meal. Next, we write another query that calculates cost per meal and stores it in a table called "cost."
We sum up the costs for each meal to get the total cost. Finally, we join the two tables on the meal ID column to combine the revenue and cost data.
Once we have both the revenue and cost data combined, we can calculate profit by subtracting the cost from the revenue. To ensure that our results are accurate, we make sure to group by both the metric (e.g., meal ID) and the city (e.g., "Eatery").
To finalize our calculation, we select the meal ID column from one of the tables and specify which table to use to avoid any ambiguous column reference errors. Finally, we order our results by profit in descending order and return only the top three meals with the highest profits.
This process allows us to calculate profit using CTS and gain a deeper understanding of our company's financial performance.
"WEBVTTKind: captionsLanguage: enit's time to use CTS to combine the revenue and cost queries to calculate profit before doing that though let's recap revenue is the money a company makes which in the livers case is the money that its customers pay to audre the means calculate revenue by multiplying each means price by its ordered quantity then some of the results cost is the money a company spends which in the livers case is the money it pays to eateries to stock their meals calculate cost by multiplying each meals cost by its ordered quantity then sum the results finally profit is the difference between revenue and costs let's see why profit is such an important metric for company's profit alongside revenue and cost is a key performance indicator or KPI a KPI is a metric with some value that the company uses to measure its performance you learn about other KPIs throughout the course profit tells you whether a company is making or losing money after everything is accounted for profit per user helps you identify their best users those who bring delivery the most money profit per meal helps you identify the most profitable meals finally profit per month tracks profit over time signaling whether or not deliver is getting better at making money why isn't revenue used instead let's explore why through an example Neal 21 has a price of 8 and has been ordered 100 times so its revenue is 8 times 100 equals 800 its total purchase cost is 500 profit is the difference between revenue and cost so its profit is 800 minus 500 equals 300 mil 22 has a price of 5 and has been ordered 80 times so its revenue is 5 times 80 equals 400 its purchase cost is 80 so its profit is 400 minus 80 equals 320 even though meal 21 has a higher price and order quantity and thus a higher revenue meal 22 generated more profit because the difference between its revenue and cost was higher than meal 21s difference to calculate profit combine the revenue and cost queries by storing each of them in a CTE let's calculate profit per meal to start write a query that calculates revenue per mil and store it in a city called revenue some Inc each Mills price times its ordered quantity and grouping by mill ID then write a query that calculates cost per million and store that in a city called cost summing each Mills cost times its stock quantity and grouping by m'lady if you're calculating profit or some other metric like eatery or a month make sure to group by that metric and both the city E's since you'll join the two city is on that matrix column later on now that you've combined the revenue and cost queries join revenue to cost on meal ID then select the meal ID column from one of the city ease making sure to specify which city to avoid an ambiguous column reference error since both cities have that column finally subtract cost from revenue to calculate profit this particular query orders by profit in descending order and returns only the top three meals by profit you now have the tools to answer core business questions about profit the following exercises your calculate profit byit's time to use CTS to combine the revenue and cost queries to calculate profit before doing that though let's recap revenue is the money a company makes which in the livers case is the money that its customers pay to audre the means calculate revenue by multiplying each means price by its ordered quantity then some of the results cost is the money a company spends which in the livers case is the money it pays to eateries to stock their meals calculate cost by multiplying each meals cost by its ordered quantity then sum the results finally profit is the difference between revenue and costs let's see why profit is such an important metric for company's profit alongside revenue and cost is a key performance indicator or KPI a KPI is a metric with some value that the company uses to measure its performance you learn about other KPIs throughout the course profit tells you whether a company is making or losing money after everything is accounted for profit per user helps you identify their best users those who bring delivery the most money profit per meal helps you identify the most profitable meals finally profit per month tracks profit over time signaling whether or not deliver is getting better at making money why isn't revenue used instead let's explore why through an example Neal 21 has a price of 8 and has been ordered 100 times so its revenue is 8 times 100 equals 800 its total purchase cost is 500 profit is the difference between revenue and cost so its profit is 800 minus 500 equals 300 mil 22 has a price of 5 and has been ordered 80 times so its revenue is 5 times 80 equals 400 its purchase cost is 80 so its profit is 400 minus 80 equals 320 even though meal 21 has a higher price and order quantity and thus a higher revenue meal 22 generated more profit because the difference between its revenue and cost was higher than meal 21s difference to calculate profit combine the revenue and cost queries by storing each of them in a CTE let's calculate profit per meal to start write a query that calculates revenue per mil and store it in a city called revenue some Inc each Mills price times its ordered quantity and grouping by mill ID then write a query that calculates cost per million and store that in a city called cost summing each Mills cost times its stock quantity and grouping by m'lady if you're calculating profit or some other metric like eatery or a month make sure to group by that metric and both the city E's since you'll join the two city is on that matrix column later on now that you've combined the revenue and cost queries join revenue to cost on meal ID then select the meal ID column from one of the city ease making sure to specify which city to avoid an ambiguous column reference error since both cities have that column finally subtract cost from revenue to calculate profit this particular query orders by profit in descending order and returns only the top three meals by profit you now have the tools to answer core business questions about profit the following exercises your calculate profit by\n"
