You have a table orders with columns customer_id, order_date, and total. How would you write a query to find the total sales per customer per month?

hard📝 Application Q8 of 15

PostgreSQL - Aggregate Functions and GROUP BY

You have a table orders with columns customer_id, order_date, and total. How would you write a query to find the total sales per customer per month?

ASELECT customer_id, order_date, SUM(total) FROM orders GROUP BY customer_id;

BSELECT customer_id, order_date, SUM(total) FROM orders GROUP BY order_date;

CSELECT customer_id, MONTH(order_date), SUM(total) FROM orders GROUP BY customer_id, MONTH(order_date);

DSELECT customer_id, DATE_TRUNC('month', order_date), SUM(total) FROM orders GROUP BY customer_id, DATE_TRUNC('month', order_date);

Step-by-Step Solution

Solution:

Step 1: Extract month from order_date
Use DATE_TRUNC('month', order_date) to get the month part for grouping.
Step 2: Group by customer_id and month
Group by customer_id and the truncated month to get total sales per customer per month.
Final Answer:
SELECT customer_id, DATE_TRUNC('month', order_date), SUM(total) FROM orders GROUP BY customer_id, DATE_TRUNC('month', order_date); -> Option D
Quick Check:
Group by customer and month using DATE_TRUNC [OK]

Quick Trick: Use DATE_TRUNC('month', date) to group by month [OK]

Common Mistakes:

Using order_date directly without truncation
Using non-PostgreSQL MONTH() function
Grouping by only one column

Master "Aggregate Functions and GROUP BY" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

You have a table orders with columns customer_id, order_date, and total. How would you write a query to find the total sales per customer per month?

Step 1: Extract month from order_date

Step 2: Group by customer_id and month

Final Answer:

Quick Check:

Want More Practice?