Increase Count Sequentially

Question

I have a dataset which tracks when a user reads a website. A user can read a website and at anytime therefore the user will appear multiple time. I want to create a column which tracks the number of times a user reads a specific website. But since it is a time series, the count should be incremental. I have about 28gbs so pandas will not be able to handle the work load, so I have to write it in sql.

Sample data below:

Date    ID  WebID
201901  Bob X-001
201902  Bob X-002
201903  Bob X-001
201901  Sue X-001

Expected Results:

Date    ID  WebID   Count
201901  Bob X-001   1
201902  Bob X-002   1
201903  Bob X-001   2
201901  Sue X-001   1

@ZaynulAbadinTuhin because Bob read the website on 201901. So it should increase by 1 — Bryce Ramgovind, Aug 05 '19 at 08:08

Zaynul Abadin Tuhin · Accepted Answer · 2019-08-05T08:17:06.420

2

use row_number()

select *,row_number() over(partition by id,webid order by date) cnt
from table
order by date,id

edited Aug 05 '19 at 08:17

answered Aug 05 '19 at 08:11

Zaynul Abadin Tuhin

31,407
5
33
63

score -1 · Answer 2 · edited Aug 05 '19 at 08:55

-1

You can use below sql query:

Select  count(*) "Count" , Date, ID, WebID, from table group by webid, id, date

edited Aug 05 '19 at 08:55

Zaynul Abadin Tuhin

31,407
5
33
63

answered Aug 05 '19 at 08:51

DS Passionate

86
6

Increase Count Sequentially

2 Answers2