Skip to Content
Learn
Advanced Aggregates
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?

Instructions

1.

We can make a few changes to our Daily Count query to get the revenue.

First, instead of using count(1) to count the rows per date, we’ll use round(sum(amount_paid), 2) to add up the revenue per date. Complete the query by adding revenue per date.

Second, we need to join in the order_items table because that table has an amount_paid column representing revenue. Complete the query by adding a join clause where orders.id = order_items.order_id.

select date(ordered_at), /**/ from orders /**/ order_items on orders.id = order_items.order_id group by 1 order by 1;

Note that the round function rounds decimals to digits, based on the number passed in. Here round(..., 2) rounds the sum paid to two digits.

2.

Nice. Now with a small change, we can find out how much we’re making per day for any single dish. What’s the daily revenue from customers ordering kale smoothies?

Complete the query by using a where clause to filter the daily sums down to orders where the name = 'kale-smoothie'.

select date(ordered_at), round(sum(amount_paid), 2) from orders join order_items on orders.id = order_items.order_id where /**/ group by 1 order by 1;
Folder Icon

Sign up to start coding

Already have an account?