How long does a parcel really take?
Customer care keeps hearing "my parcel took forever". Dates in this marketplace are stored as ISO text (YYYY-MM-DD), so you can group them by month with strftime and measure the gap in days with julianday. Only orders that were actually delivered (status = 'Delivered') have a delivered_date.
Task: for each order month return month (format YYYY-MM of order_date), delivered_orders and avg_days_to_deliver (average days from order_date to delivered_date, ROUND(x, 2)). Sort by month, oldest first.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | name TEXT | country TEXT | city TEXT | signup_date TEXT | loyalty_tier TEXT |
|---|---|---|---|---|---|
| 1 | Emily Carter | United States | Austin | 2025-01-11 | NULL |
| 2 | Michael Brooks | United States | Seattle | 2025-01-19 | Silver |
| 3 | Sofia Ramirez | United States | Miami | 2025-08-21 | Gold |
| 4 | Jake Turner | United States | Chicago | 2025-03-21 | Bronze |
| 5 | Oliver Hughes | United Kingdom | Manchester | 2025-03-22 | Bronze |
| 6 | Amelia Wright | United Kingdom | London | 2025-10-14 | Platinum |
Showing the first 6 of 48 rows.
| id INTEGER | order_id INTEGER | product_id INTEGER | quantity INTEGER | unit_price REAL | discount_pct INTEGER |
|---|---|---|---|---|---|
| 1 | 1 | 15 | 3 | 94.95 | 0 |
| 2 | 1 | 10 | 2 | 24.99 | 0 |
| 3 | 2 | 21 | 1 | 29 | 20 |
| 4 | 3 | 15 | 3 | 94.95 | 15 |
| 5 | 4 | 24 | 1 | 12.99 | 10 |
| 6 | 4 | 21 | 1 | 29 | 0 |
Showing the first 6 of 719 rows.
| id INTEGER | customer_id INTEGER | order_date TEXT | ship_date TEXT | delivered_date TEXT | status TEXT | channel TEXT |
|---|---|---|---|---|---|---|
| 1 | 1 | 2025-01-16 | 2025-01-19 | 2025-01-21 | Delivered | Mobile App |
| 2 | 1 | 2025-01-23 | 2025-01-25 | 2025-01-30 | Delivered | Web |
| 3 | 1 | 2025-01-23 | 2025-01-25 | 2025-01-29 | Delivered | Web |
| 4 | 2 | 2025-01-31 | 2025-02-02 | 2025-02-04 | Delivered | Web |
| 5 | 1 | 2025-02-02 | 2025-02-03 | 2025-02-06 | Delivered | Social Media |
| 6 | 31 | 2025-02-14 | 2025-02-15 | 2025-02-20 | Delivered | Web |
Showing the first 6 of 340 rows.
| id INTEGER | order_id INTEGER | method TEXT | amount REAL |
|---|---|---|---|
| 1 | 1 | PayPal | 334.83 |
| 2 | 2 | PayPal | 23.2 |
| 3 | 3 | Card | 242.12 |
| 4 | 4 | Card | 40.69 |
| 5 | 5 | Card | 148.14 |
| 6 | 6 | Gift Card | 42.95 |
Showing the first 6 of 329 rows.
| id INTEGER | name TEXT | category TEXT | seller_id INTEGER | price REAL | cost REAL |
|---|---|---|---|---|---|
| 1 | Noise-Cancelling Headphones | Electronics | 8 | 129.99 | 71 |
| 2 | Smart Fitness Watch | Electronics | 7 | 89.5 | 48 |
| 3 | Portable Power Bank 20000mAh | Electronics | 8 | 34.99 | 17 |
| 4 | 4K Action Camera | Electronics | 1 | 159 | 96 |
| 5 | USB-C Charging Hub | Electronics | 6 | 27.5 | 11 |
| 6 | Wireless Gaming Mouse | Electronics | 1 | 45.99 | 22 |
Showing the first 6 of 36 rows.
| id INTEGER | order_item_id INTEGER | return_date TEXT | reason TEXT | refund_amount REAL |
|---|---|---|---|---|
| 1 | 7 | 2025-02-10 | Wrong size | 18.99 |
| 2 | 31 | 2025-04-12 | Not as described | 34.99 |
| 3 | 39 | 2025-04-06 | Wrong size | 31.05 |
| 4 | 50 | 2025-05-09 | Changed my mind | 169 |
| 5 | 69 | 2025-05-27 | Arrived late | 34.99 |
| 6 | 74 | 2025-06-02 | Arrived late | 23.92 |
Showing the first 6 of 52 rows.
| id INTEGER | order_item_id INTEGER | rating INTEGER | comment_length INTEGER | review_date TEXT |
|---|---|---|---|---|
| 1 | 1 | 2 | NULL | 2025-01-22 |
| 2 | 2 | 4 | 232 | 2025-02-02 |
| 3 | 7 | 4 | 18 | 2025-02-16 |
| 4 | 12 | 5 | 255 | 2025-03-14 |
| 5 | 15 | 1 | 161 | 2025-03-15 |
| 6 | 17 | 4 | 208 | 2025-03-19 |
Showing the first 6 of 261 rows.
| id INTEGER | name TEXT | country TEXT | joined_date TEXT |
|---|---|---|---|
| 1 | Northwind Traders | United States | 2024-06-14 |
| 2 | Brightside Goods | United Kingdom | 2024-09-02 |
| 3 | Kaufhaus Berlin | Germany | 2024-11-20 |
| 4 | Maison Petite | France | 2025-01-15 |
| 5 | Rio Bazaar | Brazil | 2024-08-08 |
| 6 | Bharat Emporium | India | 2024-07-19 |
Showing the first 6 of 10 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
julianday(a) - julianday(b) gives the number of days between two dates.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.