CODEWITHSUNDEEP
sqlpostgresql
Alexandra Salamatina
Alexandra Salamatina

Reputation: 17

How to the average number of records per hour in PostgreSQL

How can I calculate the average number of records per hour in PostgreSQL? I have a user column in my table and a task column (task execution time) in the format 2021-04-01 00: 00: 00.249000, Need to calculate: How many tasks does the user per hour How many hours a day he works (If at least one task is completed at a certain hour, then the user was working at that hour). In the time period between February and March

Upvotes: 0

Views: 1497

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Is this what you want?

select userid, count(distinct date_trunc('hour', datetime)) as hours worked,
       count(*) * 1.0 / count(distinct date_trunc('hour', datetime)) as avg_tasks_per_hour
from t
where datetime >= '2021-02-01' and datetime < '2021-04-01'
group by userid;

Upvotes: 1

Related Questions