Noruj.com
easy

Total Spend per Customer This Year

You are a data analyst at CartZone, a fast-growing e-commerce platform. The marketing team is preparing a Loyalty Rewards campaign and needs to know how much each customer has spent on the platform during the current calendar year. Customers who have spent more will be targeted with exclusive discount offers, so it is critical that the figures are accurate and only reflect orders placed this year.

The database contains two tables: customers — which stores customer profile data including customer_id, first_name, and last_name — and orders — which records every purchase made on the platform, including order_id, customer_id, order_date, and total_amount (the monetary value of the order in USD). Each row in orders represents one completed purchase by a customer.

Your task is to write a SQL query that returns the full name of each customer (as a single customer_name column combining first and last name) and their total amount spent on orders placed in the year 2024. Only include customers who placed at least one order in 2024. Sort the results by total spend in descending order so the highest spenders appear first.

  • Join the customers table with the orders table on customer_id.

  • Filter orders to only include rows where order_date falls within the year 2024 using EXTRACT(YEAR FROM order_date) = 2024 or a date range with BETWEEN.

  • Concatenate first_name and last_name into a single column aliased as customer_name.

  • Use SUM(total_amount) to calculate each customer's total spend, aliased as total_spend.

  • Group results by customer_id, first_name, and last_name.

  • Sort output by total_spend in descending order.

Hint: When concatenating names in PostgreSQL, use the || operator with a space in between, like first_name || ' ' || last_name. A common pitfall is filtering by year inside the HAVING clause rather than the WHERE clause — always filter rows before grouping using WHERE for better performance and correctness. Also remember to group by all non-aggregated columns in your SELECT list to avoid errors.

1
2
3
4
5
Preparing...
Total Spend per Customer This Year

You are a data analyst at CartZone , a fast-growing e-commerce platform. The marketing team is preparing a Loyalty Rewards campaign and needs to know how much each customer has spent on the platform during the current calendar year . Customers who have spent more will be targeted with exclusive discount offers, so it is critical that the figures are accurate and only reflect orders placed this year. The database contains two tables: customers — which stores customer profile data including customer_id , first_name , and last_name — and orders — which records every purchase made on the platform, including order_id , customer_id , order_date , and total_amount (the monetary value of the order in USD). Each row in orders represents one completed purchase by a customer. Your task is to write a SQL query that returns the full name of each customer (as a single customer_name column combining first and last name) and their total amount spent on orders placed in the year 2024 . Only include customers who placed at least one order in 2024. Sort the results by total spend in descending order so the highest spenders appear first. Join the customers table with the orders table on customer_id . Filter orders to only include rows where order_date falls within the year 2024 using EXTRACT(YEAR FROM order_date) = 2024 or a date range with BETWEEN . Concatenate first_name and last_name into a single column aliased as customer_name . Use SUM(total_amount) to calculate each customer's total spend, aliased as total_spend . Group results by customer_id , first_name , and last_name . Sort output by total_spend in descending order. Hint: When concatenating names in PostgreSQL, use the || operator with a space in between, like first_name || ' ' || last_name . A common pitfall is filtering by year inside the HAVING clause rather than the WHERE clause — always filter rows before grouping using WHERE for better performance and correctness. Also remember to group by all non-aggregated columns in your SELECT list to avoid errors.

Table Setup (SQL Schema)

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id),
  order_date DATE NOT NULL,
  total_amount NUMERIC(10, 2) NOT NULL
);

INSERT INTO customers (first_name, last_name) VALUES
  ('Sophie', 'Anderson'),
  ('Marcus', 'Lee'),
  ('Priya', 'Sharma'),
  ('James', 'Okafor'),
  ('Nina', 'Petrov'),
  ('Ethan', 'Cruz'),
  ('Laura', 'Bennett');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
  (1, '2024-01-15', 120.00),
  (1, '2024-03-22', 89.50),
  (1, '2024-07-04', 210.75),
  (2, '2024-02-10', 340.00),
  (2, '2024-05-18', 95.00),
  (3, '2024-01-30', 60.25),
  (3, '2024-04-11', 180.00),
  (3, '2024-09-09', 220.00),
  (3, '2024-11-25', 75.50),
  (4, '2024-06-01', 499.99),
  (4, '2024-08-14', 150.00),
  (5, '2024-03-05', 33.00),
  (6, '2024-10-20', 275.00),
  (6, '2024-12-01', 310.50),
  (1, '2023-11-10', 999.00),
  (2, '2023-12-25', 450.00),
  (7, '2023-08-08', 130.00);
Home Videos Quiz Blog