branch_30_day_circ_view (view)
42 rows
This data as json, CSV (advanced)
| branch_name | circulations | total | latitude | longitude |
|---|---|---|---|---|
| Main Library | {
"check-in": 36401,
"check-out": 23065
} |
59466 | 39.1049812 | -84.51326590000001 |
| Anderson | {
"check-in": 23401,
"check-out": 24847
} |
48248 | 39.0850447 | -84.35213569999999 |
| Symmes Township | {
"check-in": 19174,
"check-out": 23331
} |
42505 | 39.2844536 | -84.3090844 |
| Blue Ash | {
"check-in": 18221,
"check-out": 20994
} |
39215 | 39.2299695 | -84.3743428 |
| Madeira | {
"check-in": 17326,
"check-out": 19577
} |
36903 | 39.1914395 | -84.3624626 |
| Outreach Services | {
"check-in": 6446,
"check-out": 26671
} |
33117 | 39.1049812 | -84.51326590000001 |
| Green Township | {
"check-in": 15587,
"check-out": 17066
} |
32653 | 39.1520553 | -84.66270949999999 |
| Delhi Township | {
"check-in": 14410,
"check-out": 16667
} |
31077 | 39.1034539 | -84.6108879 |
| Groesbeck | {
"check-in": 14505,
"check-out": 16078
} |
30583 | 39.2203135 | -84.58353009999999 |
| Distribution Center | {
"check-in": 28753,
"check-out": 1735
} |
30488 | 39.1174333 | -84.5313914 |
| Harrison | {
"check-in": 13194,
"check-out": 14421
} |
27615 | 39.2624563 | -84.78748209999999 |
| Loveland | {
"check-in": 12988,
"check-out": 14275
} |
27263 | 39.2637373 | -84.2680182 |
| Deer Park | {
"check-in": 12445,
"check-out": 13717
} |
26162 | 39.2095778 | -84.40206359999999 |
| Monfort Heights | {
"check-in": 11357,
"check-out": 13676
} |
25033 | 39.1889712 | -84.6090983 |
| Hyde Park | {
"check-in": 10843,
"check-out": 12423
} |
23266 | 39.1391448 | -84.4411168 |
| Sharonville | {
"check-in": 10612,
"check-out": 12181
} |
22793 | 39.2676468 | -84.4037649 |
| Mariemont | {
"check-in": 9428,
"check-out": 11705
} |
21133 | 39.1448375 | -84.36792059999999 |
| Wyoming | {
"check-in": 7836,
"check-out": 9005
} |
16841 | 39.2275521 | -84.4722743 |
| Pleasant Ridge | {
"check-in": 7206,
"check-out": 8004
} |
15210 | 39.1843004 | -84.4240311 |
| Forest Park | {
"check-in": 7075,
"check-out": 7663
} |
14738 | 39.2750556 | -84.5003625 |
| Oakley | {
"check-in": 6952,
"check-out": 7407
} |
14359 | 39.1524128 | -84.43043689999999 |
| Mt Washington | {
"check-in": 6580,
"check-out": 6994
} |
13574 | 39.0913108 | -84.38709899999999 |
| Clifton | {
"check-in": 6492,
"check-out": 7069
} |
13561 | 39.1424731 | -84.51488660000001 |
| North Central | {
"check-in": 5968,
"check-out": 6231
} |
12199 | 39.28130489999999 | -84.56505369999999 |
| Norwood | {
"check-in": 5491,
"check-out": 5777
} |
11268 | 39.1566243 | -84.4593111 |
| Westwood | {
"check-in": 5425,
"check-out": 5817
} |
11242 | 39.1481341 | -84.6002968 |
| Northside | {
"check-in": 4186,
"check-out": 6215
} |
10401 | 39.1637991 | -84.5405866 |
| Reading | {
"check-in": 4427,
"check-out": 5361
} |
9788 | 39.2185522 | -84.44323519999999 |
| Walnut Hills | {
"check-in": 3764,
"check-out": 5159
} |
8923 | 39.127104 | -84.4880891 |
| Mt Healthy | {
"check-in": 4079,
"check-out": 4667
} |
8746 | 39.23290859999999 | -84.5478699 |
| Covedale | {
"check-in": 3707,
"check-out": 4611
} |
8318 | 39.1211059 | -84.60069779999999 |
| College Hill | {
"check-in": 3184,
"check-out": 4387
} |
7571 | 39.2030098 | -84.541804 |
| Miami Township | {
"check-in": 3247,
"check-out": 4146
} |
7393 | 39.1626036 | -84.74893949999999 |
| Madisonville | {
"check-in": 2850,
"check-out": 3174
} |
6024 | 39.1593732 | -84.3929306 |
| St Bernard | {
"check-in": 2706,
"check-out": 3042
} |
5748 | 39.168807 | -84.49703439999999 |
| Bond Hill | {
"check-in": 2467,
"check-out": 3219
} |
5686 | 39.185262 | -84.4635199 |
| Price Hill | {
"check-in": 2599,
"check-out": 2987
} |
5586 | 39.1093854 | -84.5638238 |
| Greenhills | {
"check-in": 2415,
"check-out": 3047
} |
5462 | 39.2676488 | -84.5204127 |
| Avondale | {
"check-in": 1410,
"check-out": 3005
} |
4415 | 39.1466064 | -84.4890727 |
| Corryville | {
"check-in": 2163,
"check-out": 1968
} |
4131 | 39.1318922 | -84.5088234 |
| Elmwood Place | {
"check-in": 1057,
"check-out": 2329
} |
3386 | 39.1870067 | -84.48762339999999 |
| West End | {
"check-in": 1377,
"check-out": 1595
} |
2972 | 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;