Advanced Aggregates
Lesson 1 of 1
  1. 1
    Introduction
    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…
    Start
  2. 2
    Daily Counts
    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…
    Start
  3. 3
    Daily Count 2
    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…
    Start
  4. 4
    Daily Revenue
    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?
    Start
  5. 5
    Daily Sum Conclusion
    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.
    Start
  6. 6
    Meal Sums
    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…
    Start
  7. 7
    Product Sum 2
    Great! We have the sum of 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 creat…
    Start
  8. 8
    Product Sum Conclusion
    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 …
    Start
  9. 9
    Grouping with Case Statements
    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 …
    Start
  10. 10
    Product Grouping Conclusion
    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…
    Start
  11. 11
    Reorder Rates
    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…
    Start
  12. 12
    Conclusion
    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…
    Start