pivott table with conditions

For example I have a table

day        id    event
1999-01-01  1     buy
1999-01-05  2     return
2000-01-10  3     buy
2000-05-12  1     return

I need to create a table that looks like this:

month     count_buy   count_return_from_last_year
1999      2            
2000      1           1

I suppose this could be solved by a window functions + self joins

Solution

This should do it:

WITH counts AS (
    SELECT
        to_char(date, 'YYYY-MM') AS month,
        count(*) filter (WHERE event = 'buy') AS count_buy,
        count(*) filter (WHERE event = 'return') AS count_return
    FROM data
    GROUP BY 1
)
SELECT
    month,
    count_buy,
    lag(count_return) OVER (ORDER BY month) AS count_return_from_last_month
FROM counts
ORDER BY month;

Note though that for the lag to always be for the previous month, you need to have a count for each month. If there are holes in the data, then the lag will pick the previous available month. To address that, you'd first have to join with a series of all months:

WITH counts AS (
    SELECT
        to_char(month, 'YYYY-MM') AS month,
        count(*) filter (WHERE event = 'buy') AS count_buy,
        count(*) filter (WHERE event = 'return') AS count_return
    FROM generate_series('1999-01-01'::date, '1999-12-01'::date, '1 month') AS month
    LEFT JOIN data ON date_trunc('month', date) = month
    GROUP BY 1
)
SELECT
    month,
    count_buy,
    lag(count_return) OVER (ORDER BY month) AS count_return_from_last_month
FROM counts
ORDER BY month;