...
SELECT
h.a as hour,
count(distinct userid) as users,
sum((duration+500)/1000) as seconds,
count(*)-1 as requests
FROM weave.wv_statistics
FULL JOIN (select unnest((array[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23])) as a) h ON h.a = date_part('hour',submitted)
group by 1
order by 1
Join with other tables
Customer
SELECT customers.name, sum((duration+500)/1000) as seconds, count(*)
FROM weave.wv_statistics
join ikartanadmin.users on wv_statistics.userid = users.userid
join ikartanadmin.customers on users.customerlink = customers.id
where request = 'com.cohga.ExecuteReport'
group by 1 order by 3 desc;
Customer and day
SELECT customers.name, date(submitted), sum((duration+500)/1000) as seconds, count(*)
FROM weave.wv_statistics
join ikartanadmin.users on wv_statistics.userid = users.userid
join ikartanadmin.customers on users.customerlink = customers.id
group by 1,2 order by 2,1;
Customer and user name per day
SELECT customers.name, (users.firstname || ' ' || users.lastname) as name, date_part('day',submitted), sum((duration+500)/1000) as seconds, count(*)
FROM weave.wv_statistics
join ikartanadmin.users on wv_statistics.userid = users.userid
join ikartanadmin.customers on users.customerlink = customers.id
group by 1,2,3 order by 3,1;