partition by postgres example, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

with transactions as (
      select '2018-01-03'::date as transaction_date, 3828 as buyerid, 219 as sellerid union all
      select '2018-01-08', 2831, 123 union all
      select '2018-02-10', 3828, 219 UNION ALL
      select '2018-02-11', 38, 219 UNION ALL
      select '2018-02-17', 3828, 219 
     ),
     combos AS (
      SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY buyerid, sellerid ORDER BY t.transaction_date) as combo_seqnum,
             ROW_NUMBER() OVER (PARTITION BY buyerid, sellerid, date_trunc('month', t.transaction_date) ORDER BY t.transaction_date) as combo_month_seqnum
      FROM transactions t
      WHERE t.transaction_date < '2018-05-01'
    )
SELECT to_char(transaction_date, 'YYYY-MM'),
       SUM( (combo_month_seqnum = 1)::int ) as uniques_in_month,
       SUM(SUM( (combo_seqnum = 1)::int )) OVER (ORDER BY to_char(transaction_date, 'YYYY-MM')) as uniques_through_month
FROM combos
GROUP BY to_char(transaction_date, 'YYYY-MM')


absolute service time: 0,71 sec