Signed up, never bought
Every month the CRM team sends a "your first order is 10% off" email to customers who registered but never placed a single order (cancelled orders count as orders here, the customer did try to buy). They need the audience list.
Task: return name, country and signup_date of every customer with no row in orders. Sort by signup_date (oldest first) and then by name A to Z.
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?
A LEFT JOIN keeps customers without orders; their order columns are NULL.
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.