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 Shop Table · orders 6 columns · 24 rows

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

Schema Table · order_items 5 columns · 48 rows

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

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