SQL Tutorial : Analyzing Business Data in SQL

Want to learn more? Take the full course at https://learn.datacamp.com/courses/analyzing-business-data-in-sql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---

Hello! My name's Michel, and I'll be your instructor.

You've finished some SQL courses on DataCamp already, so you have a firm grasp on SQL, but you probably haven't used it in a business context yet.

In this course, you'll write SQL queries to analyze a company's data to uncover facts about it and improve its operations.

Let's go over the course's chapters.

In chapter one, this chapter, you'll calculate revenue, cost, and profit.

In chapter two, you'll learn about user-centric metrics, namely registrations, active users, growth, and retention.

In chapter three, you'll learn about unit economics and distributions.

Finally, in chapter four, you'll generate an executive report.
Let's dive in.

Delivr is a fictional food delivery startup similar to Uber Eats. Delivr stocks meals from eateries in 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 Delivr's 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 = Revenue - Cost.

Delivr's 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, let's focus on revenue.

Delivr stores its data in an SQL 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, an eatery, a price (what the user pays for it), and a cost (what Delivr pays the eatery for it).

The second table is orders. Each row represents a meal within an order, with the order date, the ordering user's ID, the ordered meal's ID, and how many of it were ordered. If two rows share an order ID, they're part of the same order.

Let’s take an example. A users orders three burgers and two sandwiches, with each burger's price being $5, and each sandwich's price being $3. The user pays 3x$5+2x$3=$21, which is Delivr's revenue from this order.

This is how you calculate an order’s revenue — multiply each meal’s price by its ordered quantity, then sum the results.

This query calculates revenue per order. Join meals to orders on meal ID to get the meal price and quantity in the same query. 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 DATE_TRUNC to truncate the order date to that date part. Given a date part and a date, this function returns the first day of the date nearest to the date part.
DATE_TRUNC outputs a timestamp, not a date, so DATETRUNC's output is cast to DATE to drop the hours, minutes, and so on.

Let's practice calculating revenue in the following exercises.


#DataCamp #SQLTutorial #AnalyzingBusinessDatainSQL