python - Return count of column based on previous data -

how can count user_id group type, country_code , date current type d or , previous type d or a.

e.g query should return country_code 1 , type d => count(user_id) = 1 , country_code 2 , type => count(user_id) = 1

id  country_code type user_id  date 1   1        d     1       01-01-14 2   1        nd    1       02-01-14 3   1        d     1       03-01-14 4   1        d     1       04-01-14 5   2        d     1       05-01-14 6   2        nd    2       06-01-14 7   2            1       07-01-14 8   2            1       08-01-14 

i think want following:

select type, country_code, date, count(distinct user_id)   tbl x  type in ('d', 'a')    , exists  (select 1           tbl y          y.type in ('d', 'a')            , y.country_code = x.country_code            , y.date <= (select max(z.date)                            tbl z                           z.date < x.date                             , z.type = y.type                             , z.country_code = y.country_code))  group type, country_code, date 

i assumed wanted distinct count of user ids, if that's not case remove distinct keyword.

here sql fiddle test data: http://sqlfiddle.com/#!2/0c85e/4/0