City government operations (bigbear)
A small resort city: 311 requests, snow operations, dispatch, permits and code enforcement, a water utility, the lodging tax, parcels and fund accounting, on a map and a July-June fiscal year.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Analyze 311 requests, snow operations and dispatch in SQL.
- Map parcels and permits, and use the July to June fiscal year.
- Work through the lodging tax and water utility tables.
- Build fund accounting summaries across the city's funds.
bigbear is municipal-style data: the operating records of a city, in the way a city manager, finance director or public works superintendent would see them. The city is Big Bear Lake, California, a small mountain resort city in the San Bernardino Mountains at 6,750 feet. About 75 employees run streets, a municipal water utility and development services; police and fire are contracted; and the economy is built on visitors, second homes and short-term rentals. It covers fiscal 2022 through 24 October 2026 (the first months of fiscal 2027) in 61 tables and about 586,000 rows.
It is built for local-government analytics: 311 requests, snow operations, dispatch, permits and code enforcement, a water utility, the lodging tax, property and parcels, fund accounting and council records, all on a map and on a July-June fiscal year.
These are invented records. The place, the seasons and the kind of work are real and recognizable, but every parcel, owner, employee, vendor, call, permit and dollar amount is made up. Neighborhoods and the lake road follow the real layout only approximately, street names are typical of the area but house numbers and lot lines are invented, and tax rates, fees, budgets and ballot measures are fictional. Use it for learning, not for navigation or for any question about a real property or the real city's finances.
Connect to it
- SQL Server: server
learnsql.cpetoday.com,1433, databasebigbear, tables in thedboschema. See Connect Excel and Power BI. - In the browser: open the database page for the Playground, the SQL editor and the schema explorer.
- Time: timestamps are local time. The as-of date is 2026-10-24.
One engine drives the city: visitors and weather
weather_daily has a synthetic mountain climate and a visitor_index for every day (1.0 is an average day). Winter weekends, powder days, the summer season and holiday weeks run high; a blizzard or a road closure drops it. Almost everything else follows it, so the data behaves like a real tourist town:
- Visitors fill the rentals (
tot_filings), generate noise, trash and parking complaints (service_requests), dispatch calls (cad_calls) and water use in rentals (utility_bills). - Snow runs the public works budget.
storm_eventsare derived from daily snowfall; storms drivesnow_operations, storm overtime inpayroll_records, plow breakdowns infleet_maintenance, snow-related 311 requests, slower dispatch response, estimated water meter reads (buried meter boxes) and the emergency declaration behind disaster reimbursement. - Freezes and thaws cause main breaks and bursts in empty second homes.
- Season drives potholes in spring and defensible-space work in summer.
Tables at a glance
| Area | Tables | What they hold |
|---|---|---|
| Geography and calendar | neighborhoods, zoning_districts, fiscal_calendar, geo_states, geo_places, geo_postal_codes |
12 neighborhoods in the city and 6 outside it, zoning, the July-June calendar with budget phases, tax installment dates and seasons |
| Organization | funds, departments, facilities |
10 funds, 13 departments (two merged during the period), 32 facilities with addresses and coordinates |
| People | job_classes, positions, employees, pay_periods, payroll_records |
Authorized positions, 174 employees over the years (including seasonal snow crews), biweekly payroll with overtime split into storm overtime |
| Governance | council_members, council_meetings, agenda_items, council_votes |
Five seats with turnover, about 137 meetings, agenda items and every member's vote |
| Finance | gl_accounts, gl_journal_entries, gl_journal_lines, budgets, vendors, invoices, grants, capital_projects |
A balanced fund ledger, adopted and amended budgets, accounts payable, 12 grants and 29 capital projects |
| Streets and property | streets, street_condition_surveys, snow_plow_routes, property_owners, parcels, parcel_assessments, property_transfers |
886 street segments with endpoints, pavement condition surveys, 24 plow routes, 8,643 parcels with owners, six years of assessed value and 2,000 transfers |
| Assets | asset_types, assets |
Hydrants, valves, streetlights, signals, drains, signs, restrooms and more, each with coordinates |
| Water utility | water_rates, utility_accounts, utility_bills |
Tiered rates by fiscal year, accounts that turn over when a home sells, 260,000 bimonthly bills |
| Weather and snow | weather_daily, storm_events, snow_operations, fleet_vehicles, fleet_maintenance |
Daily weather and visitors, 30 storms, plow shifts, the fleet |
| 311 and work orders | service_request_types, service_requests, work_orders |
18,600 requests with channel and location, and the work orders they create plus preventive programs |
| Dispatch | cad_call_types, cad_units, cad_calls |
30,000 Fire/EMS and Sheriff calls with every timestamp from receipt to clear |
| Community development | permit_types, permits, inspections, code_violation_types, code_cases |
5,800 permits, 27,000 inspections, code cases from 311, patrols and defensible-space inspections |
| Tourism economy | short_term_rentals, tot_operators, tot_filings, businesses, business_licenses, special_events |
1,400 rentals, lodging operators, 78,000 monthly tax filings, local businesses and licenses, festivals and parades |
dataset_info holds the as-of date and a one-line description. Nine views save you the common joins: vw_budget_vs_actual, vw_fund_balances, vw_response_times, vw_service_request_sla, vw_monthly_tot, vw_parcel_current, vw_permit_cycle_times, vw_water_billing_monthly and vw_storm_cost_summary.
How the tables join
neighborhoods ──< parcels >── zoning_districts property_owners ──< parcels ──< parcel_assessments
│ └──< property_transfers
streets ──< parcels streets ──< assets >── asset_types snow_plow_routes ──< streets
│
parcels ──< utility_accounts ──< utility_bills
parcels ──< short_term_rentals ──< tot_operators ──< tot_filings businesses ──< tot_operators, business_licenses
parcels ──< permits ──< inspections parcels ──< code_cases ──< inspections violation types ──< code_cases
service_request_types ──< service_requests ──< work_orders >── assets service_requests ──< code_cases
cad_call_types ──< cad_calls >── cad_units storm_events ──< snow_operations, service_requests, work_orders
funds, departments, gl_accounts ──< gl_journal_lines >── gl_journal_entries vendors ──< invoices >── capital_projects >── grants
council_meetings ──< agenda_items ──< council_votes >── council_members
employees ──< payroll_records >── pay_periods positions ──< employees fiscal_calendar joins on any date
Rules built into the data
- Fiscal year. July 1 to June 30, named by the year it ends: FY2026 is July 2025 to June 2026 and fiscal period 1 is July. Fact tables carry
fiscal_year,fiscal_quarterandfiscal_period. - The ledger reconciles. TOT revenue equals the TOT payments received, water sales equal the bills issued, permit and license revenue equal the fees paid, salaries and overtime equal payroll, and grant revenue equals
grants.reimbursed_amount. Every journal entry belongs to one fund and balances; transfers between funds are a pair of entries. - Property tax follows the roll. Each parcel's assessed value grows 2 percent a year until a reassessing transfer resets it to the sale price (Proposition 13 style).
base_yearshows when it was last reset. - One department history, two names. Parks and Facilities merged into Public Works on 1 July 2024 and Building and Safety into Community Development on 1 July 2023.
departments.successor_department_idlinks them, andpayroll_records.department_idis the department on the pay date. - Locations. Parcels, assets, requests, calls, permits and cases carry
latitude,longitudeandgeocode_quality(rooftop,gps,street,address_match,neighborhood_centroid,failed). About a third of dispatch calls are outside the city limits (jurisdiction,neighborhoods.is_in_city).
Things that happen in the data
- A historic winter. FY2023 is modeled as exceptionally heavy and FY2022 as dry. The largest storm, in February 2023, drops over 100 inches, closes roads, drives record overtime and 311 volume, and triggers an emergency declaration and a state reimbursement grant.
- A rate increase. The TOT rate rises from 10 to 12 percent on 1 January 2025 (a modeled ballot measure).
- A drought surcharge on water bills from June 2022 to April 2023, and a smart-meter rollout from late 2023 to spring 2026 that changes how reads are taken.
- A permit backlog in spring and summer 2023 as storm repairs flood the counter, and a council fight over short-term rental rules in 2022 to 2026.
- Mergers and turnover: the two department mergers, a council seat filled by appointment, and an employee base that turns over about 9 percent a year.
Questions to try
Does a busier town mean more dispatch calls? Group days by the visitor index:
SELECT CASE WHEN w.visitor_index < 0.7 THEN '1 quiet (under 0.7)' WHEN w.visitor_index < 1.3 THEN '2 normal' ELSE '3 busy (1.3 and up)' END AS day_type,
COUNT(*) AS days, ROUND(AVG(c.calls * 1.0), 1) AS avg_dispatch_calls, ROUND(AVG(w.visitor_index), 2) AS avg_visitor_index
FROM (SELECT CAST(received_at AS DATE) AS call_date, COUNT(*) AS calls FROM cad_calls GROUP BY CAST(received_at AS DATE)) c
JOIN weather_daily w ON w.weather_date = c.call_date
GROUP BY CASE WHEN w.visitor_index < 0.7 THEN '1 quiet (under 0.7)' WHEN w.visitor_index < 1.3 THEN '2 normal' ELSE '3 busy (1.3 and up)' END
ORDER BY day_type;
Busy days see roughly twice the calls of quiet days.
What does the lodging tax look like month by month? The view already rolls up filings:
SELECT period_month, SUM(filings) AS filings, ROUND(SUM(tax_due), 0) AS tax_due,
ROUND(SUM(occupied_nights) * 100.0 / SUM(available_nights), 1) AS occupancy_pct
FROM vw_monthly_tot
WHERE fiscal_year = 2026
GROUP BY period_month
ORDER BY period_month;
The winter holidays and the summer peak stand out; April and the fall are the troughs.
Where does the General Fund's money come from?
SELECT a.account_number, a.account_name, ROUND(SUM(l.credit - l.debit), 0) AS revenue FROM gl_journal_lines l JOIN gl_journal_entries e ON e.entry_id = l.entry_id JOIN gl_accounts a ON a.account_id = l.account_id WHERE e.fiscal_year = 2026 AND l.fund_id = 1 AND a.account_type = 'Revenue' AND a.account_number <> '4900' GROUP BY a.account_number, a.account_name ORDER BY revenue DESC;
The lodging tax is the largest single source, ahead of property tax and sales tax.
Did departments stay inside their budgets?
SELECT department_name, ROUND(SUM(adopted_amount), 0) AS adopted, ROUND(SUM(amended_amount), 0) AS amended, ROUND(SUM(actual_amount), 0) AS actual,
ROUND(100.0 * SUM(actual_amount) / SUM(amended_amount), 1) AS pct_of_amended
FROM vw_budget_vs_actual
WHERE fiscal_year = 2026 AND fund_code = '001' AND account_type = 'Expense'
GROUP BY department_name
ORDER BY actual DESC;
Who owns the homes? Share of owner-occupied and absentee owners by neighborhood, with the active rentals:
SELECT n.neighborhood_name, COUNT(*) AS homes,
ROUND(100.0 * SUM(CASE WHEN p.is_owner_occupied = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS owner_occupied_pct,
ROUND(100.0 * SUM(CASE WHEN o.is_local = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS absentee_owner_pct,
SUM(CASE WHEN s.str_id IS NOT NULL THEN 1 ELSE 0 END) AS short_term_rentals
FROM parcels p
JOIN neighborhoods n ON n.neighborhood_id = p.neighborhood_id
JOIN property_owners o ON o.owner_id = p.owner_id
LEFT JOIN short_term_rentals s ON s.parcel_id = p.parcel_id AND s.status = 'Active'
WHERE p.land_use IN ('Single Family Residence', 'Condominium', 'Townhome')
GROUP BY n.neighborhood_name
ORDER BY absentee_owner_pct DESC;
Resort areas such as Moonridge and the lakefront are mostly absentee; the residential hillsides are the most local.
Where do the owners live?
SELECT o.city AS owner_city, o.state AS owner_state, COUNT(*) AS parcels, ROUND(SUM(a.net_assessed_value) / 1000000.0, 1) AS assessed_value_millions FROM parcels p JOIN property_owners o ON o.owner_id = p.owner_id JOIN parcel_assessments a ON a.parcel_id = p.parcel_id AND a.fiscal_year = 2026 WHERE o.is_local = 0 GROUP BY o.city, o.state ORDER BY parcels DESC OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;
What did each big storm cost?
SELECT storm_name, severity, total_snowfall_in, plow_shifts, ROUND(overtime_hours, 0) AS overtime_hours, ROUND(abrasive_tons, 0) AS abrasive_tons,
work_orders, ROUND(work_order_cost, 0) AS work_order_cost, service_requests
FROM vw_storm_cost_summary
ORDER BY total_snowfall_in DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
How much of public works overtime is storm overtime?
SELECT d.department_name, p.fiscal_year, ROUND(SUM(p.overtime_pay), 0) AS overtime_pay,
ROUND(100.0 * SUM(p.storm_overtime_hours) / NULLIF(SUM(p.hours_overtime), 0), 1) AS storm_share_pct
FROM payroll_records p
JOIN departments d ON d.department_id = p.department_id
WHERE d.department_code IN ('PW', 'DWP', 'PRK')
GROUP BY d.department_name, p.fiscal_year
ORDER BY d.department_name, p.fiscal_year;
How fast do police and fire respond, and what does snow do to it?
SELECT agency, CASE WHEN weather_snowfall_in >= 4 THEN 'snow day (4+ in)' ELSE 'otherwise' END AS conditions, COUNT(*) AS calls,
ROUND(AVG(response_seconds) / 60.0, 1) AS avg_response_min,
ROUND(100.0 * SUM(CASE WHEN response_seconds <= 480 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_8_min
FROM vw_response_times
WHERE priority <= 2 AND response_seconds IS NOT NULL AND jurisdiction = 'City of Big Bear Lake'
GROUP BY agency, CASE WHEN weather_snowfall_in >= 4 THEN 'snow day (4+ in)' ELSE 'otherwise' END
ORDER BY agency, conditions;
Which request types meet their service level?
SELECT category, COUNT(*) AS requests, ROUND(100.0 * SUM(met_sla) / COUNT(*), 1) AS pct_met_sla, ROUND(AVG(hours_to_close), 1) AS avg_hours_to_close FROM vw_service_request_sla WHERE fiscal_year = 2026 AND hours_to_close IS NOT NULL GROUP BY category ORDER BY requests DESC;
Which short-term rentals draw the most noise complaints?
SELECT s.str_permit_number, p.address_line1, n.neighborhood_name, s.max_occupancy, COUNT(*) AS noise_complaints FROM service_requests q JOIN service_request_types t ON t.type_id = q.type_id JOIN parcels p ON p.parcel_id = q.parcel_id JOIN neighborhoods n ON n.neighborhood_id = p.neighborhood_id JOIN short_term_rentals s ON s.parcel_id = p.parcel_id WHERE t.type_name LIKE 'Noise complaint%' AND q.status <> 'Duplicate' GROUP BY s.str_permit_number, p.address_line1, n.neighborhood_name, s.max_occupancy ORDER BY noise_complaints DESC OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;
How long do permits take compared with the target?
SELECT type_name, COUNT(*) AS permits, ROUND(AVG(review_days * 1.0), 1) AS avg_review_days, MAX(review_days_target) AS target_days,
ROUND(100.0 * SUM(CASE WHEN review_days <= review_days_target THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_on_target
FROM vw_permit_cycle_times
WHERE review_days IS NOT NULL AND fiscal_year IN (2024, 2025, 2026)
GROUP BY type_name
ORDER BY permits DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;
Which neighborhoods have the worst roads? Length-weighted pavement condition (0 to 100) from the latest survey:
SELECT n.neighborhood_name, COUNT(*) AS segments, ROUND(SUM(s.length_ft) / 5280.0, 1) AS miles,
ROUND(SUM(s.last_pci * 1.0 * s.length_ft) / SUM(s.length_ft), 1) AS length_weighted_pci
FROM streets s
JOIN neighborhoods n ON n.neighborhood_id = s.neighborhood_id
WHERE s.maintained_by = 'City' AND s.last_pci IS NOT NULL
GROUP BY n.neighborhood_name
ORDER BY length_weighted_pci;
Did the snow cause estimated water reads? Buried meter boxes after the big storms show up as estimates:
SELECT YEAR(bill_date) AS yr, MONTH(bill_date) AS mo, COUNT(*) AS bills,
ROUND(100.0 * SUM(CASE WHEN read_type = 'Estimated' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_estimated
FROM utility_bills
WHERE bill_date >= '2023-01-01' AND bill_date < '2023-08-01'
GROUP BY YEAR(bill_date), MONTH(bill_date)
ORDER BY yr, mo;
Estimates jump in the January and March 2023 billing cycles, right after the big storms, and fall back to the usual 2 percent in the cycles between.
Which agenda items split the council?
SELECT i.title, m.meeting_date, SUM(CASE WHEN v.vote = 'Yes' THEN 1 ELSE 0 END) AS yes_votes, SUM(CASE WHEN v.vote = 'No' THEN 1 ELSE 0 END) AS no_votes, i.outcome FROM agenda_items i JOIN council_meetings m ON m.meeting_id = i.meeting_id JOIN council_votes v ON v.agenda_item_id = i.agenda_item_id GROUP BY i.agenda_item_id, i.title, m.meeting_date, i.outcome HAVING SUM(CASE WHEN v.vote = 'No' THEN 1 ELSE 0 END) >= 2 ORDER BY m.meeting_date DESC OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
When are property tax payments due? The fiscal calendar carries the installment and apportionment dates:
SELECT calendar_date, fiscal_period_name, tax_installment, budget_phase FROM fiscal_calendar WHERE fiscal_year = 2026 AND tax_installment IS NOT NULL ORDER BY calendar_date;
All of these run on SQL Server and MariaDB.
Maps and fiscal periods
- Bin the complaints. Round the coordinates and group to get a heat map of where noise complaints concentrate:
SELECT ROUND(latitude, 3) AS lat, ROUND(longitude, 3) AS lon, COUNT(*) AS complaints FROM service_requests q JOIN service_request_types t ON t.type_id = q.type_id WHERE t.type_name LIKE 'Noise complaint%' AND q.latitude IS NOT NULL AND q.fiscal_year = 2026 GROUP BY ROUND(latitude, 3), ROUND(longitude, 3) ORDER BY complaints DESC OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
- Map the streets.
streetshasfrom_latitude/from_longitudeandto_latitude/to_longitude, so a line layer can draw the network, colored bylast_pci,snow_priorityorroute_id. - Points of every kind. Parcels (colored by
land_use, owner origin or assessed value), assets, 311 requests, dispatch calls, permits, code cases and capital projects all carry latitude and longitude, andneighborhoodsgive a roll-up level. - Fiscal periods. Use
fiscal_year,fiscal_quarterandfiscal_periodon any fact table, or joinfiscal_calendarfor the budget phase, tourism season, snow season and holidays. See Maps and fiscal calendars.
Excel and Power BI
- Star model. Use
service_requests,cad_calls,tot_filings,utility_bills,permitsandgl_journal_linesas fact tables, withneighborhoods,parcels,departments,fiscal_calendarandweather_dailyas shared dimensions.utility_billsandtot_filingsare large, so load them with a filter or use the views. - Measures to write. TOT collected, Occupancy %, Requests, Percent within SLA, Average response minutes, Overtime pay, Budget variance, Net assessed value, Absentee owner %, Water billed and collected, Pavement condition.
- Map visuals. Set latitude and longitude as such, size bubbles by a measure, and filter on fiscal year. For choropleths by neighborhood, use the neighborhood centers or bring your own boundary shapes.
Realistic imperfections
Government data is messy, and so is this: duplicate 311 requests (duplicate_of_request_id), requests with no coordinates or a parcel that never matched, mixed-case free-text addresses, requests that were never closed, request types that still point at a department that has merged away, vendors entered twice with different spellings, rentals that file late or never, audit adjustments, permits that expire or are withdrawn, inspections cancelled by snow, estimated water reads, written-off water bills and cases that stay open.
Try it on the data
Open the bigbear database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.