Quarterly Cancellation Rate
Operations suspects cancellations are rising. For each calendar quarter, return the number of orders placed (any status) and the percentage of those orders that were cancelled.
Expected output
quarter (formatted like 2025-Q1), orders, cancel_pct (percentage rounded to 1 decimal). Sorted by quarter.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: E-commerce store
An online store selling electronics, home goods, books, apparel and beauty products to customers in six countries (Jan 2025 to Mar 2026).
customers
One row per registered customer.
| customer_id | INTEGER | primary key |
| name | TEXT | |
| country | TEXT | US, UK, DE, IN, BR or CA |
| signup_date | TEXT | YYYY-MM-DD |
| referred_by | INTEGER | customer_id of the referrer, NULL if none |
products
Product catalog with current list price.
| product_id | INTEGER | primary key |
| name | TEXT | |
| category | TEXT | |
| price | REAL | list price in USD |
orders
One row per order. Only completed orders count as revenue.
| order_id | INTEGER | primary key, increases with order_date |
| customer_id | INTEGER | |
| order_date | TEXT | YYYY-MM-DD |
| status | TEXT | completed, cancelled or returned |
order_items
Line items. Revenue of a line = quantity * unit_price (unit_price may include a discount).
| order_id | INTEGER | |
| product_id | INTEGER | |
| quantity | INTEGER | |
| unit_price | REAL | price actually charged per unit |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Monthly Revenue Trend (easy)
- Days from Signup to First Order (medium)
- Running Revenue Total for 2025 (medium)
- Month-over-Month Revenue Growth (medium)
- Time Between First and Second Order (medium)