Union result from 2 years ago

Question

I am on Postgres and I have two tables table_a and table_b and their schema is:

                          Table "public.table_a"
     Column      |         Type          | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
 name            | character varying(10) |           |          |
 asof_yrmo       | integer               |           |          |

                       Table "public.table_b"
  Column   |         Type          | Collation | Nullable | Default
-----------+-----------------------+-----------+----------+---------
 name      | character varying(10) |           |          |
 comp      | character varying(1)  |           |          |
 asof_yrmo | integer               |           |          |

Here's some sample data for each table:

select name, asof_yrmo from table_a limit 10;
  name  | asof_yrmo
--------+-----------
 Will   |    202101
 James  |    202101
 Samuel |    202101
 John   |    202101
 George |    202101
 Will   |    202012
 James  |    202012
 Samuel |    202012
 John   |    202012
 George |    202012
(10 rows)

select name, comp, asof_yrmo from table_b limit 10;
  name  | comp | asof_yrmo
--------+------+-----------
 Will   | Y    |    202101
 James  | Y    |    202101
 Samuel | Y    |    202101
 John   | N    |    202101
 George | N    |    202101
 Will   | N    |    202012
 James  | N    |    202012
 Samuel | Y    |    202012
 John   | Y    |    202012
 George | Y    |    202012
(10 rows)

The interesting column is asof_yrmo which has values such as 202101 and 201901 (basically, it is the year and month).

I have the following SQL query:

select
  a.name,
  a.asof_yrmo,
  plu.comp 
from
  table_a as a 
  left join
    table_b as plu 
    on plu.name = a.name 
    and plu.asof_yrmo = a.asof_yrmo 
where
  a.asof_yrmo = 
  (
    select
      max(s60.asof_yrmo) 
    from
      table_a as s60 
  )
group by
  a.name,
  a.asof_yrmo,
  plu.comp;

Which results in:

  name  | asof_yrmo | comp
--------+-----------+------
 George |    202101 | N
 James  |    202101 | Y
 John   |    202101 | N
 Samuel |    202101 | Y
 Will   |    202101 | Y
(5 rows)

What I want to do is have another column at the end called comp_from_previous_two_years and have that show the comp value from the previous 2 years. So if we have 202101 for asof_yrmo then the column would show the comp value for 201901

So the expected output will be:

  name  | asof_yrmo | comp | comp_from_previous_two_years
--------+-----------+------+------------------------------
 George |    202101 | N    | Y
 James  |    202101 | Y    | N
 John   |    202101 | N    | N
 Samuel |    202101 | Y    | Y
 Will   |    202101 | Y    | N

I know that I have to grab the year part and I've managed to do that with left(cast (asof_yrmo as varchar), 4) but I am really struggling with creating the union to show both.

user_213136 · Answer

You mention using a "union", but I think what you really want here is a "join". Given your ASOF_YRMO column, which contains year and month as a YYYYMM integer, the data from two years previous has a 'year-month' value with is the current value - 200, since the low-order digit of the year is in the hundred's position of the values. So your query should look something like:

select a.name,
       a.asof_yrmo,
       b.comp,
       bb.comp AS comp_from_previous_two_years
  from table_a as a 
  left join table_b as b 
    on b.name = a.name and
       b.asof_yrmo = a.asof_yrmo
  LEFT OUTER JOIN table_b AS BB
    ON bb.name = a.name AND
       bb.ASOF_YRMO = b.ASOF_YRMO - 200
  where a.asof_yrmo = (select max(s60.asof_yrmo) 
                         from table_a as s60)
  ORDER BY a.name

db<>fiddle here

user_3984221 · Answer

You need to do a pivot by using a conditional group. This can be done using the FILTER clause:

demo:db<>fiddle

SELECT 
    a.name,
    MAX(a.asof_yrmo) as asof_yrmo,
    MAX(comp) FILTER (WHERE a.asof_yrmo = '202101') as comp,                    -- 2
    MAX(comp) FILTER (WHERE a.asof_yrmo = '202012') as comp_from_prev_two_years -- 3
FROM table_a a
JOIN table_b b ON a.name = b.name AND a.asof_yrmo = b.asof_yrmo                 -- 1
GROUP BY a.name

Join the tables on name and asof_yrmo
Group. Use the FILTER clause to recognize only the values from one asof_yrmo value. Don't mind the MAX() function. This is necessary because we actually do a GROUP aggregation. But because we only have one data set here, MAX(x) equals x.
The same can be done for the next asof_yrmo value of course.

Union result from 2 years ago

2 Answers