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.

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;