54 lines
1.2 KiB
SQL
54 lines
1.2 KiB
SQL
-- Aggregate-only starter queries. Do not select VIN, plate, email, session,
|
|
-- upload-log, or raw JSON fields into a public-facing application.
|
|
BEGIN TRANSACTION READ ONLY;
|
|
|
|
SELECT
|
|
count(*) AS registration_records,
|
|
min(registration_date) AS earliest_registration,
|
|
max(registration_date) AS latest_registration
|
|
FROM data.dmv_tax;
|
|
|
|
SELECT
|
|
extract(year FROM registration_date)::integer AS registration_year,
|
|
count(*) AS records
|
|
FROM data.dmv_tax
|
|
GROUP BY 1
|
|
ORDER BY 1;
|
|
|
|
SELECT
|
|
upper(trim(county)) AS county,
|
|
count(*) AS records
|
|
FROM data.dmv_tax
|
|
GROUP BY 1
|
|
ORDER BY 2 DESC;
|
|
|
|
SELECT
|
|
upper(replace(trim(fuel_type), '-', ' ')) AS canonical_fuel_type,
|
|
count(*) AS records
|
|
FROM data.dmv_tax_vehicle
|
|
GROUP BY 1
|
|
ORDER BY 2 DESC;
|
|
|
|
SELECT
|
|
count(*) AS inspection_records,
|
|
min(test_start) AS earliest_test,
|
|
max(test_start) AS latest_test
|
|
FROM data.inspection_search;
|
|
|
|
SELECT
|
|
extract(year FROM test_start)::integer AS test_year,
|
|
count(*) AS records
|
|
FROM data.inspection_search
|
|
GROUP BY 1
|
|
ORDER BY 1;
|
|
|
|
SELECT
|
|
lower(trim(county)) AS county_source,
|
|
coalesce(nullif(upper(trim(overall_result)), ''), '<BLANK/NULL>') AS result,
|
|
count(*) AS records
|
|
FROM data.inspection_search
|
|
GROUP BY 1, 2
|
|
ORDER BY 1, 3 DESC;
|
|
|
|
ROLLBACK;
|