Every measure, expanded.

Total count of citation rows (citation, violation grain). Multi-violation tickets count as multiple rows.

SELECT COUNT(*) AS citation_count
FROM main_gold.fct_citations

Citations issued during the day shift (8AM-4PM).

SELECT COUNT(*) AS count_by_day_shift
FROM main_gold.fct_citations

Citations issued during night shifts (4PM-midnight + midnight-8AM).

SELECT COUNT(*) AS count_by_night_shift
FROM main_gold.fct_citations

Average vehicle_mph across rows in the Speeding charge category. Excludes rows with NULL vehicle_mph (82.5% of total) -- those aren't speed-related.

SELECT AVG(case when charge_category like 'Speeding%' then vehicle_mph end) AS average_vehicle_speed_on_speed_violations
FROM main_gold.fct_citations

Number of distinct charge_category values observed.

SELECT COUNT(DISTINCT charge_category) AS count_distinct_violation_types
FROM main_gold.fct_citations

Total count of crime incidents.

SELECT COUNT(*) AS total_incidents
FROM main_gold.fct_crime_incidents

Crime incidents with a known Somerville ward (excludes the ~13% NULL-ward and 2 CAM rows).

SELECT COUNT(*) AS incidents_with_ward
FROM main_gold.fct_crime_incidents

Crime incidents with a known incident date (excludes the ~12.5% sensitive incidents).

SELECT COUNT(*) AS incidents_with_date
FROM main_gold.fct_crime_incidents

Total respondents across the queried grain. Note: at the city+wave+question grain this is the wave's respondent count for the question; at the city+wave grain (no question filter) it sums across questions and over-counts respondents.

SELECT SUM(respondent_count) AS total_respondents
FROM main_gold.fct_happiness_survey

Mean-of-means at the grain. Mathematically correct for trend-tracking when grouped by wave / geography / question, but NOT a respondent-level grand mean -- the per-respondent average score would require querying silver. Document this in trust-contract receipts when the analyst asks for a 'true average'.

SELECT AVG(mean_score) AS mean_score_overall
FROM main_gold.fct_happiness_survey

Standard survey 'top-two-box' -- share of respondents who rated 4 or 5 on the Likert scale, across the queried grain. Returns 0-1 (multiply by 100 for percent display).

SELECT CUSTOM(*) AS pct_top_two_box
FROM main_gold.fct_happiness_survey

Standard survey 'bottom-two-box' -- share of respondents who rated 1 or 2. Returns 0-1.

SELECT CUSTOM(*) AS pct_bottom_two_box
FROM main_gold.fct_happiness_survey

Total categories (always 4).

SELECT COUNT(*) AS category_count
FROM main_gold.dim_offense_category

Total distinct offense codes (always 39 today).

SELECT COUNT(*) AS code_count
FROM main_gold.dim_offense_code

Total count of permit applications (all statuses).

SELECT COUNT(*) AS permit_count
FROM main_gold.fct_permits

Count of permits actually issued (permit_status = 'Issued', 96.68% of rows).

SELECT COUNT(*) AS issued_permit_count
FROM main_gold.fct_permits

Sum of permit_amount across issued permits, in USD.

SELECT SUM(case when is_issued then permit_amount end) AS total_issued_permit_value
FROM main_gold.fct_permits

Average permit_amount across issued permits, in USD.

SELECT AVG(case when is_issued then permit_amount end) AS avg_issued_permit_value
FROM main_gold.fct_permits

Number of distinct property addresses with at least one permit.

SELECT COUNT(DISTINCT address) AS distinct_address_count
FROM main_gold.fct_permits

Total 311 requests

SELECT COUNT(*) AS total_requests
FROM main_gold.fct_311_requests

Requests not yet closed (status != 'Closed')

• status != 'Closed'

SELECT COUNT(*) AS open_requests
FROM main_gold.fct_311_requests
WHERE status != 'Closed'

Total (topic, year, value) observations.

SELECT COUNT(*) AS kpi_observation_count
FROM main_gold.fct_somerville_kpi

Distinct KPI topics tracked.

SELECT COUNT(DISTINCT topic) AS kpi_topic_count
FROM main_gold.fct_somerville_kpi

Distinct years observed across all topics.

SELECT COUNT(DISTINCT year) AS years_of_coverage
FROM main_gold.fct_somerville_kpi

Maximum `value` across the topic. For time-series topics with one observation per year (Population, Median Household Income, etc.), MAX surfaces the most recent observation when filtered to that topic. For categorical topics with multiple values per year, prefer querying the fact directly.

SELECT MAX(value) AS latest_value_for_topic
FROM main_gold.fct_somerville_kpi

Total surviving questions (always 8 today).

SELECT COUNT(*) AS question_count
FROM main_gold.dim_survey_question

Total waves in the survey (always 8 today).

SELECT COUNT(*) AS wave_count
FROM main_gold.dim_survey_wave

Sum of respondent_count across waves (12,583 today).

SELECT SUM(respondent_count) AS total_respondents_all_waves
FROM main_gold.dim_survey_wave

Total wards (always 7)

SELECT COUNT(*) AS ward_count
FROM main_gold.dim_ward