Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Get to know the database
Basic revenue metrics
Summary

Instruction

Good job! Now, let's introduce some time constraints to our revenue reports. Take a look:

SELECT
  c.customer_id,
  company_name,
  SUM(amount) AS total_revenue 
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date >= '2017-03-01' AND o.order_date < '2017-04-01'
GROUP BY c.customer_id, company_name;

The query above shows total revenue per customer, but this time it's based on orders from March 2017. We've added a WHERE clause, inside which we defined the range for order_date. Note the date format that we used: YYYY-MM-DD. In other words, o.order_date >= '2017-03-01' means 'orders placed on March 1, 2017 or later'. Note that the month comes before the day in this date format – this might be counterintuitive for people from the US. The other condition that we used is o.order_date < '2017-04-01', which means "orders placed before April 1, 2017".

Exercise

Find the total_revenue (the sum of all amounts) from all orders placed in 2017.

Stuck? Here's a hint!

You need the following condition in the WHERE clause:

order_date >= '2017-01-01' AND order_date < '2018-01-01'