Kickstart 2020 with new opportunities! - 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.CustomerId,
  CompanyName,
  SUM(Amount) AS TotalRevenue 
FROM Orders O
JOIN Customers C
  ON O.CustomerId = C.CustomerId
WHERE O.OrderDate >= '20170301' AND O.OrderDate < '20170401'
GROUP BY C.CustomerId, CompanyName;

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 OrderDate. Note the date format that we used: YYYYMMDD. In other words, O.OrderDate >= '20170301' 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.OrderDate < '20170401', which means "orders placed before April 1, 2017".

Exercise

Find the TotalRevenue (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:

OrderDate >= '20170101' AND OrderDate < '20180101'