Skip to content

Instantly share code, notes, and snippets.

View bschoenfeld's full-sized avatar

Ben Schoenfeld bschoenfeld

40 followers · 4 following
  • Port Solution Integrators
  • Portsmouth, VA
View GitHub Profile
@bschoenfeld
bschoenfeld / find_duplicates.sql
Created April 15, 2019 01:55
SELECT count(*), fips, "CaseNumber", max(details_fetched_for_hearing_date)
FROM public."DistrictCriminalCase"
GROUP BY fips, "CaseNumber"
HAVING count(*) > 1
ORDER BY count(*) DESC
@bschoenfeld
bschoenfeld / delete_duplicates.sql
Last active April 15, 2019 01:56
DELETE FROM "DistrictCriminalCase" a
USING "DistrictCriminalCase" b
WHERE
a.id > b.id
AND a.fips = b.fips
AND a."CaseNumber" = b."CaseNumber";
@bschoenfeld
bschoenfeld / count_duplicates.sql
Last active April 15, 2019 02:25
SELECT sum(c), count(c), sum(c) - count(c)
FROM (
SELECT count(*) as c
FROM public."DistrictCriminalCase"
GROUP BY fips, "CaseNumber"
HAVING count(*) > 1) a
@bschoenfeld
bschoenfeld / analyze_delete.sql
Created April 15, 2019 01:54
EXPLAIN ANALYZE
DELETE FROM "DistrictCriminalCase"
WHERE id=829409
@bschoenfeld
bschoenfeld / add_indexes.sql
Created April 15, 2019 01:53
CREATE index ON "CircuitCivilDefendant" (case_id);
CREATE index ON "CircuitCivilHearing" (case_id);
CREATE index ON "CircuitCivilPlaintiff" (case_id);
CREATE index ON "CircuitCivilPleading" (case_id);
CREATE index ON "CircuitCivilService" (case_id);
CREATE index ON "DistrictCivilDefendant" (case_id);
CREATE index ON "DistrictCivilHearing" (case_id);
CREATE index ON "DistrictCivilPlaintiff" (case_id);
CREATE index ON "DistrictCivilReport" (case_id);
@bschoenfeld
bschoenfeld / speeding_data_70_10-14.csv
Created January 7, 2018 14:25
Locality 10 11 12 13 14 Average
Hopewell -0.4 6.9 7.1 7.3 7.2 5.6
Emporia 6.5 3.1 2.4 1.8 2.0 3.2
@bschoenfeld
bschoenfeld / speeding_data_70_10-11.csv
Created January 6, 2018 21:36
Locality 10 11 Diff
Emporia 6.5 3.1 3.4
Smyth County 2.1 -0.1 2.2
Washington County 1.7 -0.2 1.9
Wythe County 1.2 -0.2 1.4
Alleghany County / Covington 0.9 -0.2 1.1
Hopewell -0.4 6.9 -7.3
@bschoenfeld
bschoenfeld / speeding_data_65.csv
Last active January 6, 2018 21:12
Locality 9 10 11 12 13 14 Average
Emporia -0.1 11.1 11.1 10.8 10.1 9.4 8.7
Southampton County 11.0 -0.1 0.2 0.6 0.9 2.2 2.5
Essex County -0.1 0.0 0.3 0.9 2.6 3.0 1.1
Charlotte County -0.1 -0.1 0.2 1.6 2.6 2.2 1.1
@bschoenfeld
bschoenfeld / speeding_data_55.csv
Created January 6, 2018 20:12
Locality 9 10 11 12 13 14 Average
Highland County 4.8 10.8 7.1 2.7 0.5 0.4 4.4
Brunswick County -0.2 0.1 2.0 4.9 4.9 5.3 2.8
Bath County 1.9 2.1 4.8 2.0 0.4 0.6 2.0
Cumberland County -0.3 -0.1 1.2 4.6 3.4 3.0 2.0
King & Queen County -0.3 -0.1 0.5 3.3 4.3 3.8 1.9
Page County 0.2 0.2 2.4 2.4 2.7 2.0 1.6
Craig County 8.1 -0.2 0.8 0.7 0.0 0.1 1.6
Giles County 4.4 -0.1 1.0 1.7 1.0 0.8 1.5
Greensville County -0.2 0.5 2.7 1.5 1.5 1.4 1.2
@bschoenfeld
bschoenfeld / speeding_data_45.csv
Created January 6, 2018 20:11
Locality 9 10 11 12 13 14 Average
Emporia -0.2 9.6 9.1 7.5 6.3 5.4 6.3
Greensville County -0.2 0.8 4.1 3.7 3.3 2.6 2.4
Bath County 1.8 0.5 2.7 2.9 2.1 1.6 1.9
Augusta County 10.0 0.1 0.0 0.1 0.1 0.3 1.8
Giles County 1.9 -0.1 -0.1 0.6 3.0 4.0 1.6
Washington County 1.4 4.7 0.4 0.0 0.2 1.1 1.3
Cumberland County -0.2 -0.1 1.9 3.6 1.9 0.4 1.2
Charles City County -0.2 -0.2 0.4 2.2 2.8 1.6 1.1
Danville 2.1 1.8 0.2 0.1 0.9 1.3 1.1