Calc total rev last 30 days, age 25-30 from DOB

Programming
,
1

Task: Find the net total revenue generated by customers aged 25-35 in the last 30 days

I am trying to find the total revenue generated by customers aged 25-35 in the last 30 days using the following code:

SELECT
SUM(total_amt) [NET TOTAL REVENUE]
FROM Transactions 
                  INNER JOIN Customer ON Transactions.cust_id = Customer.customer_Id
WHERE tran_date >= DATEADD(day,-30,MAX(tran_date)) and DATEDIFF(YEAR,DOB,tran_date) between 25 and 30

However, I am having difficulty solving this problem.

2

The issue in the code is that the DATEDIFF function is calculating the difference in years between the customer’s date of birth and the transaction date, which may not necessarily correspond to their age at the time of the transaction.

To find the net total revenue generated by customers aged 25-35 in the last 30 days, you can modify the code as follows:

SELECT SUM(total_amt) [NET TOTAL REVENUE]
FROM Transactions 
INNER JOIN Customer ON Transactions.cust_id = Customer.customer_Id
WHERE tran_date >= DATEADD(day,-30,GETDATE()) 
AND DATEDIFF(YEAR, DOB, GETDATE()) BETWEEN 25 AND 35

Here, we are using the GETDATE() function to get the current date and time, and then subtracting 30 days using the DATEADD() function to get the date 30 days ago. We are then checking for transactions that occurred on or after that date.

We are also using the DATEDIFF() function to calculate the difference in years between the customer’s date of birth and the current date, and checking if the result is between 25 and 35 (inclusive). This gives us the age range we are interested in.

By combining these conditions with the WHERE clause, we can find the net total revenue generated by customers aged 25-35 in the last 30 days.