Query to Get Orders Placed in the Last 7 Days
This explanation will cover:
- The SQL query to retrieve recent orders
- Creating a dummy table with sample data
- 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 dateINTERVAL '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;
At Online Learner, we're on a mission to ignite a passion for learning and empower individuals to reach their full potential. Founded by a team of dedicated educators and industry experts, our platform is designed to provide accessible and engaging educational resources for learners of all ages and backgrounds.
Terms Disclaimer About Us Contact Us
Copyright 2023-2025 © All rights reserved.