英文:
want total revenue for last 30 days from max date and age of customers between 25 and 30 calculated from DOB
问题
对于年龄在25到35岁之间的所有客户，找出这些客户在数据中可用的最大交易日期的最后30天内生成的净总收入是多少？
尝试了很多方法，但无法解决这个问题。
我的代码：
SELECT
SUM(total_amt) [净总收入]
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
英文:
For all customers aged between 25 to 35 years find what is the net total revenue generated by these consumers in last 30 days of transactions from max transaction date available in the data?
Tried many things but not able to solve this
My 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
答案1
得分: 1
我建议使用窗口函数来获取最大日期：
SELECT SUM(t.total_amt) as net_total_revenue
FROM (SELECT t.*,
MAX(t.tran_date) OVER () as max_tran_date
FROM Transactions t
) t JOIN
Customer c
ON t.cust_id = c.customer_Id
WHERE t.tran_date >= DATEADD(day, -30, t.max_tran_date) AND
t.tran_date >= DATEADD(YEAR, 25, c.DOB) AND
t.tran_date < DATEADD(YEAR, 31, c.DOB);
一些注意事项：
- 请明确所有列名的来源，以便清楚其来源。
DATEDIFF()不是您所认为的那样工作。它计算两个日期之间的1月1日的数量。使用DATEADD()更准确。- 不要使用带有空格的列别名。使用无需转义的名称。
英文:
I would recommend window functions to get the maximum date:
SELECT SUM(t.total_amt) as net_total_revenue
FROM (SELECT t.*,
MAX(t.tran_date) OVER () as max_tran_date
FROM Transactions t
) t JOIN
Customer c
ON t.cust_id = c.customer_Id
WHERE t.tran_date >= DATEADD(day, -30, t.max_tran_date) AND
t.tran_date >= DATEADD(YEAR, 25, c.DOB) AND
t.tran_date < DATEADD(YEAR, 31, c.DOB);
Some notes:
- Qualify all column names so it is clear where they come from.
DATEDIFF()does not do what you think it does. It counts the number of Jan 1sts between two dates.DATEADD()is more accurate.- Don't name column aliases with spaces. Use names that don't need to be escaped.
答案2
得分: 0
以下是已翻译的代码部分：
尝试使用临时表进行操作
select t.cust_id,
sum(convert(float,t.total_amt)) as net_sales
into #temp4
from Transactions t
where t.cust_id in (select c.customer_id from Customer c where DATEDIFF(YY,DOB,getdate()) between 25 and 36)
group by t.cust_id,t.tran_date
having t.tran_date > DATEADD(DAY, -30, max(t.tran_date))
order by t.tran_date desc
GO
select t1.cust_id, sum(net_sales) as net_sales from #temp4 t1
group by t1.cust_id
order by sum(net_sales) desc
drop table #temp4
英文:
Tried to do it through temp tables
select t.cust_id,
sum(convert(float,t.total_amt)) as net_sales
into #temp4
from Transactions t
where t.cust_id in (select c.customer_id from Customer c where DATEDIFF(YY,DOB,getdate()) between 25 and 36)
group by t.cust_id,t.tran_date
having t.tran_date > DATEADD(DAY, -30, max(t.tran_date))
order by t.tran_date desc
GO
select t1.cust_id, sum(net_sales) as net_sales from #temp4 t1
group by t1.cust_id
order by sum(net_sales) desc
drop table #temp4
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论