Hello My Name Is Michele and All Be Your Instructor
As your instructor, I have noticed that you've finished some sequel courses on DataCamp already, so you have a firm grasp on SQL but probably haven't used it in a business context yet. In this course, we will be using SQL to analyze a company's data to uncover facts about it and to prove its operations.
The Course Chapters
This chapter will cover calculating revenue, cost, and profit. In the next chapter, you'll learn about user subject metrics, namely registrations, active users, growth, and retention. Finally, in Chapter three, you'll learn about unit economics and distributions, and in Chapter four, you'll generate an executive report.
Diving into Liver: A Fictional Food Delivery Startup
Let's dive into a fictional food delivery startup called "Liver". Liver is similar to Uber Eats or Grubhub, where users can order meals from several eateries in one order. You've been hired as a data analyst at Liver, and your first task is to determine the company's profitability.
A Company's Performance
A company's performance is often judged by its profits. Profit is the money a company makes minus the money it spends. The former is revenue, while the latter is cost. According to the formula for profit, which is profit equals revenue minus costs, we need to calculate both revenue and costs.
Revenue at Liver
Liver's revenue is the money its users pay for the meals they order. To calculate this, we need to explore the tables needed in the sequel database. The first table is "meals", where each row represents a meal that users can order through the app. Each meal has a unique meal ID and eatery, as well as a price that the user pays for it and the cost that deliver pays to the eatery.
The second table is "orders", which represents a meal within an order with an order date, ordering user ID, ordered meals ID, and how many of each were ordered. If two rolls shared an order ID, they're part of the same order. Let's take an example: a user orders three burgers and two sandwiches with each burger price being $5 and each sandwich price being $3.
Calculating Revenue
To calculate an order's revenue, we multiply each meal's price by its ordered quantity. For this example, the user pays three times five dollars plus two times three dollars equals 21 dollars. This is how deliver calculates revenue from this order.
Joining Tables to Calculate Revenue per Order
We can also calculate revenue per order by joining the "meals" and "orders" tables on the meal ID. We use the `JOIN` clause in SQL to combine rows from two or more tables based on a related column between them. Here, we join the `meals` table with the `orders` table on the meal ID.
In the example above, the revenue per order is calculated by multiplying each meal's price by its ordered quantity and then grouping the results by order ID. This gives us the total revenue for each order.
Calculating Revenue per Other Metrics
We can also calculate revenue per other metrics such as user ID and month. The former is useful in determining which users bring in the most revenue, while the latter tracks revenue over time. To do this, we use a function like `DAY_TRUNC` to truncate the order date to that date part.
DAY_TRUNC outputs a timestamp and not a date, so the output needs to be cast to a date to drop the hours minutes etc. Let's practice calculating revenue on such examples.
"WEBVTTKind: captionsLanguage: enhello my name is Michele and all be your instructor you've finished some sequel courses on data camp already so you have a firm grasp on sequel but you probably haven't used it in a business context yet in this course you write sequel queries to analyze a company's data to uncover facts about it and to prove its operations let's go over the courses chapters in Chapter one this chapter you'll calculate revenue cost and profit in Chapter two you're not about user subject metrics namely registrations active users growth and retention in Chapter three you'll learn about unit economics and distributions finally in Chapter four you will generate an executive report let's dive in a liver is a fictional food delivery startup similar two breeds deliver stocks meals from eateries and bulk then offers users these meals through its app it owns an electric scooter fleet it uses to deliver meals to its users its competitive advantage is that users can order meals from several eateries in one order you've been hired there as a data analyst and your first task is to determine the livers profitability a company's performance is often judged by its profits profit is the money a company makes minus the money it spends the former is revenue while the latter is cost accordingly the formula for profit is profit equals revenue minus costs the livers revenue is the money its users pay for the meals they order some of its costs include the salaries paid to its employees for now though let's focus on revenue the liver stores its data in a sequel database let's explore the tables needed to calculate revenue the first table is meals each row represents a meal that users can order through the app each meal has a meal ID and eatery a price what the user pays for it and the cost what deliver pays the eatery for it the second table has orders each row represents a meal within an order with an order date the ordering users ID we ordered meals ID and how many of it were ordered if two rolls shared an order ID they're part of the same order let's take an example a user orders three burgers and two sandwiches with each burgers price being $5 and each sandwiches price being $3 the user pays three times five dollars plus two times three dollars equals 21 dollars which is delivers revenue from this order this is how you calculate an orders revenue multiply each meals price by its ordered quantity then some of the results thus query calculates revenue per order join meals to orders odd meal ID took at the meal price and quantity in the same crew group by order ID to get revenue per order you can also calculate revenue per other metrics such as user ID and month the former is useful in determining which users bring in the most revenue while the latter tracks revenue over time when calculating revenue per month or some other date part such as week quarter or year use day-drunk to truncate the order date to that date part given a date part and the date this function returns the first day of the date nearest to the date part the trunk outputs a timestamp and not a date so day trunks output is cast to date to drop the hours minutes and so on let's practice calculating revenue on the fallhello my name is Michele and all be your instructor you've finished some sequel courses on data camp already so you have a firm grasp on sequel but you probably haven't used it in a business context yet in this course you write sequel queries to analyze a company's data to uncover facts about it and to prove its operations let's go over the courses chapters in Chapter one this chapter you'll calculate revenue cost and profit in Chapter two you're not about user subject metrics namely registrations active users growth and retention in Chapter three you'll learn about unit economics and distributions finally in Chapter four you will generate an executive report let's dive in a liver is a fictional food delivery startup similar two breeds deliver stocks meals from eateries and bulk then offers users these meals through its app it owns an electric scooter fleet it uses to deliver meals to its users its competitive advantage is that users can order meals from several eateries in one order you've been hired there as a data analyst and your first task is to determine the livers profitability a company's performance is often judged by its profits profit is the money a company makes minus the money it spends the former is revenue while the latter is cost accordingly the formula for profit is profit equals revenue minus costs the livers revenue is the money its users pay for the meals they order some of its costs include the salaries paid to its employees for now though let's focus on revenue the liver stores its data in a sequel database let's explore the tables needed to calculate revenue the first table is meals each row represents a meal that users can order through the app each meal has a meal ID and eatery a price what the user pays for it and the cost what deliver pays the eatery for it the second table has orders each row represents a meal within an order with an order date the ordering users ID we ordered meals ID and how many of it were ordered if two rolls shared an order ID they're part of the same order let's take an example a user orders three burgers and two sandwiches with each burgers price being $5 and each sandwiches price being $3 the user pays three times five dollars plus two times three dollars equals 21 dollars which is delivers revenue from this order this is how you calculate an orders revenue multiply each meals price by its ordered quantity then some of the results thus query calculates revenue per order join meals to orders odd meal ID took at the meal price and quantity in the same crew group by order ID to get revenue per order you can also calculate revenue per other metrics such as user ID and month the former is useful in determining which users bring in the most revenue while the latter tracks revenue over time when calculating revenue per month or some other date part such as week quarter or year use day-drunk to truncate the order date to that date part given a date part and the date this function returns the first day of the date nearest to the date part the trunk outputs a timestamp and not a date so day trunks output is cast to date to drop the hours minutes and so on let's practice calculating revenue on the fall\n"
