Query to Get Orders Placed in the Last 7 Days

This explanation will cover:

1. The SQL query to retrieve recent orders
2. Creating a dummy table with sample data
3. Expected output

The SQL Query

SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY order_date DESC;

Explanation:

• CURRENT_DATE returns today's date
• INTERVAL '7 days' subtracts 7 days from today
• The WHERE clause filters for orders on or after that date
• ORDER BY order_date DESC shows most recent orders first

Alternative syntax for different databases:

MySQL/MariaDB:

SELECT *
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY order_date DESC;

SQL Server:

SELECT *
FROM orders
WHERE order_date >= DATEADD(day, -7, GETDATE())
ORDER BY order_date DESC;

Creating a Dummy Table with Sample Data

-- Create orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- Insert dummy data
INSERT INTO orders VALUES
(1, 101, CURRENT_DATE - INTERVAL '1 day', 150.00, 'Shipped'),
(2, 102, CURRENT_DATE - INTERVAL '3 days', 225.50, 'Processing'),
(3, 103, CURRENT_DATE - INTERVAL '5 days', 89.99, 'Delivered'),
(4, 104, CURRENT_DATE - INTERVAL '8 days', 300.00, 'Shipped'),
(5, 105, CURRENT_DATE - INTERVAL '2 days', 45.25, 'Processing'),
(6, 101, CURRENT_DATE - INTERVAL '6 days', 120.75, 'Delivered'),
(7, 106, CURRENT_DATE - INTERVAL '10 days', 199.99, 'Shipped'),
(8, 102, CURRENT_DATE - INTERVAL '0 days', 75.50, 'Processing'),
(9, 107, CURRENT_DATE - INTERVAL '4 days', 250.00, 'Shipped'),
(10, 103, CURRENT_DATE - INTERVAL '7 days', 99.99, 'Delivered');

Expected Output

Assuming today is 2023-11-15, the query would return:

order_id | customer_id | order_date | order_amount | status
---------+-------------+------------+--------------+-----------
8        | 102         | 2023-11-15 | 75.50        | Processing
1        | 101         | 2023-11-14 | 150.00       | Shipped
5        | 105         | 2023-11-13 | 45.25        | Processing
9        | 107         | 2023-11-11 | 250.00       | Shipped
2        | 102         | 2023-11-12 | 225.50       | Processing
6        | 101         | 2023-11-09 | 120.75       | Delivered
3        | 103         | 2023-11-10 | 89.99        | Delivered
10       | 103         | 2023-11-08 | 99.99        | Delivered

Note that order #4 and #7 are excluded because they're older than 7 days, and order #10 is included because it's exactly 7 days old (depending on your exact definition of "last 7 days").

Variations

If you want to exclude today and get only the previous 7 complete days:

SELECT *
FROM orders
WHERE order_date BETWEEN CURRENT_DATE - INTERVAL '8 days' AND CURRENT_DATE - INTERVAL '1 day'
ORDER BY order_date DESC;