英文:
How to pull data from two CTEs in Snowflake
问题
I'm trying to pull current ytd sales and prior ytd sales for the same period. I have the SQL that pulls the data correctly (separately), but I don't know how to put it together in a CTE, or if that is even the best way to do it. Here is what I have so far:
with current_year AS
(
SELECT
SUM(TOTAL_REVENUE_USD) AS 当年总收入
FROM
FACT_ORDER
WHERE
year(COMMISSION_DATE) = year(current_date)
),
prior_year AS
(
SELECT
SUM(TOTAL_REVENUE_USD) AS 去年同期总收入
FROM
FACT_ORDER
WHERE
COMMISSION_DATE BETWEEN date_from_parts((date_part(year,current_date())-1),1,1)
AND dateadd(year,-1,current_date());
)
I've tried using CTEs (see the above code), but I am stuck. I don't know where to go from here. I would like the result to be something like:
2022 $10,000,000
2023 $11,000,000
SAMPLE DATA:
英文:
I'm trying to pull current ytd sales and prior ytd sales for the same period. I have the SQL that pulls the data correctly (separately), but I don't know how to put it together in a CTE, or if that is even the best way to do it. Here is what I have so far:
with current_year AS
(
SELECT
SUM(TOTAL_REVENUE_USD) AS CURRENT_YEAR_REVENUE
FROM
FACT_ORDER
WHERE
year(COMMISSION_DATE) = year(current_date)
),
prior_year AS
(
SELECT
SUM(TOTAL_REVENUE_USD) AS PRIOR_YEAR_REVENUE
FROM
FACT_ORDER
WHERE
COMMISSION_DATE BETWEEN date_from_parts((date_part(year,current_date())-1),1,1)
AND dateadd(year,-1,current_date());
)
I've tried using CTEs (see the above code), but I am stuck. I don't know where to go from here. I would like the result to be something like:
2022 $10,000,000
2023 $11,000,000
SAMPLE DATA:
答案1
得分: 1
你似乎想要一个 union：
select *
from current_year
union all
select *
from previous year
但由于CTE缺少列 year：
select 2023 year, *
from current_year
union all
select 2022, *
from previous year
英文:
It seems you want an union:
select *
from current_year
union all
select *
from previous year
But since the CTEs are missing the column year:
select 2023 year, *
from current_year
union all
select 2022, *
from previous year
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论