Snowflake-SQL - Add row from one query to another query

Question

I'm looking for assistance in combining query results.

I have one query that results in:

YEAR    MONTH   T_DATE  TypeACount
2021    9   2021-09-01  13280
2021    8   2021-08-01  24508
2021    7   2021-07-01  21014
2021    6   2021-06-01  19666

The query:

select
    EXTRACT(YEAR FROM  f.value:paymentDate::date) as year,
    EXTRACT(MONTH FROM  f.value:paymentDate::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:payments[0].paymentMethod.source = 'SourceA' then 1 end) as "TypeACount"
from public.transactions t,
        lateral flatten(input => t.v, path => 'payments') f
where 
    f.value:paymentDate::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
    and
    f.value:paymentMethod.source = 'SourceA'
    and
    f.value:status = 'paid'
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC

and I have another query that results in:

YEAR    MONTH   T_DATE  TypeBCount  TypeCCount  TypeDCount  TypeECount
2021    9   2021-09-01  3639        1340        6800        56401
2021    8   2021-08-01  6185        2482        11707       96122
2021    7   2021-07-01  5485        1680        10820       92394
2021    6   2021-06-01  5423        521         10643       97303

The query:

select
    EXTRACT(YEAR FROM v:created::date) as year,
    EXTRACT(MONTH FROM v:created::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:category[0].source = 'TypeB' then 1 end) as TypeBCount,
    count (case when v:category[0].source = 'TypeC' then 1 end) as TypeCCount,
    count (case when v:category[0].source = 'TypeD' then 1 end) as TypeDCount,
    count (case when v:category[0].source = 'TypeE' then 1 end) as TypeECount
from PUBLIC.TRANSACTIONS
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC

Is there a way to add the TypeACount column to the second query?

Could you post the queries themselves?

Peter Leimbigler
– Peter Leimbigler

2021-09-17 00:53:20 +00:00
Commented Sep 17, 2021 at 0:53 — Peter Leimbigler
– Peter Leimbigler, Commented Sep 17, 2021 at 0:53
Sorry about that - will do. Added to the post.

Maandeep
– Maandeep

2021-09-17 01:13:17 +00:00
Commented Sep 17, 2021 at 1:13 — Maandeep
– Maandeep, Commented Sep 17, 2021 at 1:13

Greg Pavlik · Accepted Answer · 2021-09-17 01:20:45Z

You can run both queries in a table expressions in a CTE and then join them.

with A as
(
select
    EXTRACT(YEAR FROM  f.value:paymentDate::date) as year,
    EXTRACT(MONTH FROM  f.value:paymentDate::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:payments[0].paymentMethod.source = 'SourceA' then 1 end) as "TypeACount"
from public.transactions t,
        lateral flatten(input => t.v, path => 'payments') f
where 
    f.value:paymentDate::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
    and
    f.value:paymentMethod.source = 'SourceA'
    and
    f.value:status = 'paid'
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC
),
B as
(
select
    EXTRACT(YEAR FROM v:created::date) as year,
    EXTRACT(MONTH FROM v:created::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:category[0].source = 'TypeB' then 1 end) as TypeBCount,
    count (case when v:category[0].source = 'TypeC' then 1 end) as TypeCCount,
    count (case when v:category[0].source = 'TypeD' then 1 end) as TypeDCount,
    count (case when v:category[0].source = 'TypeE' then 1 end) as TypeECount
from PUBLIC.TRANSACTIONS
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC
)
select * from A
  left join B
    on A.YEAR = B.YEAR and A.MONTH = B.MONTH
;

Collectives™ on Stack Overflow

Snowflake-SQL - Add row from one query to another query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related