Lesson 17.2

SQL Playground Mission: Revenue Audit Report

Build a monthly shipped-revenue audit from order line items.

Revenue reports usually come from line items, not just orders. The order row says when the purchase happened and whether it shipped; the line-item rows say what each order was worth.

This mission combines a filter, a join, date bucketing, a sum, and a distinct order count. It is a realistic audit pattern: state the population first (shipped orders), then summarize only those rows.

Pattern

select date_bucket,
       round(sum(quantity * unit_price), 2) as revenue,
       count(distinct order_id) as orders
from order_lines
where status = 'shipped'
group by date_bucket
order by date_bucket

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

SchemaTable · order_items5 columns · 48 rows

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id intorder_id intproduct_id intquantity intunit_price decimal

Your task

Return monthly shipped revenue. Use order_month,revenue, and orders. Revenue isquantity * unit_price rounded to two decimals. Sort by month.