total-users.sql.md

db: postgres

table cols: timestamp, userId, event { 'in', 'out' }

The report should be:

for given time range (startDate, endDate)

grouped by period (day, week, month)

return for each interval of time range (partitioned by period) calc total count of users that remain in (through all table history) at the end of the corresponding interval

*The user is considered in if there is an in event and no following out (until the end of the given interval)

Example dataset:

2024-01-02T02:33:55Z, 1, 'in'
2024-01-02T02:35:55Z, 1, 'out'
2024-01-02T02:38:55Z, 1, 'in'
2024-01-02T02:38:55Z, 2, 'in'
2024-01-03T02:38:55Z, 2, 'out'

Example query:

{
  startDate: '2024-01-01',
  endDate: '2024-01-04',
  period: 'day'
}

Example result:

[
  {
    period: '2024-01-01T00:00:00Z',
    totalUsers: 0
  },
  {
    period: '2024-01-02T00:00:00Z',
    totalUsers: 2
  },
  {
    period: '2024-01-03T00:00:00Z',
    totalUsers: 1
  },
  {
    period: '2024-01-04T00:00:00Z',
    totalUsers: 1
  }
]