Lesson 13.1

SQL Practice: Daily Revenue Report

Turn completed orders and line items into a day-by-day revenue report.

Store operators often start with a simple question: how much shipped revenue did each day produce? The answer is not in one table. Order dates and status live in orders, while the money comes from order_items.

This is a compact reporting query: join the tables, filter to completed work, group by date, and calculate revenue from line-item quantity and price.

Count orders separately from line items

Joining orders to line items repeats an order once per product on the receipt. That is correct for revenue, but not for order counts. Use count(distinct o.order_id) so a multi-item order still counts once.

Pattern

select date_column,
       count(distinct id_column) as orders,
       round(sum(quantity * price), 2) as revenue
from orders_table
join line_items_table using (order_id)
where status = 'shipped'
group by date_column
order by date_column

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text

Your task

For shipped orders only, return each order_date, the number of distinct orders, and total revenuerounded to two decimals. Sort by order_date.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer