branch_30_day_circ_view (view)
42 rows
This data as json, CSV (advanced)
| branch_name | circulations | total | latitude | longitude |
|---|---|---|---|---|
| Main Library | {
"check-in": 34686,
"check-out": 22755
} |
57441 | 39.1049812 | -84.51326590000001 |
| Anderson | {
"check-in": 23167,
"check-out": 23753
} |
46920 | 39.0850447 | -84.35213569999999 |
| Symmes Township | {
"check-in": 18479,
"check-out": 22100
} |
40579 | 39.2844536 | -84.3090844 |
| Blue Ash | {
"check-in": 17761,
"check-out": 20319
} |
38080 | 39.2299695 | -84.3743428 |
| Madeira | {
"check-in": 16714,
"check-out": 18375
} |
35089 | 39.1914395 | -84.3624626 |
| Green Township | {
"check-in": 15372,
"check-out": 16779
} |
32151 | 39.1520553 | -84.66270949999999 |
| Outreach Services | {
"check-in": 6122,
"check-out": 24497
} |
30619 | 39.1049812 | -84.51326590000001 |
| Delhi Township | {
"check-in": 14232,
"check-out": 15968
} |
30200 | 39.1034539 | -84.6108879 |
| Distribution Center | {
"check-in": 27899,
"check-out": 1686
} |
29585 | 39.1174333 | -84.5313914 |
| Groesbeck | {
"check-in": 14010,
"check-out": 15232
} |
29242 | 39.2203135 | -84.58353009999999 |
| Harrison | {
"check-in": 13009,
"check-out": 13953
} |
26962 | 39.2624563 | -84.78748209999999 |
| Deer Park | {
"check-in": 12591,
"check-out": 13910
} |
26501 | 39.2095778 | -84.40206359999999 |
| Loveland | {
"check-in": 12590,
"check-out": 13698
} |
26288 | 39.2637373 | -84.2680182 |
| Monfort Heights | {
"check-in": 11355,
"check-out": 13300
} |
24655 | 39.1889712 | -84.6090983 |
| Hyde Park | {
"check-in": 10843,
"check-out": 12252
} |
23095 | 39.1391448 | -84.4411168 |
| Sharonville | {
"check-in": 10556,
"check-out": 11837
} |
22393 | 39.2676468 | -84.4037649 |
| Mariemont | {
"check-in": 9203,
"check-out": 11203
} |
20406 | 39.1448375 | -84.36792059999999 |
| Wyoming | {
"check-in": 7793,
"check-out": 8897
} |
16690 | 39.2275521 | -84.4722743 |
| Pleasant Ridge | {
"check-in": 7256,
"check-out": 7748
} |
15004 | 39.1843004 | -84.4240311 |
| Forest Park | {
"check-in": 7013,
"check-out": 7472
} |
14485 | 39.2750556 | -84.5003625 |
| Oakley | {
"check-in": 6893,
"check-out": 7127
} |
14020 | 39.1524128 | -84.43043689999999 |
| Clifton | {
"check-in": 6375,
"check-out": 6883
} |
13258 | 39.1424731 | -84.51488660000001 |
| Mt Washington | {
"check-in": 6180,
"check-out": 6589
} |
12769 | 39.0913108 | -84.38709899999999 |
| North Central | {
"check-in": 5782,
"check-out": 6133
} |
11915 | 39.28130489999999 | -84.56505369999999 |
| Norwood | {
"check-in": 5518,
"check-out": 5667
} |
11185 | 39.1566243 | -84.4593111 |
| Westwood | {
"check-in": 5362,
"check-out": 5671
} |
11033 | 39.1481341 | -84.6002968 |
| Northside | {
"check-in": 3968,
"check-out": 5898
} |
9866 | 39.1637991 | -84.5405866 |
| Reading | {
"check-in": 4434,
"check-out": 5315
} |
9749 | 39.2185522 | -84.44323519999999 |
| Walnut Hills | {
"check-in": 3701,
"check-out": 4838
} |
8539 | 39.127104 | -84.4880891 |
| Mt Healthy | {
"check-in": 3956,
"check-out": 4466
} |
8422 | 39.23290859999999 | -84.5478699 |
| Covedale | {
"check-in": 3576,
"check-out": 4591
} |
8167 | 39.1211059 | -84.60069779999999 |
| College Hill | {
"check-in": 3197,
"check-out": 4417
} |
7614 | 39.2030098 | -84.541804 |
| Miami Township | {
"check-in": 3216,
"check-out": 4042
} |
7258 | 39.1626036 | -84.74893949999999 |
| Madisonville | {
"check-in": 2905,
"check-out": 3169
} |
6074 | 39.1593732 | -84.3929306 |
| St Bernard | {
"check-in": 2836,
"check-out": 3039
} |
5875 | 39.168807 | -84.49703439999999 |
| Greenhills | {
"check-in": 2502,
"check-out": 3063
} |
5565 | 39.2676488 | -84.5204127 |
| Bond Hill | {
"check-in": 2313,
"check-out": 2967
} |
5280 | 39.185262 | -84.4635199 |
| Price Hill | {
"check-in": 2451,
"check-out": 2776
} |
5227 | 39.1093854 | -84.5638238 |
| Avondale | {
"check-in": 1495,
"check-out": 2738
} |
4233 | 39.1466064 | -84.4890727 |
| Corryville | {
"check-in": 2152,
"check-out": 1961
} |
4113 | 39.1318922 | -84.5088234 |
| Elmwood Place | {
"check-in": 937,
"check-out": 2217
} |
3154 | 39.1870067 | -84.48762339999999 |
| West End | {
"check-in": 1332,
"check-out": 1503
} |
2835 | 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;