Introduction and revenue

Analyzing Business Data in SQL

Michel Semaan

Data Scientist

Course overview

Chapter 1: Revenue, cost, and profit
Chapter 2: User-centric metrics
Chapter 3: Unit economics and distributions
Chapter 4: Generating an executive report

Delivr logo

Food delivery startup, similar to to Uber Eats
Stocks meals from eateries in bulk
Offers users these meals through its app
Users can order meals from several eateries in one order

Revenue, cost, and profit

Profit: The money a company makes minus the money it spends
Revenue: The money a company makes
Cost: The money a company spends
Profit = Revenue - Cost

Tables you'll need

meals

meal_id  eatery                    meal_price  meal_cost
-------  ------------------------  ----------  ---------
0        'Leaning Tower of Pizza'  4           2        
1        'Leaning Tower of Pizza'  3.5         1.25     
2        'Leaning Tower of Pizza'  4.5         1.75
...      ...                       ...         ...

orders

order_date  user_id  order_id  meal_id  order_quantity
----------  -------  --------  -------  --------------
2018-06-01  0        0         4        3            
2018-06-01  0        0         14       2            
2018-06-01  0        0         15       1
...         ...      ...       ...       ...

Calculating revenue

Example order
- Three (3) burgers at $5 each
- Two (2) sandwiches at $3 each
- Total price: $ 3 \times \$5 + 2 \times \$3 = \$21 $
Revenue: Multiply each meal's price times its ordered quantity, then sum the results

Query

SELECT
  order_id,
  SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY order_id;

Working with dates

DATE_TRUNC(date_part, date)
Examples
- DATE_TRUNC('week', '2018-06-12') :: DATE → '2018-06-11'
- DATE_TRUNC('month', '2018-06-12') :: DATE → '2018-06-01'
- DATE_TRUNC('quarter', '2018-06-12') :: DATE → '2018-04-01'
- DATE_TRUNC('year', '2018-06-12') :: DATE → '2018-01-01'

Revenue

Analyzing Business Data in SQL