ENDS 12/4: Get 50% off your Pro annual membership using code [CYBER23](https://www.codecademy.com/checkout?periods=year&plan_id=proGoldAnnualV2&discountCode=CYBER23)


Use data aggregation to explore and understand the data behind SpeedySpoon, a food delivery app.

Advanced Aggregates

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 of recommending smoothies be taken off the menu, we should talk to the smoothie customers and see what they like so much about these smoothies. There could be an opportunity here to expand the product line, or get other customers as excited as these kale fanatics. Nice work! 

Let's generalize what we've learned so far:

* *Data aggregation* is the grouping of data in summary form.
* *Daily Count* is the count of orders in a day. 
* *Daily Revenue Count* is the revenue on orders per day.
* *Product Sum* is the total revenue of a product. 
* *Subqueries* can be used to perform complicated calculations and create filtered or aggregate tables on the fly.
* *Reorder Rate* is the ratio of the total number of orders to the number of people making orders.



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 figure out why.

Product Grouping Conclusion

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 the piles of data in your database.

In our case, the reason could be that no one likes kale, but it could be something else entirely. To find out, we'll create a metric called *reorder rate* and see how that compares to the other products at SpeedySpoon.

We'll define *reorder rate* as the ratio of the total number of orders to the number of people making those orders. A higher ratio means most of the orders are reorders. A lower ratio means more of the orders are first purchases.

Reorder Rates

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, the first of which is determining what percent of revenue these smoothies represent.


Meal Sums

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:

```sql
select count(1)
from users;
```

This will treat all rows as a single group, and return one row in the result set - the total count.

To count orders by their dates, we'll use the `date` and `count` functions and pair them with the `group by` clause. Together this will count the records in the table, grouped by date.

For example, to see the user records counted by the date created, we use the `date` and `count` functions and `group by` clause as follows:

```sql
select date(created_at), count(1)
from users
group by date(created_at)
```

Daily Count 2

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 keep digging to find out what's going on with these smoothies.


Product Sum Conclusion

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 create filtered or aggregate tables on the fly.

Subqueries are useful when you want to perform an aggregation outside the context of the current query. This will let us calculate the overall total and per-item total at the same time. 

Product Sum 2

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 among data analysis tasks is *data aggregation*, the grouping of data to express in summary form. We'll be working with **SpeedySpoon**, a meal delivery app. The folks at SpeedySpoon have asked us to look into their deliveries and help them optimize their process.

This course was developed in partnership with our good friends at <a href="https://www.periscopedata.com?source=cc" target="_blank">Periscope Data</a>. If you're new to SQL, we recommend you do <a href="https://www.codecademy.com/learn/learn-sql" target="_blank">this course</a> first.

Introduction

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. 

Daily Sum Conclusion

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 include categories already, we'll need to make some!

Previously we've been using `group by` with a column (like `order_items.name`) or a function (like `date(orders.ordered_at)`).

We can also use `group by` with expressions. In this case a `case` statement is just what we need to build our own categories. `case` statements are similar to `if/else`  in other languages. 

Here's the basic structure of a `case` statement:

```sql
case {condition}
  when {value1} then {result1}
  when {value2} then {result2}
  else {result3}
end
```


Grouping with Case Statements

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?

Daily Revenue

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 on the `date` function and the `ordered_at` field.

To get the Daily Metrics we need the *date*. Most dates in databases are *timestamps*, which have hours and minutes, as well as the year, month, and day. Timestamps look like `2015-01-05 14:43:31`, while dates are the first part: `2015-01-05`.

We can easily select the date an item was ordered at with the `date` function and the `ordered_at` field:

```sql
select date(ordered_at)
from orders;
```