Skip to content

Instantly share code, notes, and snippets.

@boo1ean
Last active April 9, 2024 12:40
Show Gist options
  • Save boo1ean/3318a23da81d5bbb93404d0095036fb9 to your computer and use it in GitHub Desktop.
Save boo1ean/3318a23da81d5bbb93404d0095036fb9 to your computer and use it in GitHub Desktop.

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
  }
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment