branch_30_day_circ_view (view)
42 rows
This data as json, CSV (advanced)
| branch_name | circulations | total | latitude | longitude |
|---|---|---|---|---|
| Main Library | {
"check-in": 33586,
"check-out": 22608
} |
56194 | 39.1049812 | -84.51326590000001 |
| Anderson | {
"check-in": 24039,
"check-out": 24782
} |
48821 | 39.0850447 | -84.35213569999999 |
| Symmes Township | {
"check-in": 19343,
"check-out": 23664
} |
43007 | 39.2844536 | -84.3090844 |
| Blue Ash | {
"check-in": 18224,
"check-out": 22326
} |
40550 | 39.2299695 | -84.3743428 |
| Madeira | {
"check-in": 17115,
"check-out": 19314
} |
36429 | 39.1914395 | -84.3624626 |
| Green Township | {
"check-in": 15660,
"check-out": 17094
} |
32754 | 39.1520553 | -84.66270949999999 |
| Delhi Township | {
"check-in": 14207,
"check-out": 16811
} |
31018 | 39.1034539 | -84.6108879 |
| Outreach Services | {
"check-in": 6754,
"check-out": 24137
} |
30891 | 39.1049812 | -84.51326590000001 |
| Groesbeck | {
"check-in": 14319,
"check-out": 15557
} |
29876 | 39.2203135 | -84.58353009999999 |
| Harrison | {
"check-in": 13456,
"check-out": 14836
} |
28292 | 39.2624563 | -84.78748209999999 |
| Loveland | {
"check-in": 12857,
"check-out": 14279
} |
27136 | 39.2637373 | -84.2680182 |
| Deer Park | {
"check-in": 12169,
"check-out": 14037
} |
26206 | 39.2095778 | -84.40206359999999 |
| Monfort Heights | {
"check-in": 11999,
"check-out": 13309
} |
25308 | 39.1889712 | -84.6090983 |
| Hyde Park | {
"check-in": 11173,
"check-out": 12368
} |
23541 | 39.1391448 | -84.4411168 |
| Sharonville | {
"check-in": 10485,
"check-out": 11937
} |
22422 | 39.2676468 | -84.4037649 |
| Mariemont | {
"check-in": 9521,
"check-out": 11871
} |
21392 | 39.1448375 | -84.36792059999999 |
| Wyoming | {
"check-in": 7886,
"check-out": 8646
} |
16532 | 39.2275521 | -84.4722743 |
| Pleasant Ridge | {
"check-in": 7494,
"check-out": 8034
} |
15528 | 39.1843004 | -84.4240311 |
| Oakley | {
"check-in": 7205,
"check-out": 7661
} |
14866 | 39.1524128 | -84.43043689999999 |
| Forest Park | {
"check-in": 6973,
"check-out": 7460
} |
14433 | 39.2750556 | -84.5003625 |
| Clifton | {
"check-in": 6500,
"check-out": 6882
} |
13382 | 39.1424731 | -84.51488660000001 |
| Mt Washington | {
"check-in": 6239,
"check-out": 6679
} |
12918 | 39.0913108 | -84.38709899999999 |
| North Central | {
"check-in": 5753,
"check-out": 6209
} |
11962 | 39.28130489999999 | -84.56505369999999 |
| Westwood | {
"check-in": 5380,
"check-out": 5881
} |
11261 | 39.1481341 | -84.6002968 |
| Norwood | {
"check-in": 5529,
"check-out": 5715
} |
11244 | 39.1566243 | -84.4593111 |
| Reading | {
"check-in": 4725,
"check-out": 5732
} |
10457 | 39.2185522 | -84.44323519999999 |
| Northside | {
"check-in": 4024,
"check-out": 6366
} |
10390 | 39.1637991 | -84.5405866 |
| Mt Healthy | {
"check-in": 3837,
"check-out": 4587
} |
8424 | 39.23290859999999 | -84.5478699 |
| Walnut Hills | {
"check-in": 3698,
"check-out": 4613
} |
8311 | 39.127104 | -84.4880891 |
| College Hill | {
"check-in": 3210,
"check-out": 4791
} |
8001 | 39.2030098 | -84.541804 |
| Covedale | {
"check-in": 3762,
"check-out": 4186
} |
7948 | 39.1211059 | -84.60069779999999 |
| Miami Township | {
"check-in": 3676,
"check-out": 4105
} |
7781 | 39.1626036 | -84.74893949999999 |
| Madisonville | {
"check-in": 2995,
"check-out": 3333
} |
6328 | 39.1593732 | -84.3929306 |
| St Bernard | {
"check-in": 2945,
"check-out": 3188
} |
6133 | 39.168807 | -84.49703439999999 |
| Price Hill | {
"check-in": 2478,
"check-out": 3003
} |
5481 | 39.1093854 | -84.5638238 |
| Greenhills | {
"check-in": 2437,
"check-out": 2760
} |
5197 | 39.2676488 | -84.5204127 |
| Corryville | {
"check-in": 2318,
"check-out": 2076
} |
4394 | 39.1318922 | -84.5088234 |
| Avondale | {
"check-in": 1721,
"check-out": 2613
} |
4334 | 39.1466064 | -84.4890727 |
| Bond Hill | {
"check-in": 1937,
"check-out": 2374
} |
4311 | 39.185262 | -84.4635199 |
| Elmwood Place | {
"check-in": 983,
"check-out": 2214
} |
3197 | 39.1870067 | -84.48762339999999 |
| Distribution Center | {
"check-in": 782,
"check-out": 2141
} |
2923 | 39.1174333 | -84.5313914 |
| West End | {
"check-in": 1074,
"check-out": 1165
} |
2239 | 39.1091183 | -84.52807589999999 |
Advanced export
JSON shape: default, array, newline-delimited
CREATE VIEW branch_30_day_circ_view AS with circ_data as (
select
c.branch_name,
c.branch_code_num,
case
c.op_code
when 'o' then 'check-out'
when 'i' then 'check-in'
end as circ_type,
sum(c.count_op_code) as sum_circ_type,
d.target_date as checkouts_since
from
circ_agg as c,
(
select
date(julianday(max(transaction_day)) - 30.0) as target_date
from
circ_agg
) as d
where
transaction_day > d.target_date
and op_code in ('o', 'i')
group by
1,
2,
3
)
select
branch_name,
json_group_object(circ_type, sum_circ_type) as circulations,
sum(sum_circ_type) as total,
-- checkouts_since, - it's last 30 days, so i don't think we need this
-- chpl_branch_location_name, code_num, address
latitude,
longitude
from
circ_data as c
join branch_locations as l on l.code_num = c.branch_code_num
group by
branch_name,
latitude,
longitude
order by
sum(sum_circ_type) desc;