What were the most active institutions (e.g. users grouped by online aggregations) during a period of time?
Query
select i.name, count(oa.primary_institution_id)
from online_aggregations as oa
inner join institution as i
on i.institution_id = oa.primary_institution_id
where oa.date > (now() - interval '20 minutes')
group by i.name, oa.primary_institution_id
order by count(oa.primary_institution_id) desc
limit 10
;
You could also run this over an interval of time.
select i.name, count(oa.primary_institution_id)
from online_aggregations as oa
inner join institution as i
on i.institution_id = oa.primary_institution_id
where oa.date > (timestamp '2015-05-13 00:00:00.000-05')
and oa.date < (timestamp '2015-05-13 23:59:00.000-05')
group by i.name, oa.primary_institution_id
order by count(oa.primary_institution_id) desc
limit 10
;
Example Output
name | count
----------------------------+-------
Equity Bank | 61
Bank of Southside Virginia | 46
Grayson National Bank | 42
County National Bank | 33
Bank Forward | 32
Montecito Bank & Trust | 31
Autotruck Financial CU | 30
Generations Bank | 28
MBC/Foundation Bank | 27
PS Bank | 24
(10 rows)