Suppose you have a table called "orders" with the following columns: "order_id" (integer), "customer_id" (integer), "order_date" (date), and "total_price" (decimal). and we want to find out the total revenue generated by each customer in the month of Jan 2022.
Solution 1:
SELECT customer_id, SUM(total_price) AS total_revenue FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31' GROUP BY customer_id;
In this solution, we use the
SUM() function to add up the "total_price" column for each customer, and the GROUP BY clause to group the results by customer_id. We also use the BETWEEN operator to filter the results to only include orders made in the month of January 2022. The resulting output will show the customer_id and the total revenue generated by each customer during that time period.Solution 2:
SELECT
customer_id,
SUM(CASE
WHEN order_date BETWEEN '2022-01-01' AND '2022-01-31'
THEN total_price
ELSE 0
END) AS total_revenue
FROM orders
GROUP BY customer_id;
This solution also calculates the total revenue generated by each customer in the month of January 2022. However, instead of using a
WHERE clause to filter the data, it uses a CASE expression inside a SUM function to only include orders made within the specific time period. The GROUP BY clause groups the results by customer_id.
