<!-- https://learnsql.cpetoday.com/analyze/big-bear -->
# City government operations (bigbear)

`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`, database `bigbear`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** open [the database page](/databases/bigbear) 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_events` are derived from daily snowfall; storms drive `snow_operations`, storm overtime in `payroll_records`, plow breakdowns in `fleet_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_quarter` and `fiscal_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_year` shows 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_id` links them, and `payroll_records.department_id` is the department on the pay date.
- **Locations.** Parcels, assets, requests, calls, permits and cases carry `latitude`, `longitude` and `geocode_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:

```sql
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:

```sql
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?**

```sql
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?**

```sql
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:

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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:

```sql
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:

```sql
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?**

```sql
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:

```sql
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:

```sql
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.** `streets` has `from_latitude`/`from_longitude` and `to_latitude`/`to_longitude`, so a line layer can draw the network, colored by `last_pci`, `snow_priority` or `route_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, and `neighborhoods` give a roll-up level.
- **Fiscal periods.** Use `fiscal_year`, `fiscal_quarter` and `fiscal_period` on any fact table, or join `fiscal_calendar` for the budget phase, tourism season, snow season and holidays. See [Maps and fiscal calendars](/analyze/maps-and-fiscal).

## Excel and Power BI

- **Star model.** Use `service_requests`, `cad_calls`, `tot_filings`, `utility_bills`, `permits` and `gl_journal_lines` as fact tables, with `neighborhoods`, `parcels`, `departments`, `fiscal_calendar` and `weather_daily` as shared dimensions. `utility_bills` and `tot_filings` are 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.
