Advanced Aggregates
Lesson 1 of 1
  1. 1
    At the heart of every great business decision is data. Since most businesses store critical data in SQL databases, a deep understanding of SQL is a necessary skill for every data analyst. Chief a…
  2. 2
    Nice work! Now that we have a good handle on our data, let’s dive into some common business queries. We’ll begin with the Daily Count of orders placed. To make our Daily Count metric, we’ll focus o…
  3. 3
    Great! Now that we can convert timestamps to dates, we can count the Orders Per Date. Generally, when we count all records in a table we run a query with the count function, as follows: select cou…
  4. 4
    We have the Daily Count of orders, but what we really want to know is revenue. How much money has SpeedySpoon made from orders each day?
  5. 5
    Those numbers are pretty low! A typical day has thousands in revenue but a small portion of that is coming from kale smoothies. Let’s dig deeper to find out what’s going on.
  6. 6
    It looks like the smoothies might not be performing well, but to be sure we need to see how they’re doing in the context of the other order items. We’ll look at the data several different ways, th…
  7. 7
    Great! We have the sum of the the products by revenue, but we still need the percent. For that, we’ll need to get the total using a subquery. A subquery can perform complicated calculations and c…
  8. 8
    As we suspected, kale smoothies are not bringing in the money. And thanks to this analysis, we found what might be a trend - several of the other low performing products are also smoothies. Let’s …
  9. 9
    To see if our smoothie suspicion has merit, let’s look at purchases by category. We can group the order items by what type of food they are, and go from there. Since our order_items table does not …
  10. 10
    Ah ha! It’s true that the whole smoothie category is performing poorly compared to the others. We’ll certainly take this discovery to SpeedySpoon. Before we do, let’s go one level deeper and figur…
  11. 11
    While we do know that kale smoothies (and drinks overall) are not driving a lot of revenue, we don’t know why. A big part of data analysis is implementing your own metrics to get information out of…
  12. 12
    Wow! That’s unexpected. While smoothies aren’t making a lot of money for SpeedySpoon, they have a very high reorder rate. That means these smoothie customers are strong repeat customers. Instead o…