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

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?

Answer

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
;