Skip to main content
Noruj.com
Find Orders Placed on Weekends

You are working as a data analyst for an e-commerce company called ShopNest . The business team has noticed that weekend shopping behavior tends to differ significantly from weekday purchases — customers often spend more and buy different product categories. To support an upcoming marketing campaign targeting weekend shoppers, the team needs a list of all orders that were placed on a Saturday or Sunday . You have access to the orders table, which records every customer transaction on the platform. Here is a summary of the relevant schema: orders — order_id (PK), customer_id , order_date (DATE), amount (NUMERIC), status (VARCHAR) Your task is to query the orders table and return all orders where the order_date falls on a weekend. Results should be ordered by order_date ascending so the marketing team can review them chronologically. Requirements: Return order_id , customer_id , order_date , amount , and status . Only include rows where order_date is a Saturday or Sunday. Order the results by order_date ascending. Hint: In PostgreSQL, the EXTRACT(DOW FROM date) function returns the day of the week as a number — 0 for Sunday and 6 for Saturday. Alternatively, you can use TO_CHAR(order_date, 'Day') to get the day name, but EXTRACT is cleaner and more reliable for numeric comparisons. A common pitfall is assuming Monday = 1 through Sunday = 7 (ISO week numbering) — remember that PostgreSQL's DOW is zero-indexed starting on Sunday.

Table Setup (SQL Schema)

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount NUMERIC(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL
);

INSERT INTO orders (customer_id, order_date, amount, status) VALUES
  (1, '2024-03-01', 120.00, 'completed'),
  (2, '2024-03-02', 85.50, 'completed'),
  (3, '2024-03-03', 200.00, 'completed'),
  (4, '2024-03-04', 45.00, 'pending'),
  (5, '2024-03-05', 310.75, 'completed'),
  (6, '2024-03-06', 99.99, 'cancelled'),
  (7, '2024-03-07', 150.00, 'completed'),
  (8, '2024-03-08', 60.00, 'completed'),
  (9, '2024-03-09', 175.25, 'completed'),
  (10, '2024-03-10', 220.00, 'pending'),
  (1, '2024-03-11', 95.00, 'completed'),
  (2, '2024-03-12', 410.00, 'completed'),
  (3, '2024-03-13', 33.00, 'cancelled'),
  (4, '2024-03-14', 78.50, 'completed'),
  (5, '2024-03-15', 500.00, 'completed'),
  (6, '2024-03-16', 65.00, 'completed'),
  (7, '2024-03-17', 145.99, 'pending'),
  (8, '2024-03-18', 290.00, 'completed'),
  (9, '2024-03-19', 55.00, 'completed'),
  (10, '2024-03-20', 180.00, 'completed');
HomeVideosBlog