Lesson 14.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 isn't 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.

In Garden Shop every shipped date happens to have a single order, so the orders column comes out as all 1s. Plain count(*) would report 2 or 3 on every date, one per line item. On a busier store most dates have several orders, and the distinct count is what keeps them right.

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 Shop Tables · orders, order_items 2 tables

Table · orders

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

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Your task

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