Get a count of an enum if its value has been changed in the past month based on an updated

Apologies in advance if the title was unclear. I'll try to explain more here.

I have two tables, a non-temporal table and a temporal version of that table that stores the updated_at value of a row, and its historical values.

I want to count all the values of a row (in the non-temporal table) that have changed in the past 30 days, using the temporal table. These values are enums.

Here's an example.


create temporary table headcount (id int, hiring_status text, pay float);

create temporary table headcount_history (id int, hiring_status text, pay float, updated_at date);

insert into headcount values 
    (1, 'interviewing', 1000.00),
    (2, 'hired', 1000.00),
    (3, 'scouting', 1000.00),
    (4, 'interviewing', 100.12),
    (5, 'scouting', 123.42);

insert into headcount_history values 
    (1, 'scouting', 1000.00, '2022-07-25'),
    (1, 'scouting', 1005.00, '2022-07-26'),
    (1, 'scouting', 1005.00, '2022-07-27'),
    (1, 'interviewing', 1005.00, '2022-07-28'),

    (2, 'scouting', 1000.00, '2022-03-20'),
    (2, 'interviewing', 1000.00,'2022-04-20'),
    (2, 'hired', 3230.00, '2022-4-23'),
    (2, 'hired', 1000.00, '2022-4-25'),


    (3, 'scouting', 1000.00, '2022-03-20'),
    (3, 'interviewing', 1000.00,'2022-04-20'),
    (3, 'scouting', 1000.00, '2022-7-25'),

    (4, 'scouting', 1000.00, '2022-6-25'),
    (4, 'interviewing', 100.12, '2022-7-25'),

    (5, 'scouting', 1000.0, '2022-6-29'),
    (5, 'scouting', 123.42, '2022-7-10');

/* 
The goal is to find the number of hiring_statuses that were changed in the past 30 days 
for each hiring_status.

this is expected output 

scouting | interviewing | hired 
2        | 2            | 0

here's a link to a db fiddle - https://dbfiddle.uk/?rdbms=postgres_13&fiddle=79ebffe4a28ef67d8552d36d47e967e7

I'm using postgres 13

CodePudding user response：

    select  sum(coalesce(hired,0)) hired,
            sum(coalesce(scouting,0)) scouting,
            sum(coalesce(interviewing,0)) interviewing
    from
    (
    select  case when hiring_status ='hired' then h_cnt end "hired",
            case when hiring_status ='scouting' then h_cnt end "scouting",
            case when hiring_status ='interviewing' then h_cnt end "interviewing"
    from
    (
    
        select b.hiring_status,coalesce(a.cnt,0) h_cnt
        from 
        (select     hiring_status,
                count(1) cnt
        from    headcount_history 
        where   updated_at between current_date and current_date - interval '-30 days' 
        group by hiring_status) A right outer join
        (select     hiring_status
        from    headcount_history
        group by hiring_status)B
        on a.hiring_status = b.hiring_status
    )AA
    ) BB