Report query log

Every SQL query logged during the report build, with its purpose, execution time and up to 20 result rows. Corpus fingerprint: b1857220890d635c.

Loading 157 logged queries…

Section:

Showing 157 queries

Q-0001·Section: overview·86 ms·18 rows

Tickets and set fines per year

select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total from tickets group by year order by year

Tickets and set fines per year — 18 rows
year	tickets	fines_total
2,008	2,868,448	111,428,745
2,009	2,774,553	113,314,385
2,010	2,760,045	111,518,215
2,011	2,805,492	114,226,595
2,012	2,746,154	111,485,565
2,013	2,613,156	106,554,180
2,014	2,484,983	108,987,340
2,015	2,168,493	99,872,770
2,016	2,254,761	109,675,985
2,017	2,144,226	106,200,060
2,018	2,044,288	100,206,695
2,019	2,213,939	108,424,170
2,020	1,403,663	70,555,350
2,021	1,481,709	81,688,410
2,022	1,821,887	96,347,560
2,023	2,256,360	122,409,230
2,024	2,147,159	145,978,460
2,025	2,051,175	164,705,020

Q-0002·Section: time·74 ms·24 rows

Tickets by hour of day, all years

SELECT hour, count(*) AS tickets, round(100.0*count(*)/sum(count(*)) over (),1) AS pct FROM tickets WHERE hour IS NOT NULL GROUP BY hour ORDER BY hour

Tickets by hour of day, all years — first 20 of 24 rows
hour	tickets	pct
0	1,803,912	4.4
1	1,423,511	3.5
2	1,707,912	4.2
3	1,778,425	4.3
4	1,296,866	3.2
5	326,322	0.8
6	431,848	1.1
7	994,686	2.4
8	1,774,532	4.3
9	2,646,553	6.5
10	2,336,493	5.7
11	2,949,239	7.2
12	3,070,113	7.5
13	2,848,605	6.9
14	2,068,674	5
15	2,281,515	5.6
16	2,344,597	5.7
17	1,748,106	4.3
18	1,475,501	3.6
19	1,744,409	4.3

Q-0003·Section: time·48 ms·7 rows

Tickets by day of week

SELECT dow, count(*) AS tickets, round(100.0*count(*)/sum(count(*)) over (),1) AS pct FROM tickets GROUP BY dow ORDER BY dow

Tickets by day of week — 7 rows
dow	tickets	pct
0	5,707,467	13.9
1	6,470,405	15.8
2	6,431,889	15.7
3	6,368,293	15.5
4	6,410,819	15.6
5	5,408,924	13.2
6	4,242,694	10.3

Q-0004·Section: time·56 ms·12 rows

Tickets by month

SELECT month(date) AS month, count(*) AS tickets, round(100.0*count(*)/sum(count(*)) over (),1) AS pct FROM tickets GROUP BY 1 ORDER BY 1

Tickets by month — 12 rows
month	tickets	pct
1	3,253,737	7.9
2	3,018,905	7.4
3	3,616,391	8.8
4	3,379,468	8.2
5	3,506,591	8.5
6	3,482,657	8.5
7	3,459,529	8.4
8	3,498,109	8.5
9	3,546,774	8.6
10	3,656,635	8.9
11	3,525,881	8.6
12	3,095,814	7.5

Q-0005·Section: time·135 ms·6 rows

Top hours, weekday vs weekend

WITH t AS (SELECT CASE WHEN dow>=5 THEN 'weekend' ELSE 'weekday' END AS part, hour, count(*) n FROM tickets WHERE hour IS NOT NULL GROUP BY 1,2) SELECT part, hour, n, round(100.0*n/sum(n) over (partition by part),1) pct FROM t QUALIFY row_number() over (partition by part order by n desc) <= 3 ORDER BY part, n desc

Top hours, weekday vs weekend — 6 rows
part	hour	n	pct
weekday	12	2,612,527	8.3
weekday	11	2,506,294	8
weekday	13	2,380,105	7.6
weekend	0	776,827	8.1
weekend	2	581,869	6
weekend	16	561,520	5.8

Q-0006·Section: time·2 ms·1 row

Missing time share

SELECT sum(rows) AS total, sum(invalid_time) AS invalid, round(100.0*sum(invalid_time)/sum(rows),2) AS pct_invalid FROM years

Missing time share — 1 row
total	invalid	pct_invalid
41,040,491	27,612	0.07

Q-0007·Section: overview·60 ms·1 row

Total tickets and key changes

WITH y AS (SELECT year, count(*) t FROM tickets GROUP BY year)
SELECT sum(t) AS total_tickets,
 round(100.0*(max(t) FILTER (WHERE year=2025) - max(t) FILTER (WHERE year=2008))/max(t) FILTER (WHERE year=2008),1) AS pct_change_2008_2025,
 round(100.0*(max(t) FILTER (WHERE year=2020) - max(t) FILTER (WHERE year=2019))/max(t) FILTER (WHERE year=2019),1) AS pct_change_2019_2020
FROM y

Total tickets and key changes — 1 row
total_tickets	pct_change_2008_2025	pct_change_2019_2020
41,040,491	-28.5	-36.6

Q-0008·Section: overview·302 ms·8 rows

Tickets by infraction family

SELECT t.family, f.label, count(*) tickets, round(100.0*count(*)/sum(count(*)) over (),1) pct
FROM tickets t LEFT JOIN families f USING(family) GROUP BY 1,2 ORDER BY 3 DESC

Tickets by infraction family — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	8,834,742	21.5
meter	Unpaid meter / pay-and-display	7,664,145	18.7
private_property	Private property	6,846,878	16.7
permit	Permit parking	6,283,505	15.3
no_stopping	No stopping / rush hour	5,363,378	13.1
other	Other	2,080,680	5.1
time_limit	Over the time limit	2,058,145	5
safety	Hydrant, fire route, accessible, sidewalk, bike lane	1,909,018	4.7

Q-0009·Section: overview·70 ms·5 rows

Busiest hours of day

SELECT hour, count(*) tickets, round(100.0*count(*)/sum(count(*)) over (),1) pct FROM tickets WHERE hour IS NOT NULL GROUP BY 1 ORDER BY 2 DESC LIMIT 5

Busiest hours of day — 5 rows
hour	tickets	pct
12	3,070,113	7.5
11	2,949,239	7.2
13	2,848,605	6.9
9	2,646,553	6.5
16	2,344,597	5.7

Q-0010·Section: overview·85 ms·7 rows

Tickets by day of week

SELECT dow, count(*) tickets, round(100.0*count(*)/sum(count(*)) over (),1) pct FROM tickets GROUP BY 1 ORDER BY 1

Tickets by day of week — 7 rows
dow	tickets	pct
0	5,707,467	13.9
1	6,470,405	15.8
2	6,431,889	15.7
3	6,368,293	15.5
4	6,410,819	15.6
5	5,408,924	13.2
6	4,242,694	10.3

Q-0011·Section: overview·519 ms·5 rows

Top streets by ticket count (primary segment)

SELECT s.street_name, sum(CASE WHEN t.segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END) w FROM tickets t JOIN segments s ON s.segment_id=t.segment_id GROUP BY 1 ORDER BY 2 DESC LIMIT 5

Top streets by ticket count (primary segment) — 5 rows
street_name	w
Yonge St	1,055,391
Bloor St W	762,660.5
Queen St W	718,744
Danforth Ave	692,502.5
Queen St E	512,104

Q-0012·Section: infractions-fines·62 ms·8 rows

Tickets and fines by infraction family

WITH t AS (SELECT family, count(*) n, sum(fine) f FROM tickets GROUP BY family)
SELECT t.family, fa.label, n, round(100.0*n/sum(n) over(),1) pct_tickets, round(f,0) total_fines, round(100.0*f/sum(f) over(),1) pct_fines, round(f/n,2) avg_fine FROM t LEFT JOIN families fa USING(family) ORDER BY n DESC

Tickets and fines by infraction family — 8 rows
family	label	n	pct_tickets	total_fines	pct_fines	avg_fine
no_parking	No parking (signed hours)	8,834,742	21.5	379,259,425	19.1	42.93
meter	Unpaid meter / pay-and-display	7,664,145	18.7	250,299,380	12.6	32.66
private_property	Private property	6,846,878	16.7	244,976,280	12.4	35.78
permit	Permit parking	6,283,505	15.3	194,445,250	9.8	30.95
no_stopping	No stopping / rush hour	5,363,378	13.1	453,913,635	22.9	84.63
other	Other	2,080,680	5.1	100,530,550	5.1	48.32
time_limit	Over the time limit	2,058,145	5	42,700,695	2.2	20.75
safety	Hydrant, fire route, accessible, sidewalk, bike lane	1,909,018	4.7	317,453,520	16	166.29

Q-0013·Section: infractions-fines·90 ms·12 rows

Most common infraction codes

WITH t AS (SELECT code, count(*) n, sum(fine) f FROM tickets GROUP BY code)
SELECT t.code, c.description_latest, c.family, n, round(100.0*n/sum(n) over(),1) pct_tickets, round(f,0) total_fines, round(100.0*f/sum(f) over(),1) pct_fines, c.typical_fine_latest FROM t LEFT JOIN codes c USING(code) ORDER BY n DESC LIMIT 12

Most common infraction codes — 12 rows
code	description_latest	family	n	pct_tickets	total_fines	pct_fines	typical_fine_latest
5	PARK-SIGNED HWY-PROHIBIT DY/TM	no_parking	7,308,759	17.8	324,402,825	16.4	65
3	PARK ON PRIVATE PROPERTY	private_property	6,444,019	15.7	228,532,725	11.5	75
29	PARK PROHIBITED TIME NO PERMIT	permit	6,155,161	15	190,517,960	9.6	45
207	PARK MACHINE-REQD FEE NOT PAID	meter	4,206,115	10.2	133,774,710	6.7	50
210	PARK FAIL TO DISPLAY RECEIPT	meter	2,824,499	6.9	84,734,820	4.3	0
9	STOP-SIGNED HWY-PROHIBIT TM/DY	no_stopping	2,405,410	5.9	176,777,380	8.9	120
2	PARK - LONGER THAN 3 HOURS	time_limit	2,057,452	5	42,649,370	2.2	40
8	STAND VEH.-PROHIBIT TIME/DAY	no_stopping	1,485,977	3.6	106,294,230	5.4	120
6	PARK-SIGNED HWY-EXC PERMT TIME	no_parking	1,216,501	3	43,521,230	2.2	50
403	STOP-SIGNED HIGHWAY-RUSH HOUR	no_stopping	695,387	1.7	110,897,410	5.6	190
15	PARK-WITHIN 3M OF FIRE HYDRANT	safety	601,909	1.5	60,428,905	3	125
312	PARKING MACH-NOT USED/NO FEE	meter	546,484	1.3	28,653,735	1.4	75

Q-0014·Section: infractions-fines·100 ms·10 rows

Codes with largest total set fines

WITH t AS (SELECT code, count(*) n, sum(fine) f FROM tickets GROUP BY code)
SELECT t.code, c.description_latest, c.family, n, round(f,0) total_fines, round(100.0*f/sum(f) over(),1) pct_fines, round(f/n,2) avg_fine FROM t LEFT JOIN codes c USING(code) ORDER BY f DESC LIMIT 10

Codes with largest total set fines — 10 rows
code	description_latest	family	n	total_fines	pct_fines	avg_fine
5	PARK-SIGNED HWY-PROHIBIT DY/TM	no_parking	7,308,759	324,402,825	16.4	44.39
3	PARK ON PRIVATE PROPERTY	private_property	6,444,019	228,532,725	11.5	35.46
29	PARK PROHIBITED TIME NO PERMIT	permit	6,155,161	190,517,960	9.6	30.95
9	STOP-SIGNED HWY-PROHIBIT TM/DY	no_stopping	2,405,410	176,777,380	8.9	73.49
207	PARK MACHINE-REQD FEE NOT PAID	meter	4,206,115	133,774,710	6.7	31.8
403	STOP-SIGNED HIGHWAY-RUSH HOUR	no_stopping	695,387	110,897,410	5.6	159.48
8	STAND VEH.-PROHIBIT TIME/DAY	no_stopping	1,485,977	106,294,230	5.4	71.53
210	PARK FAIL TO DISPLAY RECEIPT	meter	2,824,499	84,734,820	4.3	30
347	PARK IN A FIRE ROUTE	safety	325,698	81,424,280	4.1	250
15	PARK-WITHIN 3M OF FIRE HYDRANT	safety	601,909	60,428,905	3	100.4

Q-0015·Section: infractions-fines·562 ms·1 row

Overall set fine distribution

SELECT count(*) n, round(sum(fine),0) total_fines, median(fine) median_fine, mode(fine) modal_fine, round(avg(fine),2) avg_fine, round(100.0*count(*) FILTER (WHERE fine<=50)/count(*),1) pct_le_50, round(100.0*count(*) FILTER (WHERE fine>=100)/count(*),1) pct_ge_100, round(100.0*count(*) FILTER (WHERE fine>=300)/count(*),1) pct_ge_300 FROM tickets

Overall set fine distribution — 1 row
n	total_fines	median_fine	modal_fine	avg_fine	pct_le_50	pct_ge_100	pct_ge_300
41,040,491	1,983,578,735	30	30	48.33	78.8	8.9	0.7

Q-0016·Section: infractions-fines·504 ms·2 rows

Public street vs private property mix

SELECT is_public, count(*) n, round(100.0*count(*)/sum(count(*)) over(),1) pct_tickets, round(sum(fine),0) total_fines, round(avg(fine),2) avg_fine, median(fine) median_fine FROM tickets GROUP BY is_public

Public street vs private property mix — 2 rows
is_public	n	pct_tickets	total_fines	avg_fine	median_fine
false	6,846,878	16.7	244,976,280	35.78	30
true	34,193,613	83.3	1,738,602,455	50.85	40

Q-0017·Section: time·121 ms·2 rows

Overnight vs daytime share, weekday vs weekend

SELECT CASE WHEN dow>=5 THEN 'weekend' ELSE 'weekday' END AS part, count(*) n, round(100.0*avg(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END),1) AS pct_overnight, round(100.0*avg(CASE WHEN hour BETWEEN 9 AND 16 THEN 1 ELSE 0 END),1) AS pct_daytime FROM tickets WHERE hour IS NOT NULL GROUP BY 1 ORDER BY 1

Overnight vs daytime share, weekday vs weekend — 2 rows
part	n	pct_overnight	pct_daytime
weekday	31,367,283	16.6	54.4
weekend	9,645,596	29.2	36.2

Q-0018·Section: time·84 ms·5 rows

Infraction families in overnight hours (midnight to 5 a.m.)

SELECT t.family, f.label, count(*) n, round(100.0*count(*)/sum(count(*)) over (),1) pct FROM tickets t LEFT JOIN families f USING(family) WHERE hour BETWEEN 0 AND 4 GROUP BY 1,2 ORDER BY n DESC LIMIT 5

Infraction families in overnight hours (midnight to 5 a.m.) — 5 rows
family	label	n	pct
permit	Permit parking	3,276,454	40.9
private_property	Private property	2,217,618	27.7
time_limit	Over the time limit	1,066,418	13.3
other	Other	644,316	8
no_parking	No parking (signed hours)	441,144	5.5

Q-0019·Section: overview·502 ms·5 rows

Top streets by weighted ticket count incl. corner splits

WITH w AS (
 SELECT segment_id sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL
 UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL)
SELECT s.street_name, round(sum(wt)) tickets FROM w JOIN segments s ON s.segment_id=w.sid GROUP BY 1 ORDER BY 2 DESC LIMIT 5

Top streets by weighted ticket count incl. corner splits — 5 rows
street_name	tickets
Yonge St	1,055,746
Bloor St W	762,750
Queen St W	718,838
Danforth Ave	692,583
Queen St E	512,190

Q-0020·Section: pandemic·4 ms·9 rows

Yearly ticket totals around the pandemic

SELECT year, rows, invalid_time, notes FROM years WHERE year BETWEEN 2016 AND 2024 ORDER BY year

Yearly ticket totals around the pandemic — 9 rows
year	rows	invalid_time	notes
2,016	2,254,761	1,329	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1329 tickets have no valid time.
2,017	2,144,226	1,377	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1377 tickets have no valid time.
2,018	2,044,288	1,549	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1549 tickets have no valid time.
2,019	2,213,939	1,959	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1959 tickets have no valid time.
2,020	1,403,663	1,300	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1300 tickets have no valid time.
2,021	1,481,709	903	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 903 tickets have no valid time.
2,022	1,821,887	963	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 963 tickets have no valid time.
2,023	2,256,360	696	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 696 tickets have no valid time.
2,024	2,147,159	252	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 252 tickets have no valid time.

Q-0021·Section: pandemic·68 ms·48 rows

Infraction family mix by year 2018-2023

SELECT year, family, round(100.0*count(*)/sum(count(*)) over (partition by year),1) AS share_pct FROM tickets WHERE year BETWEEN 2018 AND 2023 GROUP BY year, family ORDER BY family, year

Infraction family mix by year 2018-2023 — first 20 of 48 rows
year	family	share_pct
2,018	meter	12.7
2,019	meter	14.5
2,020	meter	16
2,021	meter	14.8
2,022	meter	19
2,023	meter	18.9
2,018	no_parking	19.8
2,019	no_parking	18.5
2,020	no_parking	18
2,021	no_parking	16.8
2,022	no_parking	16.9
2,023	no_parking	16.9
2,018	no_stopping	14.3
2,019	no_stopping	13
2,020	no_stopping	11.3
2,021	no_stopping	13.8
2,022	no_stopping	12.9
2,023	no_stopping	14.1
2,018	other	8.5
2,019	other	8.7

Q-0022·Section: fines-2024·97 ms·4 rows

Tickets and average fine by year 2022-2025

SELECT year, count(*) AS tickets, round(avg(fine),2) AS avg_fine, round(median(fine),2) AS median_fine, round(sum(fine),0) AS total_fines FROM tickets WHERE year BETWEEN 2022 AND 2025 GROUP BY year ORDER BY year

Tickets and average fine by year 2022-2025 — 4 rows
year	tickets	avg_fine	median_fine	total_fines
2,022	1,821,887	52.88	30	96,347,560
2,023	2,256,360	54.25	30	122,409,230
2,024	2,147,159	67.99	50	145,978,460
2,025	2,051,175	80.3	65	164,705,020

Q-0023·Section: fines-2024·38 ms·40 rows

Codes whose typical fine changed 2023-2025

WITH m AS (SELECT code, year, mode(fine) AS typ, count(*) AS n FROM tickets WHERE year BETWEEN 2023 AND 2025 GROUP BY code, year),
p AS (SELECT code,
 max(CASE WHEN year=2023 THEN typ END) f23, max(CASE WHEN year=2024 THEN typ END) f24, max(CASE WHEN year=2025 THEN typ END) f25,
 max(CASE WHEN year=2023 THEN n END) n23, max(CASE WHEN year=2024 THEN n END) n24, max(CASE WHEN year=2025 THEN n END) n25
 FROM m GROUP BY code)
SELECT p.code, c.family, c.description_latest, f23, f24, f25, n23, n24, n25 FROM p JOIN codes c USING(code)
WHERE f23 IS DISTINCT FROM f24 OR f24 IS DISTINCT FROM f25
ORDER BY coalesce(n24,0) DESC LIMIT 40

Codes whose typical fine changed 2023-2025 — first 20 of 40 rows
code	family	description_latest	f23	f24	f25	n23	n24	n25
3	private_property	PARK ON PRIVATE PROPERTY	30	75	75	435,620	365,060	387,901
207	meter	PARK MACHINE-REQD FEE NOT PAID	30	30	50	366,917	329,296	270,929
29	permit	PARK PROHIBITED TIME NO PERMIT	30	30	45	331,429	319,967	273,337
5	no_parking	PARK-SIGNED HWY-PROHIBIT DY/TM	50	50	65	317,607	312,790	286,967
9	no_stopping	STOP-SIGNED HWY-PROHIBIT TM/DY	100	100	120	130,968	134,151	144,105
2	time_limit	PARK - LONGER THAN 3 HOURS	30	30	40	109,467	128,121	139,059
8	no_stopping	STAND VEH.-PROHIBIT TIME/DAY	100	100	120	67,047	69,423	61,154
403	no_stopping	STOP-SIGNED HIGHWAY-RUSH HOUR	150	150	190	74,635	65,846	75,710
6	no_parking	PARK-SIGNED HWY-EXC PERMT TIME	40	40	50	51,855	50,163	40,185
4	private_property	PARK ON MUNICIPAL PROPERTY	30	75	75	49,386	46,821	47,810
28	other	PARK-N.YORK 2AM-6AM DEC1-MAR31	40	40	50	26,545	31,899	27,003
15	safety	PARK-WITHIN 3M OF FIRE HYDRANT	100	100	125	29,145	30,851	28,941
415	other	PARK COMMERC LOAD ZONE NOT LDG	40	40	50	25,424	23,819	21,592
192	no_stopping	STAND SIGNED TRANSIT STOP	150	150	190	16,857	16,519	13,215
134	no_parking	PARK-SIGNED HWY-PUBLIC LANE	40	40	50	11,867	14,285	14,844
30	safety	STOP-(ON/OVER) (SIDEWK/FTPATH)	150	150	200	13,627	13,415	12,618
337	safety	PARK - ON BOULEVARD	50	50	70	6,765	9,320	7,736
336	permit	PARK-FAIL TO DISPLAY PERMIT	30	30	—	16,329	8,542	—
384	safety	STOP VEH OTR THN BCYCL-BYCL LN	150	150	200	5,894	7,272	11,330
419	no_stopping	STAND NON-TAXI IN SIGNED STAND	60	60	75	8,073	6,963	7,046

Q-0024·Section: hotspots·187 ms·15 rows

Most-ticketed blocks 2008-2025

WITH w AS (SELECT segment_id seg, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT seg, sum(wt) n FROM w GROUP BY seg)
SELECT s.seg, g.street_name, g.from_street, g.to_street, round(g.length_m) len_m, round(s.n,1) tickets, round(100.0*s.n/(SELECT sum(n) FROM s),2) pct_matched
FROM s JOIN segments g ON g.segment_id=s.seg ORDER BY s.n DESC LIMIT 15

Most-ticketed blocks 2008-2025 — 15 rows
seg	street_name	from_street	to_street	len_m	tickets	pct_matched
14,064,697	Keele St	The Pond Rd	York Blvd	359	149,472.5	0.38
60,076,475	Bayview Ave	Blythwood Rd	—	126	136,770	0.35
7,762,735	Edward St	Yonge St	Bay St	234	131,247	0.34
10,486,145	Queen St W	Peter St	Spadina Ave	228	92,577.5	0.24
30,009,802	Yorkville Ave	Bellair St	Hazelton Ave	150	89,883.5	0.23
9,340,683	Bayview Ave	Belsize Dr	Manor Rd E	212	75,636.5	0.19
6,349,934	University Ave	Armoury St	Dundas St W	200	70,514.5	0.18
436,731	Finch Ave E	Seneca Hill Dr	404 S Finch W Ramp	257	69,821	0.18
60,056,411	Cumberland St	Bellair St	—	141	68,421.5	0.18
14,673,808	Elm St	Harry Barberian Lane	Ln E Bay S Elm	127	67,468	0.17
11,046,582	Danforth Ave	Broadview Ave	Ellerbeck St	205	65,750	0.17
10,258,454	Orchard View Blvd	Yonge St	Duplex Ave	181	60,295	0.15
8,540,537	Yonge St	Broadway Ave	Roselawn Ave	151	59,814.5	0.15
439,467	Spring Garden Ave	Yonge St	Doris Ave	252	58,574.5	0.15
60,030,555	Yonge St	Hillcrest Ave	Empress Ave	203	58,556	0.15

Q-0025·Section: hotspots·250 ms·1 row

Concentration of matched tickets across blocks

WITH w AS (SELECT segment_id seg, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT seg, sum(wt) n FROM w GROUP BY seg),
r AS (SELECT n, row_number() OVER (ORDER BY n DESC) rk, count(*) OVER () tot, sum(n) OVER () alln FROM s)
SELECT max(tot) ticketed_blocks, (SELECT count(*) FROM segments) all_road_segments, round(max(alln)) matched_tickets,
round(100.0*sum(CASE WHEN rk<=ceil(tot*0.01) THEN n END)/max(alln),1) top1_share,
round(100.0*sum(CASE WHEN rk<=ceil(tot*0.10) THEN n END)/max(alln),1) top10_share,
round(100.0*sum(CASE WHEN rk<=ceil(tot*0.50) THEN n END)/max(alln),1) top50_share,
max(CASE WHEN rk=ceil(tot*0.01) THEN ceil(tot*0.01) END) top1_count,
round(median(n),1) median_block
FROM r

Concentration of matched tickets across blocks — 1 row
ticketed_blocks	all_road_segments	matched_tickets	top1_share	top10_share	top50_share	top1_count	median_block
38,513	50,079	38,943,724	28	76.6	98.4	386	106

Q-0026·Section: pandemic·2 ms·1 row

Percentage changes in totals vs 2019

SELECT round(100.0*(1403663-2213939)/2213939,1) AS chg_2019_2020_pct, round(100.0*(1481709-2213939)/2213939,1) AS chg_2019_2021_pct, round(100.0*(2256360-2213939)/2213939,1) AS chg_2019_2023_pct

Percentage changes in totals vs 2019 — 1 row
chg_2019_2020_pct	chg_2019_2021_pct	chg_2019_2023_pct
-36.6	-33.1	1.9

Q-0027·Section: pandemic·41 ms·48 rows

Monthly tickets 2019-2022

SELECT strftime(date,'%Y-%m') AS month, count(*) AS tickets FROM tickets WHERE year BETWEEN 2019 AND 2022 GROUP BY 1 ORDER BY 1

Monthly tickets 2019-2022 — first 20 of 48 rows
month	tickets
2019-01	160,463
2019-02	140,191
2019-03	193,198
2019-04	188,258
2019-05	193,634
2019-06	186,244
2019-07	196,548
2019-08	190,099
2019-09	198,012
2019-10	202,567
2019-11	192,054
2019-12	172,671
2020-01	190,022
2020-02	168,063
2020-03	130,390
2020-04	17,979
2020-05	25,493
2020-06	37,877
2020-07	102,692
2020-08	135,285

Q-0028·Section: pandemic·44 ms·6 rows

Time-of-day and weekend shares by year

SELECT year, round(100.0*count(*) FILTER (WHERE hour<6)/count(hour),1) AS overnight_pct, round(100.0*count(*) FILTER (WHERE hour BETWEEN 9 AND 17)/count(hour),1) AS daytime_9_17_pct, round(100.0*count(*) FILTER (WHERE dow>=5)/count(*),1) AS weekend_pct FROM tickets WHERE year BETWEEN 2018 AND 2023 GROUP BY year ORDER BY year

Time-of-day and weekend shares by year — 6 rows
year	overnight_pct	daytime_9_17_pct	weekend_pct
2,018	21	53.5	22.6
2,019	21.3	52.8	22.4
2,020	22.6	53.4	21.6
2,021	22.7	53.7	21.2
2,022	22	51.8	22.9
2,023	21.7	51.4	22.4

Q-0029·Section: hotspots·277 ms·10 rows

Top 10 blocks with private-property share

WITH w AS (SELECT segment_id seg, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt, is_public FROM tickets WHERE segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5, is_public FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT seg, sum(wt) n, sum(CASE WHEN NOT is_public THEN wt ELSE 0 END) priv FROM w GROUP BY seg)
SELECT g.street_name || ' (' || g.from_street || coalesce(' to ' || g.to_street, '') || ')' AS block, round(s.n,1) tickets, round(100.0*priv/n,1) pct_private
FROM s JOIN segments g ON g.segment_id=s.seg ORDER BY s.n DESC LIMIT 10

Top 10 blocks with private-property share — 10 rows
block	tickets	pct_private
Keele St (The Pond Rd to York Blvd)	149,472.5	97.9
Bayview Ave (Blythwood Rd)	136,770	99.9
Edward St (Yonge St to Bay St)	131,247	0.3
Queen St W (Peter St to Spadina Ave)	92,577.5	2.6
Yorkville Ave (Bellair St to Hazelton Ave)	89,883.5	0.3
Bayview Ave (Belsize Dr to Manor Rd E)	75,636.5	0.2
University Ave (Armoury St to Dundas St W)	70,514.5	0.2
Finch Ave E (Seneca Hill Dr to 404 S Finch W Ramp)	69,821	99
Cumberland St (Bellair St)	68,421.5	0.2
Elm St (Harry Barberian Lane to Ln E Bay S Elm)	67,468	0.5

Q-0030·Section: fines-2024·23 ms·18 rows

Codes with typical fine change 2023 to 2024 and volume change

WITH m AS (SELECT code, year, mode(fine) AS typ, count(*) AS n FROM tickets WHERE year IN (2023,2024) GROUP BY code, year),
p AS (SELECT code, max(CASE WHEN year=2023 THEN typ END) f23, max(CASE WHEN year=2024 THEN typ END) f24,
 max(CASE WHEN year=2023 THEN n END) n23, max(CASE WHEN year=2024 THEN n END) n24 FROM m GROUP BY code)
SELECT code, f23, f24, n23, n24, round(100.0*(n24-n23)/n23,1) AS pct_change FROM p WHERE f23 <> f24 ORDER BY n24 DESC

Codes with typical fine change 2023 to 2024 and volume change — 18 rows
code	f23	f24	n23	n24	pct_change
3	30	75	435,620	365,060	-16.2
4	30	75	49,386	46,821	-5.2
414	40	50	745	1,052	41.2
50	30	40	271	341	25.8
208	30	50	211	191	-9.5
387	60	200	165	177	7.3
71	60	75	21	52	147.6
12	15	25	28	44	57.1
385	60	200	25	36	44
409	100	125	6	33	450
20	40	75	26	14	-46.2
21	40	75	8	14	75
13	15	25	16	13	-18.8
79	40	50	10	7	-30
342	50	70	36	6	-83.3
73	60	75	3	2	-33.3
72	60	75	3	2	-33.3
343	50	70	3	1	-66.7

Q-0031·Section: fines-2024·43 ms·3 rows

Average fine public vs private property 2023-2025

SELECT year, round(avg(fine) FILTER (WHERE is_public),2) AS avg_fine_public, round(avg(fine) FILTER (WHERE NOT is_public),2) AS avg_fine_private, count(*) FILTER (WHERE NOT is_public) AS private_tickets, round(100.0*count(*) FILTER (WHERE NOT is_public)/count(*),1) AS private_share_pct, count(*) FILTER (WHERE is_public) AS public_tickets FROM tickets WHERE year BETWEEN 2023 AND 2025 GROUP BY year ORDER BY year

Average fine public vs private property 2023-2025 — 3 rows
year	avg_fine_public	avg_fine_private	private_tickets	private_share_pct	public_tickets
2,023	60.08	32.98	485,006	21.5	1,771,354
2,024	66.33	74.99	411,881	19.2	1,735,278
2,025	81.73	75	435,711	21.2	1,615,464

Q-0032·Section: fines-2024·17 ms·12 rows

Monthly average fine in 2024, code 3 and all

SELECT month(date) AS month, round(avg(fine) FILTER (WHERE code=3),2) AS avg_fine_code3, round(avg(fine),2) AS avg_fine_all FROM tickets WHERE year=2024 GROUP BY 1 ORDER BY 1

Monthly average fine in 2024, code 3 and all — 12 rows
month	avg_fine_code3	avg_fine_all
1	74.99	63.72
2	74.99	62.49
3	74.99	62.33
4	75	62.18
5	75	62.68
6	74.99	61.85
7	75	64.19
8	75	77.3
9	75	76.62
10	75	76.01
11	75	76.31
12	75	77.55

Q-0033·Section: fines-2024·22 ms·12 rows

Monthly fines for top public codes in 2024

SELECT month(date) AS month, round(avg(fine) FILTER (WHERE code=207),2) AS c207, round(avg(fine) FILTER (WHERE code=5),2) AS c5, round(avg(fine) FILTER (WHERE code=9),2) AS c9, round(avg(fine) FILTER (WHERE is_public),2) AS public_avg, count(*) AS tickets FROM tickets WHERE year=2024 GROUP BY 1 ORDER BY 1

Monthly fines for top public codes in 2024 — 12 rows
month	c207	c5	c9	public_avg	tickets
1	30	50	100	61.31	186,602
2	30	50	100	59.66	191,638
3	30	50	100	59.66	196,324
4	30	50	100	59.51	197,986
5	30	50	99.99	59.96	199,731
6	30	50	100	58.96	190,376
7	30	50	100	61.69	181,263
8	50	65	120	77.84	167,811
9	50	65	120	77.08	162,185
10	50	65	120	76.3	169,974
11	50	65	120	76.69	160,388
12	50	65	120	78.22	142,881

Q-0034·Section: fines-2024·16 ms·6 rows

Private property fine around the 2023-2024 change

SELECT year, month(date) AS month, round(avg(fine) FILTER (WHERE code=3),2) AS avg_fine_code3, count(*) FILTER (WHERE code=3) AS code3_tickets FROM tickets WHERE (year=2023 AND month(date)>=10) OR (year=2024 AND month(date)<=3) GROUP BY 1,2 ORDER BY 1,2

Private property fine around the 2023-2024 change — 6 rows
year	month	avg_fine_code3	code3_tickets
2,023	10	30	37,978
2,023	11	30	37,701
2,023	12	74.99	29,679
2,024	1	74.99	30,099
2,024	2	74.99	31,937
2,024	3	74.99	30,099

Q-0035·Section: streets·482 ms·10 rows

Top streets by tickets 2008-2025

WITH w AS (
 SELECT segment_id sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL
 UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT sg.street_name, sum(wt) t FROM w JOIN segments sg ON sg.segment_id=w.sid GROUP BY 1),
tot AS (SELECT count(*) n FROM tickets)
SELECT street_name, round(t,0) tickets, round(100.0*t/n,1) pct_all, (SELECT n FROM tot) all_tickets FROM s, tot ORDER BY t DESC LIMIT 10

Top streets by tickets 2008-2025 — 10 rows
street_name	tickets	pct_all	all_tickets
Yonge St	1,055,746	2.6	41,040,491
Bloor St W	762,750	1.9	41,040,491
Queen St W	718,838	1.8	41,040,491
Danforth Ave	692,583	1.7	41,040,491
Queen St E	512,190	1.2	41,040,491
King St W	468,927	1.1	41,040,491
Dundas St W	453,671	1.1	41,040,491
College St	418,443	1	41,040,491
St Clair Ave W	386,150	0.9	41,040,491
Bayview Ave	334,981	0.8	41,040,491

Q-0036·Section: fines-2024·15 ms·2 rows

Public-street tickets before and after August, 2023 vs 2024

WITH t AS (SELECT year, count(*) FILTER (WHERE is_public AND month(date)<=7) AS jan_jul, count(*) FILTER (WHERE is_public AND month(date)>=8) AS aug_dec FROM tickets WHERE year IN (2023,2024) GROUP BY year)
SELECT year, jan_jul, aug_dec, round(100.0*(jan_jul - lag(jan_jul) OVER (ORDER BY year))/lag(jan_jul) OVER (ORDER BY year),1) AS jan_jul_pct, round(100.0*(aug_dec - lag(aug_dec) OVER (ORDER BY year))/lag(aug_dec) OVER (ORDER BY year),1) AS aug_dec_pct FROM t ORDER BY year

Public-street tickets before and after August, 2023 vs 2024 — 2 rows
year	jan_jul	aug_dec	jan_jul_pct	aug_dec_pct
2,023	956,991	814,363	—	—
2,024	1,102,795	632,483	15.2	-22.3

Q-0037·Section: fines-2024·1 ms·3 rows

Data-quality notes 2023-2025

SELECT year, rows, notes FROM years WHERE year BETWEEN 2023 AND 2025 ORDER BY year

Data-quality notes 2023-2025 — 3 rows
year	rows	notes
2,023	2,256,360	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 696 tickets have no valid time.
2,024	2,147,159	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 252 tickets have no valid time.
2,025	2,051,175	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 422 tickets have no valid time.

Q-0038·Section: streets·686 ms·54 rows

Top 3 streets each year

WITH w AS (
 SELECT year, segment_id sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL
 UNION ALL SELECT year, segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT year, sg.street_name, sum(wt) t FROM w JOIN segments sg ON sg.segment_id=w.sid GROUP BY 1,2),
r AS (SELECT *, rank() OVER (PARTITION BY year ORDER BY t DESC) rk FROM s)
SELECT year, street_name, round(t,0) tickets, rk FROM r WHERE rk<=3 ORDER BY year, rk

Top 3 streets each year — first 20 of 54 rows
year	street_name	tickets	rk
2,008	Yonge St	85,365	1
2,008	Danforth Ave	70,662	2
2,008	Bloor St W	67,313	3
2,009	Bloor St W	74,433	1
2,009	Yonge St	68,323	2
2,009	Danforth Ave	63,442	3
2,010	Yonge St	70,085	1
2,010	Danforth Ave	60,864	2
2,010	Bloor St W	60,569	3
2,011	Yonge St	85,677	1
2,011	Bloor St W	63,939	2
2,011	Danforth Ave	53,301	3
2,012	Yonge St	86,264	1
2,012	Bloor St W	70,453	2
2,012	Danforth Ave	50,287	3
2,013	Yonge St	79,891	1
2,013	Bloor St W	68,038	2
2,013	Danforth Ave	46,884	3
2,014	Yonge St	72,377	1
2,014	Bloor St W	64,082	2

Q-0039·Section: streets·2 ms·18 rows

Yearly row counts and match rates

SELECT year, rows, round(100*match_rate,1) match_pct, notes FROM years ORDER BY year

Yearly row counts and match rates — 18 rows
year	rows	match_pct	notes
2,008	2,868,448	92.9	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 3511 tickets have no valid time.
2,009	2,774,553	93.1	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 2911 tickets have no valid time.
2,010	2,760,045	93.5	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 2562 tickets have no valid time.
2,011	2,805,492	93.9	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 2063 tickets have no valid time.
2,012	2,746,154	94.4	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1728 tickets have no valid time.
2,013	2,613,156	94.6	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1403 tickets have no valid time.
2,014	2,484,983	95	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1254 tickets have no valid time.
2,015	2,168,493	95.4	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1430 tickets have no valid time.
2,016	2,254,761	95.7	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1329 tickets have no valid time.
2,017	2,144,226	95.8	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1377 tickets have no valid time.
2,018	2,044,288	95.4	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1549 tickets have no valid time.
2,019	2,213,939	95.2	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1959 tickets have no valid time.
2,020	1,403,663	95.2	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1300 tickets have no valid time.
2,021	1,481,709	95.7	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 903 tickets have no valid time.
2,022	1,821,887	96.2	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 963 tickets have no valid time.
2,023	2,256,360	96.2	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 696 tickets have no valid time.
2,024	2,147,159	96.5	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 252 tickets have no valid time.
2,025	2,051,175	96.1	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 422 tickets have no valid time.

Q-0040·Section: year-2008·32 ms·1 row

Key figures for 2008 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2008

Key figures for 2008 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,008	2,868,448	111,428,745	—	—

Q-0041·Section: year-2008·13 ms·24 rows

Tickets by hour of day in 2008

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2008
                group by h.hour order by h.hour

Tickets by hour of day in 2008 — first 20 of 24 rows
hour	tickets
0	145,097
1	109,204
2	112,738
3	116,202
4	79,624
5	19,160
6	22,958
7	59,327
8	116,547
9	172,195
10	145,412
11	199,384
12	222,500
13	210,640
14	150,719
15	179,755
16	179,757
17	126,926
18	102,818
19	123,321

Q-0042·Section: year-2008·22 ms·8 rows

Infraction family mix in 2008

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2008
                group by f.family, f.label order by tickets desc

Infraction family mix in 2008 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	717,158	25
meter	Unpaid meter / pay-and-display	633,479	22.1
permit	Permit parking	462,055	16.1
private_property	Private property	340,001	11.9
no_stopping	No stopping / rush hour	336,929	11.7
safety	Hydrant, fire route, accessible, sidewalk, bike lane	149,660	5.2
time_limit	Over the time limit	131,373	4.6
other	Other	97,793	3.4

Q-0043·Section: streets·149 ms·15 rows

Top infraction families on leading streets

WITH w AS (
 SELECT family, segment_id sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL
 UNION ALL SELECT family, segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT sg.street_name, family, sum(wt) t FROM w JOIN segments sg ON sg.segment_id=w.sid
 WHERE sg.street_name IN ('Yonge St','Bloor St W','Queen St W','Danforth Ave','Keele St') GROUP BY 1,2),
r AS (SELECT *, round(100.0*t/sum(t) OVER (PARTITION BY street_name),1) pct, rank() OVER (PARTITION BY street_name ORDER BY t DESC) rk FROM s)
SELECT r.street_name, r.family, f.label, round(t,0) tickets, pct, rk FROM r LEFT JOIN families f USING(family) WHERE rk<=3 ORDER BY street_name, rk

Top infraction families on leading streets — 15 rows
street_name	family	label	tickets	pct	rk
Bloor St W	meter	Unpaid meter / pay-and-display	367,700	48.2	1
Bloor St W	no_stopping	No stopping / rush hour	161,790	21.2	2
Bloor St W	no_parking	No parking (signed hours)	152,988	20.1	3
Danforth Ave	meter	Unpaid meter / pay-and-display	400,966	57.9	1
Danforth Ave	no_parking	No parking (signed hours)	152,887	22.1	2
Danforth Ave	no_stopping	No stopping / rush hour	57,661	8.3	3
Keele St	private_property	Private property	175,384	87.7	1
Keele St	meter	Unpaid meter / pay-and-display	7,658	3.8	2
Keele St	safety	Hydrant, fire route, accessible, sidewalk, bike lane	7,066	3.5	3
Queen St W	meter	Unpaid meter / pay-and-display	320,786	44.6	1
Queen St W	no_stopping	No stopping / rush hour	190,140	26.5	2
Queen St W	no_parking	No parking (signed hours)	121,172	16.9	3
Yonge St	no_parking	No parking (signed hours)	281,726	26.7	1
Yonge St	meter	Unpaid meter / pay-and-display	271,522	25.7	2
Yonge St	no_stopping	No stopping / rush hour	254,651	24.1	3

Q-0044·Section: streets·706 ms·18 rows

Keele St tickets and rank by year

WITH w AS (
 SELECT year, segment_id sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END wt FROM tickets WHERE segment_id IS NOT NULL
 UNION ALL SELECT year, segment_id_alt, 0.5 FROM tickets WHERE segment_id_alt IS NOT NULL),
s AS (SELECT year, sg.street_name, sum(wt) t FROM w JOIN segments sg ON sg.segment_id=w.sid GROUP BY 1,2),
r AS (SELECT *, rank() OVER (PARTITION BY year ORDER BY t DESC) rk FROM s)
SELECT year, round(t,0) keele_tickets, rk keele_rank FROM r WHERE street_name='Keele St' ORDER BY year

Keele St tickets and rank by year — 18 rows
year	keele_tickets	keele_rank
2,008	2,757	173
2,009	2,706	181
2,010	2,845	168
2,011	2,486	195
2,012	2,651	186
2,013	2,912	164
2,014	3,642	108
2,015	2,920	131
2,016	4,065	84
2,017	2,742	143
2,018	3,401	106
2,019	9,224	25
2,020	5,756	30
2,021	6,951	27
2,022	26,249	3
2,023	43,837	3
2,024	39,344	3
2,025	35,515	2

Q-0045·Section: year-2008·14 ms·5 rows

Top infraction codes in 2008

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2868448,1) AS pct FROM tickets WHERE year=2008 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2008 — 5 rows
code	description	tickets	pct
5	PARK-HWY DRNG PROH TIMES/DAYS	605,901	21.1
29	PARK PROHIBITED TIME NO PERMIT	453,490	15.8
210	PARK FAIL TO DISPLAY RECEIPT	348,958	12.2
3	PARK/LEAVE ON PRIVATE PROPERTY	327,854	11.4
207	PARK FAIL TO DEP. FEE MACHINE	247,095	8.6

Q-0046·Section: year-2008·71 ms·5 rows

Most-ticketed street locations in 2008 (public streets)

SELECT loc2 AS street, count(*) AS tickets FROM tickets WHERE year=2008 AND is_public GROUP BY loc2 ORDER BY tickets DESC LIMIT 5

Most-ticketed street locations in 2008 (public streets) — 5 rows
street	tickets
20 EDWARD ST	6,098
JAMES ST	4,685
60 MURRAY ST	3,509
70 YORK ST	3,216
25 ST MARY ST	2,839

Q-0047·Section: year-2008·2 ms·1 row

2008 data-quality notes

SELECT rows, round(100*match_rate,1) AS match_pct, invalid_time, notes FROM years WHERE year=2008

2008 data-quality notes — 1 row
rows	match_pct	invalid_time	notes
2,868,448	92.9	3,511	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 3511 tickets have no valid time.

Q-0048·Section: year-2009·42 ms·1 row

Key figures for 2009 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2009

Key figures for 2009 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,009	2,774,553	113,314,385	2,868,448	-3.3

Q-0049·Section: year-2009·15 ms·24 rows

Tickets by hour of day in 2009

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2009
                group by h.hour order by h.hour

Tickets by hour of day in 2009 — first 20 of 24 rows
hour	tickets
0	145,315
1	103,657
2	111,907
3	110,248
4	76,259
5	18,689
6	18,998
7	55,008
8	110,689
9	164,366
10	139,176
11	193,274
12	207,001
13	203,107
14	146,121
15	173,698
16	172,981
17	122,477
18	99,286
19	117,149

Q-0050·Section: year-2009·18 ms·8 rows

Infraction family mix in 2009

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2009
                group by f.family, f.label order by tickets desc

Infraction family mix in 2009 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	685,870	24.7
meter	Unpaid meter / pay-and-display	597,676	21.5
permit	Permit parking	460,217	16.6
private_property	Private property	364,022	13.1
no_stopping	No stopping / rush hour	320,000	11.5
safety	Hydrant, fire route, accessible, sidewalk, bike lane	131,571	4.7
time_limit	Over the time limit	122,961	4.4
other	Other	92,236	3.3

Q-0051·Section: year-2008·14 ms·1 row

Average fine and daytime/overnight shares in 2008

SELECT round(avg(fine),2) AS avg_fine, round(100.0*sum(CASE WHEN hour BETWEEN 9 AND 16 THEN 1 ELSE 0 END)/count(hour),1) AS daytime_pct, round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS overnight_pct FROM tickets WHERE year=2008

Average fine and daytime/overnight shares in 2008 — 1 row
avg_fine	daytime_pct	overnight_pct
38.85	51	19.6

Q-0052·Section: year-2009·14 ms·5 rows

Top infraction codes in 2009

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2774553,1) AS pct FROM tickets WHERE year=2009 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2009 — 5 rows
code	description	tickets	pct
5	PARK HWY PROHIBED TIME/DAY	575,044	20.7
29	PARK PROHIBITED TIME NO PERMIT	453,272	16.3
3	PARK/LEAVE ON PRIVATE PROPERTY	354,649	12.8
210	PARK FAIL TO DISPLAY RECEIPT	340,051	12.3
207	PARK FAIL TO DEP. FEE MACHINE	225,850	8.1

Q-0053·Section: year-2009·60 ms·5 rows

Most-ticketed streets in 2009

WITH w AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS wt FROM tickets WHERE year=2009 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2009 AND segment_id_alt IS NOT NULL) SELECT s.street_name, round(sum(wt),0) AS tickets FROM w JOIN segments s ON s.segment_id=w.sid GROUP BY s.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets in 2009 — 5 rows
street_name	tickets
Bloor St W	74,433
Yonge St	68,323
Danforth Ave	63,442
Queen St W	48,038
King St W	37,890

Q-0054·Section: year-2009·1 ms·1 row

2009 data-quality notes

SELECT year, rows, round(100*match_rate,1) AS match_pct, invalid_time, notes FROM years WHERE year=2009

2009 data-quality notes — 1 row
year	rows	match_pct	invalid_time	notes
2,009	2,774,553	93.1	2,911	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 2911 tickets have no valid time.

Q-0055·Section: year-2010·31 ms·1 row

Key figures for 2010 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2010

Key figures for 2010 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,010	2,760,045	111,518,215	2,774,553	-0.5

Q-0056·Section: year-2010·15 ms·24 rows

Tickets by hour of day in 2010

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2010
                group by h.hour order by h.hour

Tickets by hour of day in 2010 — first 20 of 24 rows
hour	tickets
0	144,845
1	97,324
2	107,346
3	103,912
4	74,889
5	19,376
6	20,418
7	53,656
8	105,012
9	166,489
10	140,415
11	190,175
12	209,974
13	203,411
14	140,576
15	168,062
16	166,620
17	116,916
18	94,629
19	127,888

Q-0057·Section: year-2010·16 ms·8 rows

Infraction family mix in 2010

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2010
                group by f.family, f.label order by tickets desc

Infraction family mix in 2010 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	687,354	24.9
meter	Unpaid meter / pay-and-display	589,643	21.4
permit	Permit parking	470,795	17.1
private_property	Private property	350,243	12.7
no_stopping	No stopping / rush hour	326,721	11.8
time_limit	Over the time limit	123,228	4.5
safety	Hydrant, fire route, accessible, sidewalk, bike lane	121,352	4.4
other	Other	90,709	3.3

Q-0058·Section: year-2011·30 ms·1 row

Key figures for 2011 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2011

Key figures for 2011 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,011	2,805,492	114,226,595	2,760,045	1.6

Q-0059·Section: year-2011·12 ms·24 rows

Tickets by hour of day in 2011

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2011
                group by h.hour order by h.hour

Tickets by hour of day in 2011 — first 20 of 24 rows
hour	tickets
0	140,162
1	92,957
2	102,376
3	104,671
4	74,795
5	18,356
6	20,390
7	53,347
8	103,929
9	173,422
10	151,355
11	203,903
12	219,849
13	216,688
14	150,187
15	171,346
16	168,675
17	117,927
18	97,115
19	130,313

Q-0060·Section: year-2011·16 ms·8 rows

Infraction family mix in 2011

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2011
                group by f.family, f.label order by tickets desc

Infraction family mix in 2011 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	704,488	25.1
meter	Unpaid meter / pay-and-display	609,997	21.7
permit	Permit parking	460,928	16.4
no_stopping	No stopping / rush hour	347,773	12.4
private_property	Private property	344,943	12.3
safety	Hydrant, fire route, accessible, sidewalk, bike lane	126,593	4.5
time_limit	Over the time limit	118,846	4.2
other	Other	91,924	3.3

Q-0061·Section: year-2012·31 ms·1 row

Key figures for 2012 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2012

Key figures for 2012 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,012	2,746,154	111,485,565	2,805,492	-2.1

Q-0062·Section: year-2012·13 ms·24 rows

Tickets by hour of day in 2012

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2012
                group by h.hour order by h.hour

Tickets by hour of day in 2012 — first 20 of 24 rows
hour	tickets
0	133,571
1	86,971
2	99,221
3	98,049
4	68,261
5	16,671
6	24,407
7	55,896
8	106,645
9	181,299
10	156,203
11	200,864
12	218,454
13	205,380
14	147,148
15	169,355
16	166,552
17	118,605
18	93,230
19	123,192

Q-0063·Section: year-2012·17 ms·8 rows

Infraction family mix in 2012

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2012
                group by f.family, f.label order by tickets desc

Infraction family mix in 2012 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	697,988	25.4
meter	Unpaid meter / pay-and-display	594,477	21.6
permit	Permit parking	436,001	15.9
private_property	Private property	343,353	12.5
no_stopping	No stopping / rush hour	335,333	12.2
safety	Hydrant, fire route, accessible, sidewalk, bike lane	132,232	4.8
time_limit	Over the time limit	114,811	4.2
other	Other	91,959	3.3

Q-0064·Section: year-2010·15 ms·5 rows

Top infraction codes in 2010

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2760045,1) AS pct FROM tickets WHERE year=2010 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2010 — 5 rows
code	description	tickets	pct
5	PARK HWY PROHIBED TIME/DAY	573,781	20.8
29	PARK PROHIBITED TIME NO PERMIT	463,321	16.8
3	PARK/LEAVE ON PRIVATE PROPERTY	343,001	12.4
210	PARK FAIL TO DISPLAY RECEIPT	331,907	12
207	PARK FAIL TO DEP. FEE MACHINE	226,550	8.2

Q-0065·Section: year-2010·49 ms·5 rows

Most-ticketed streets in 2010 (primary segment)

SELECT s.street_name, round(sum(CASE WHEN t.segment_id_alt IS NULL THEN 1 ELSE 0.5 END),1) AS tickets FROM tickets t JOIN segments s ON s.segment_id=t.segment_id WHERE t.year=2010 GROUP BY s.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets in 2010 (primary segment) — 5 rows
street_name	tickets
Yonge St	70,048
Danforth Ave	60,857.5
Bloor St W	60,565
Queen St W	46,803.5
Queen St E	41,719

Q-0066·Section: year-2010·12 ms·1 row

Overnight and midday shares and geocode match rate 2010

SELECT round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS overnight_pct, round(100.0*sum(CASE WHEN hour BETWEEN 11 AND 13 THEN 1 ELSE 0 END)/count(hour),1) AS midday_pct, (SELECT match_rate FROM years WHERE year=2010) AS match_rate, round(100.0*(SELECT match_rate FROM years WHERE year=2010),1) AS match_pct FROM tickets WHERE year=2010

Overnight and midday shares and geocode match rate 2010 — 1 row
overnight_pct	midday_pct	match_rate	match_pct
19.2	21.9	0.94	93.5

Q-0067·Section: year-2012·14 ms·5 rows

Top infraction codes in 2012

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2746154,1) AS pct FROM tickets WHERE year=2012 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2012 — 5 rows
code	description	tickets	pct
5	PARK HWY PROHIBED TIME/DAY	569,772	20.7
29	PARK PROHIBITED TIME NO PERMIT	428,476	15.6
210	PARK FAIL TO DISPLAY RECEIPT	341,828	12.4
3	PARK/LEAVE ON PRIVATE PROPERTY	332,757	12.1
207	PARK FAIL TO DEP. FEE MACHINE	226,683	8.3

Q-0068·Section: year-2012·60 ms·5 rows

Most-ticketed locations (loc2) in 2012

SELECT loc2, count(*) AS tickets FROM tickets WHERE year=2012 AND loc2 IS NOT NULL GROUP BY loc2 ORDER BY tickets DESC LIMIT 5

Most-ticketed locations (loc2) in 2012 — 5 rows
loc2	tickets
1750 FINCH AVE E	7,669
20 EDWARD ST	6,123
2075 BAYVIEW AVE	5,937
941 PROGRESS AVE	3,930
JAMES ST	3,930

Q-0069·Section: year-2012·1 ms·1 row

Data-quality notes for 2012

SELECT match_rate, invalid_time, notes FROM years WHERE year=2012

Data-quality notes for 2012 — 1 row
match_rate	invalid_time	notes
0.94	1,728	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1728 tickets have no valid time.

Q-0070·Section: year-2011·14 ms·5 rows

Top infraction codes in 2011

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2805492,1) AS pct FROM tickets WHERE year=2011 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2011 — 5 rows
code	description	tickets	pct
5	PARK HWY PROHIBED TIME/DAY	582,359	20.8
29	PARK PROHIBITED TIME NO PERMIT	452,902	16.1
210	PARK FAIL TO DISPLAY RECEIPT	346,126	12.3
3	PARK/LEAVE ON PRIVATE PROPERTY	336,797	12
207	PARK FAIL TO DEP. FEE MACHINE	234,060	8.3

Q-0071·Section: year-2011·60 ms·5 rows

Most-ticketed locations (loc2) in 2011

SELECT loc2 AS street, count(*) AS tickets FROM tickets WHERE year=2011 AND loc2 IS NOT NULL GROUP BY loc2 ORDER BY tickets DESC LIMIT 5

Most-ticketed locations (loc2) in 2011 — 5 rows
street	tickets
1750 FINCH AVE E	7,905
20 EDWARD ST	6,993
2075 BAYVIEW AVE	6,496
2075 BAYVIEW AV	3,696
JAMES ST	2,962

Q-0072·Section: year-2011·42 ms·5 rows

Most-ticketed streets 2011 (geocoded)

WITH s AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2011 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2011 AND segment_id_alt IS NOT NULL), a AS (SELECT sg.street_name, sum(w) AS tickets FROM s JOIN segments sg ON sg.segment_id=s.sid GROUP BY sg.street_name) SELECT street_name, round(tickets,0) AS tickets FROM a ORDER BY tickets DESC LIMIT 5

Most-ticketed streets 2011 (geocoded) — 5 rows
street_name	tickets
Yonge St	85,677
Bloor St W	63,939
Danforth Ave	53,301
Queen St W	51,481
King St W	41,450

Q-0073·Section: year-2011·13 ms·1 row

Midday and overnight shares 2011, geocode rate

SELECT round(100.0*sum(CASE WHEN hour BETWEEN 11 AND 13 THEN 1 ELSE 0 END)/count(hour),1) AS midday_pct, round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS overnight_pct, (SELECT match_rate FROM years WHERE year=2011) AS match_rate, round(100.0*(SELECT match_rate FROM years WHERE year=2011),1) AS match_pct FROM tickets WHERE year=2011

Midday and overnight shares 2011, geocode rate — 1 row
midday_pct	overnight_pct	match_rate	match_pct
22.8	18.4	0.94	93.9

Q-0074·Section: year-2012·50 ms·5 rows

Most-ticketed streets (public tickets) 2012

SELECT s.street_name, round(sum(CASE WHEN t.segment_id_alt IS NULL THEN 1 ELSE 0.5 END),0) AS tickets FROM tickets t JOIN segments s ON s.segment_id=t.segment_id WHERE t.year=2012 AND t.is_public GROUP BY s.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets (public tickets) 2012 — 5 rows
street_name	tickets
Yonge St	73,921
Bloor St W	69,124
Queen St W	47,372
Danforth Ave	47,065
King St W	38,169

Q-0075·Section: year-2012·12 ms·1 row

Daytime and overnight shares 2012

SELECT sum(CASE WHEN hour BETWEEN 9 AND 16 THEN 1 ELSE 0 END) AS daytime, round(100.0*sum(CASE WHEN hour BETWEEN 9 AND 16 THEN 1 ELSE 0 END)/count(hour),1) AS daytime_pct, round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS overnight_pct FROM tickets WHERE year=2012

Daytime and overnight shares 2012 — 1 row
daytime	daytime_pct	overnight_pct
1,445,255	52.7	17.7

Q-0076·Section: year-2013·30 ms·1 row

Key figures for 2013 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2013

Key figures for 2013 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,013	2,613,156	106,554,180	2,746,154	-4.8

Q-0077·Section: year-2013·12 ms·24 rows

Tickets by hour of day in 2013

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2013
                group by h.hour order by h.hour

Tickets by hour of day in 2013 — first 20 of 24 rows
hour	tickets
0	118,289
1	80,691
2	92,535
3	92,210
4	69,806
5	22,844
6	29,303
7	57,624
8	103,164
9	173,520
10	150,386
11	196,623
12	211,014
13	190,714
14	135,255
15	158,090
16	155,050
17	110,354
18	87,484
19	118,997

Q-0078·Section: year-2013·16 ms·8 rows

Infraction family mix in 2013

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2013
                group by f.family, f.label order by tickets desc

Infraction family mix in 2013 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	663,867	25.4
meter	Unpaid meter / pay-and-display	572,711	21.9
permit	Permit parking	413,709	15.8
no_stopping	No stopping / rush hour	328,887	12.6
private_property	Private property	307,919	11.8
safety	Hydrant, fire route, accessible, sidewalk, bike lane	123,158	4.7
time_limit	Over the time limit	119,012	4.6
other	Other	83,893	3.2

Q-0079·Section: year-2014·31 ms·1 row

Key figures for 2014 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2014

Key figures for 2014 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,014	2,484,983	108,987,340	2,613,156	-4.9

Q-0080·Section: year-2014·14 ms·24 rows

Tickets by hour of day in 2014

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2014
                group by h.hour order by h.hour

Tickets by hour of day in 2014 — first 20 of 24 rows
hour	tickets
0	119,143
1	81,974
2	95,817
3	95,930
4	65,395
5	19,844
6	25,341
7	56,236
8	105,886
9	167,745
10	143,560
11	181,117
12	198,753
13	172,840
14	117,498
15	149,726
16	139,670
17	101,107
18	82,496
19	112,017

Q-0081·Section: year-2014·14 ms·8 rows

Infraction family mix in 2014

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2014
                group by f.family, f.label order by tickets desc

Infraction family mix in 2014 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	548,815	22.1
meter	Unpaid meter / pay-and-display	504,160	20.3
permit	Permit parking	398,189	16
private_property	Private property	316,593	12.7
no_stopping	No stopping / rush hour	307,661	12.4
other	Other	176,266	7.1
time_limit	Over the time limit	116,943	4.7
safety	Hydrant, fire route, accessible, sidewalk, bike lane	116,356	4.7

Q-0082·Section: year-2015·33 ms·1 row

Key figures for 2015 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2015

Key figures for 2015 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,015	2,168,493	99,872,770	2,484,983	-12.7

Q-0083·Section: year-2015·13 ms·24 rows

Tickets by hour of day in 2015

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2015
                group by h.hour order by h.hour

Tickets by hour of day in 2015 — first 20 of 24 rows
hour	tickets
0	99,123
1	66,049
2	84,602
3	88,472
4	59,845
5	15,968
6	18,438
7	53,502
8	98,197
9	145,524
10	116,956
11	145,813
12	168,138
13	146,644
14	106,547
15	127,492
16	135,444
17	94,080
18	78,592
19	99,568

Q-0084·Section: year-2015·14 ms·8 rows

Infraction family mix in 2015

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2015
                group by f.family, f.label order by tickets desc

Infraction family mix in 2015 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	460,571	21.2
meter	Unpaid meter / pay-and-display	389,123	17.9
private_property	Private property	330,806	15.3
permit	Permit parking	327,234	15.1
no_stopping	No stopping / rush hour	306,001	14.1
other	Other	144,514	6.7
safety	Hydrant, fire route, accessible, sidewalk, bike lane	106,733	4.9
time_limit	Over the time limit	103,511	4.8

Q-0085·Section: year-2013·16 ms·5 rows

Top infraction codes in 2013

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2613156,1) AS pct FROM tickets WHERE year=2013 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2013 — 5 rows
code	description	tickets	pct
5	PARK-HWY DRNG PROH TIMES/DAYS	544,940	20.9
29	PARK PROHIBITED TIME NO PERMIT	406,521	15.6
210	PARK FAIL TO DISPLAY RECEIPT	339,751	13
3	PARK/LEAVE ON PRIVATE PROPERTY	300,212	11.5
207	PARK FAIL TO DEP. FEE MACHINE	209,896	8

Q-0086·Section: year-2013·82 ms·5 rows

Most-ticketed locations (location2) in 2013

SELECT loc2, count(*) AS tickets FROM tickets WHERE year=2013 AND loc2 IS NOT NULL GROUP BY loc2 ORDER BY tickets DESC LIMIT 5

Most-ticketed locations (location2) in 2013 — 5 rows
loc2	tickets
2075 BAYVIEW AVE	8,269
20 EDWARD ST	6,612
JAMES ST	3,131
700 LAWRENCE AVE W	2,633
225 KING ST W	2,617

Q-0087·Section: year-2013·56 ms·5 rows

Most-ticketed streets in 2013 (matched segments)

WITH s AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2013 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2013 AND segment_id_alt IS NOT NULL), t AS (SELECT sg.street_name, sum(w) AS tickets FROM s JOIN segments sg ON sg.segment_id=s.sid GROUP BY sg.street_name) SELECT street_name, round(tickets,0) AS tickets FROM t ORDER BY tickets DESC LIMIT 5

Most-ticketed streets in 2013 (matched segments) — 5 rows
street_name	tickets
Yonge St	79,891
Bloor St W	68,038
Danforth Ave	46,884
Queen St W	44,999
Queen St E	37,975

Q-0088·Section: year-2014·15 ms·5 rows

Top infraction codes in 2014

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2484983,1) AS pct, round(sum(fine)) AS fines FROM tickets WHERE year=2014 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2014 — 5 rows
code	description	tickets	pct	fines
5	PARK-SIGNED HWY-PROHIBIT DY/TM	444,262	17.9	17,767,560
29	PARK PROHIBITED TIME NO PERMIT	389,870	15.7	11,695,830
210	PARK FAIL TO DISPLAY RECEIPT	317,909	12.8	9,537,270
3	PARK/LEAVE ON PRIVATE PROPERTY	309,275	12.4	9,277,620
207	PARK FAIL TO DEP. FEE MACHINE	162,767	6.6	4,883,010

Q-0089·Section: year-2014·30 ms·5 rows

Most-ticketed blocks in 2014

WITH t AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2014 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2014 AND segment_id_alt IS NOT NULL) SELECT s.street_name, s.from_street, s.to_street, round(sum(w)) AS tickets FROM t JOIN segments s ON s.segment_id=t.sid GROUP BY ALL ORDER BY tickets DESC LIMIT 5

Most-ticketed blocks in 2014 — 5 rows
street_name	from_street	to_street	tickets
Edward St	Yonge St	Bay St	8,372
Bayview Ave	Blythwood Rd	—	7,997
Mill St	Trinity St	Cherry St	7,237
Queen St W	Peter St	Spadina Ave	6,971
Yorkville Ave	Bellair St	Hazelton Ave	5,968

Q-0090·Section: year-2014·1 ms·1 row

Data quality for 2014

SELECT year, match_rate, invalid_time, notes, round(100.0*match_rate,1) AS match_pct FROM years WHERE year=2014

Data quality for 2014 — 1 row
year	match_rate	invalid_time	notes	match_pct
2,014	0.95	1,254	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1254 tickets have no valid time.	95

Q-0091·Section: year-2015·18 ms·5 rows

Top infraction codes in 2015

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2168493,1) AS pct, sum(fine) AS fines FROM tickets WHERE year=2015 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2015 — 5 rows
code	description	tickets	pct	fines
5	PARK-SIGNED HWY-PROHIBIT DY/TM	380,239	17.5	15,205,920
3	PARK ON PRIVATE PROPERTY	324,580	15	9,737,160
29	PARK PROHIBITED TIME NO PERMIT	321,312	14.8	9,639,360
210	PARK FAIL TO DISPLAY RECEIPT	244,569	11.3	7,337,070
207	PARK FAIL TO DEP. FEE MACHINE	123,488	5.7	3,704,640

Q-0092·Section: year-2015·27 ms·5 rows

Most-ticketed blocks in 2015

SELECT s.street_name, s.from_street, s.to_street, sum(CASE WHEN t.segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END) AS tickets FROM (SELECT segment_id AS sid, segment_id_alt FROM tickets WHERE year=2015 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt AS sid, segment_id_alt FROM tickets WHERE year=2015 AND segment_id_alt IS NOT NULL) t JOIN segments s ON s.segment_id=t.sid GROUP BY 1,2,3 ORDER BY tickets DESC LIMIT 5

Most-ticketed blocks in 2015 — 5 rows
street_name	from_street	to_street	tickets
Bayview Ave	Blythwood Rd	—	9,082
Edward St	Yonge St	Bay St	7,423
Dufferin St	Jane Osler Blvd	401 C E Dufferin St S Ramp	4,934
Mill St	Trinity St	Cherry St	4,278.5
Danforth Ave	Broadview Ave	Ellerbeck St	4,255.5

Q-0093·Section: year-2015·5 ms·1 row

2015 data quality and daytime share

SELECT year, rows, round(100*match_rate,1) AS match_pct, invalid_time, notes, round(100.0*(SELECT count(*) FROM tickets WHERE year=2015 AND hour BETWEEN 9 AND 16)/rows,1) AS daytime_pct FROM years WHERE year=2015

2015 data quality and daytime share — 1 row
year	rows	match_pct	invalid_time	notes	daytime_pct
2,015	2,168,493	95.4	1,430	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1430 tickets have no valid time.	50.4

Q-0094·Section: year-2016·31 ms·1 row

Key figures for 2016 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2016

Key figures for 2016 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,016	2,254,761	109,675,985	2,168,493	4

Q-0095·Section: year-2016·10 ms·24 rows

Tickets by hour of day in 2016

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2016
                group by h.hour order by h.hour

Tickets by hour of day in 2016 — first 20 of 24 rows
hour	tickets
0	101,979
1	73,298
2	92,834
3	96,295
4	69,995
5	16,030
6	21,562
7	61,110
8	108,865
9	156,531
10	128,078
11	152,315
12	163,886
13	148,357
14	105,527
15	122,516
16	134,983
17	94,342
18	77,502
19	95,925

Q-0096·Section: year-2016·17 ms·8 rows

Infraction family mix in 2016

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2016
                group by f.family, f.label order by tickets desc

Infraction family mix in 2016 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	483,619	21.4
private_property	Private property	374,205	16.6
meter	Unpaid meter / pay-and-display	368,677	16.4
permit	Permit parking	344,654	15.3
no_stopping	No stopping / rush hour	311,724	13.8
other	Other	149,066	6.6
time_limit	Over the time limit	116,502	5.2
safety	Hydrant, fire route, accessible, sidewalk, bike lane	106,314	4.7

Q-0097·Section: year-2017·30 ms·1 row

Key figures for 2017 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2017

Key figures for 2017 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,017	2,144,226	106,200,060	2,254,761	-4.9

Q-0098·Section: year-2017·11 ms·24 rows

Tickets by hour of day in 2017

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2017
                group by h.hour order by h.hour

Tickets by hour of day in 2017 — first 20 of 24 rows
hour	tickets
0	95,198
1	71,117
2	97,787
3	99,851
4	71,963
5	15,925
6	20,795
7	64,432
8	108,250
9	155,525
10	126,605
11	141,304
12	150,298
13	140,308
14	107,367
15	110,915
16	122,526
17	85,123
18	72,796
19	82,117

Q-0099·Section: year-2017·14 ms·8 rows

Infraction family mix in 2017

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2017
                group by f.family, f.label order by tickets desc

Infraction family mix in 2017 — 8 rows
family	label	tickets	pct
no_parking	No parking (signed hours)	456,801	21.3
private_property	Private property	379,654	17.7
permit	Permit parking	342,995	16
no_stopping	No stopping / rush hour	312,331	14.6
meter	Unpaid meter / pay-and-display	277,978	13
other	Other	154,537	7.2
time_limit	Over the time limit	123,242	5.7
safety	Hydrant, fire route, accessible, sidewalk, bike lane	96,688	4.5

Q-0100·Section: year-2016·15 ms·5 rows

Top infraction codes in 2016

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/sum(count(*)) over (),1) AS pct FROM tickets WHERE year=2016 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2016 — 5 rows
code	description	tickets	pct
5	PARK-SIGNED HWY-PROHIBIT DY/TM	401,636	17.8
3	PARK ON PRIVATE PROPERTY	367,638	16.3
29	PARK PROHIBITED TIME NO PERMIT	339,145	15
210	PARK FAIL TO DISPLAY RECEIPT	213,327	9.5
207	PARK MACHINE-REQD FEE NOT PAID	131,511	5.8

Q-0101·Section: year-2016·51 ms·5 rows

Most-ticketed locations (location2) in 2016

SELECT loc2, count(*) AS tickets FROM tickets WHERE year=2016 AND loc2 IS NOT NULL GROUP BY loc2 ORDER BY tickets DESC LIMIT 5

Most-ticketed locations (location2) in 2016 — 5 rows
loc2	tickets
2075 BAYVIEW AVE	6,463
20 EDWARD ST	5,455
1 BRIMLEY RD S	3,990
JAMES ST	3,635
3401 DUFFERIN ST	3,608

Q-0102·Section: year-2016·2 ms·1 row

Data-quality notes for 2016

SELECT match_rate, invalid_time, notes FROM years WHERE year=2016

Data-quality notes for 2016 — 1 row
match_rate	invalid_time	notes
0.96	1,329	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1329 tickets have no valid time.

Q-0103·Section: year-2018·36 ms·1 row

Key figures for 2018 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2018

Key figures for 2018 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,018	2,044,288	100,206,695	2,144,226	-4.7

Q-0104·Section: year-2018·10 ms·24 rows

Tickets by hour of day in 2018

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2018
                group by h.hour order by h.hour

Tickets by hour of day in 2018 — first 20 of 24 rows
hour	tickets
0	85,178
1	67,612
2	89,433
3	96,616
4	72,093
5	17,507
6	21,647
7	60,992
8	102,307
9	147,510
10	125,219
11	137,883
12	135,513
13	128,941
14	100,915
15	111,393
16	118,959
17	86,728
18	74,150
19	80,920

Q-0105·Section: year-2018·19 ms·8 rows

Infraction family mix in 2018

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2018
                group by f.family, f.label order by tickets desc

Infraction family mix in 2018 — 8 rows
family	label	tickets	pct
private_property	Private property	406,381	19.9
no_parking	No parking (signed hours)	404,371	19.8
permit	Permit parking	305,131	14.9
no_stopping	No stopping / rush hour	292,343	14.3
meter	Unpaid meter / pay-and-display	260,514	12.7
other	Other	174,648	8.5
time_limit	Over the time limit	113,138	5.5
safety	Hydrant, fire route, accessible, sidewalk, bike lane	87,762	4.3

Q-0106·Section: year-2016·39 ms·5 rows

Most-ticketed streets in 2016 (segment-matched)

WITH t AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2016 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2016 AND segment_id_alt IS NOT NULL) SELECT s.street_name, round(sum(w))::BIGINT AS tickets FROM t JOIN segments s ON s.segment_id=t.sid GROUP BY s.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets in 2016 (segment-matched) — 5 rows
street_name	tickets
Yonge St	63,974
Bloor St W	38,969
Queen St W	36,355
Danforth Ave	35,209
King St W	28,553

Q-0107·Section: year-2017·15 ms·5 rows

Top infraction codes in 2017

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2144226,1) AS pct, round(sum(fine)) AS fines FROM tickets WHERE year=2017 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2017 — 5 rows
code	description	tickets	pct	fines
5	PARK-SIGNED HWY-PROHIBIT DY/TM	379,414	17.7	18,969,050
3	PARK ON PRIVATE PROPERTY	368,888	17.2	11,066,010
29	PARK PROHIBITED TIME NO PERMIT	333,732	15.6	10,011,900
207	PARK MACHINE-REQD FEE NOT PAID	252,267	11.8	7,568,010
2	PARK - LONGER THAN 3 HOURS	123,238	5.7	1,848,570

Q-0108·Section: year-2017·32 ms·5 rows

Most-ticketed blocks in 2017

WITH w AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS wt FROM tickets WHERE year=2017 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2017 AND segment_id_alt IS NOT NULL) SELECT s.street_name, s.from_street, s.to_street, round(sum(wt)) AS tickets FROM w JOIN segments s ON s.segment_id=w.sid GROUP BY ALL ORDER BY tickets DESC LIMIT 5

Most-ticketed blocks in 2017 — 5 rows
street_name	from_street	to_street	tickets
Edward St	Yonge St	Bay St	8,984
Bayview Ave	Blythwood Rd	—	8,150
Mavety St	Annette St	Ln S Dundas W Keele	4,896
University Ave	Armoury St	Dundas St W	4,866
Queen St W	Peter St	Spadina Ave	4,415

Q-0109·Section: year-2017·2 ms·1 row

2017 data-quality notes

SELECT match_rate, invalid_time, notes, round(100.0*match_rate,1) AS match_pct FROM years WHERE year=2017

2017 data-quality notes — 1 row
match_rate	invalid_time	notes	match_pct
0.96	1,377	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1377 tickets have no valid time.	95.8

Q-0110·Section: year-2018·14 ms·5 rows

Top infraction codes in 2018

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2044288,1) AS pct, sum(fine) AS fines FROM tickets WHERE year=2018 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2018 — 5 rows
code	description	tickets	pct	fines
3	PARK ON PRIVATE PROPERTY	393,469	19.2	11,803,545
5	PARK-SIGNED HWY-PROHIBIT DY/TM	337,282	16.5	16,863,400
29	PARK PROHIBITED TIME NO PERMIT	298,488	14.6	8,954,610
207	PARK MACHINE-REQD FEE NOT PAID	231,652	11.3	6,949,560
2	PARK - LONGER THAN 3 HOURS	113,134	5.5	1,696,995

Q-0111·Section: year-2018·35 ms·5 rows

Most-ticketed streets 2018 (public tickets)

WITH s AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2018 AND is_public AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2018 AND is_public AND segment_id_alt IS NOT NULL) SELECT g.street_name, round(sum(w),0) AS tickets FROM s JOIN segments g ON g.segment_id=s.sid GROUP BY g.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets 2018 (public tickets) — 5 rows
street_name	tickets
Yonge St	36,360
Queen St W	34,968
Bloor St W	27,440
Danforth Ave	23,719
Queen St E	18,578

Q-0112·Section: year-2018·19 ms·1 row

2018 data quality and hour shares

SELECT rows, round(100*match_rate,1) AS match_pct, invalid_time, notes, (SELECT round(100.0*count(*) FILTER (WHERE hour BETWEEN 0 AND 4)/count(hour),1) FROM tickets WHERE year=2018) AS overnight_pct, (SELECT round(100.0*count(*) FILTER (WHERE hour BETWEEN 9 AND 16)/count(hour),1) FROM tickets WHERE year=2018) AS daytime_pct FROM years WHERE year=2018

2018 data quality and hour shares — 1 row
rows	match_pct	invalid_time	notes	overnight_pct	daytime_pct
2,044,288	95.4	1,549	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1549 tickets have no valid time.	20.1	49.3

Q-0113·Section: year-2019·30 ms·1 row

Key figures for 2019 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2019

Key figures for 2019 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,019	2,213,939	108,424,170	2,044,288	8.3

Q-0114·Section: year-2019·11 ms·24 rows

Tickets by hour of day in 2019

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2019
                group by h.hour order by h.hour

Tickets by hour of day in 2019 — first 20 of 24 rows
hour	tickets
0	81,851
1	74,167
2	97,728
3	111,486
4	86,101
5	20,548
6	25,440
7	62,257
8	99,729
9	143,072
10	131,865
11	159,139
12	146,461
13	142,870
14	103,799
15	118,975
16	127,842
17	94,261
18	84,936
19	90,761

Q-0115·Section: year-2019·15 ms·8 rows

Infraction family mix in 2019

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2019
                group by f.family, f.label order by tickets desc

Infraction family mix in 2019 — 8 rows
family	label	tickets	pct
private_property	Private property	477,746	21.6
no_parking	No parking (signed hours)	409,368	18.5
meter	Unpaid meter / pay-and-display	321,070	14.5
permit	Permit parking	288,797	13
no_stopping	No stopping / rush hour	288,056	13
other	Other	193,479	8.7
time_limit	Over the time limit	144,394	6.5
safety	Hydrant, fire route, accessible, sidewalk, bike lane	91,029	4.1

Q-0116·Section: year-2020·39 ms·1 row

Key figures for 2020 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2020

Key figures for 2020 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,020	1,403,663	70,555,350	2,213,939	-36.6

Q-0117·Section: year-2020·10 ms·24 rows

Tickets by hour of day in 2020

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2020
                group by h.hour order by h.hour

Tickets by hour of day in 2020 — first 20 of 24 rows
hour	tickets
0	45,284
1	50,171
2	70,854
3	78,220
4	56,031
5	16,911
6	15,774
7	33,560
8	62,061
9	84,291
10	83,700
11	104,130
12	98,944
13	100,427
14	77,026
15	69,013
16	69,974
17	61,333
18	50,866
19	51,175

Q-0118·Section: year-2020·16 ms·8 rows

Infraction family mix in 2020

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2020
                group by f.family, f.label order by tickets desc

Infraction family mix in 2020 — 8 rows
family	label	tickets	pct
private_property	Private property	377,069	26.9
no_parking	No parking (signed hours)	252,902	18
meter	Unpaid meter / pay-and-display	224,206	16
permit	Permit parking	166,052	11.8
no_stopping	No stopping / rush hour	158,301	11.3
other	Other	97,334	6.9
time_limit	Over the time limit	67,400	4.8
safety	Hydrant, fire route, accessible, sidewalk, bike lane	60,399	4.3

Q-0119·Section: year-2021·30 ms·1 row

Key figures for 2021 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2021

Key figures for 2021 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,021	1,481,709	81,688,410	1,403,663	5.6

Q-0120·Section: year-2021·9 ms·24 rows

Tickets by hour of day in 2021

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2021
                group by h.hour order by h.hour

Tickets by hour of day in 2021 — first 20 of 24 rows
hour	tickets
0	45,101
1	56,523
2	78,702
3	82,231
4	58,003
5	15,502
6	15,668
7	32,573
8	65,272
9	88,596
10	96,094
11	121,380
12	114,934
13	105,851
14	83,973
15	59,883
16	61,247
17	63,881
18	57,588
19	54,544

Q-0121·Section: year-2021·10 ms·8 rows

Infraction family mix in 2021

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2021
                group by f.family, f.label order by tickets desc

Infraction family mix in 2021 — 8 rows
family	label	tickets	pct
private_property	Private property	379,812	25.6
no_parking	No parking (signed hours)	249,533	16.8
meter	Unpaid meter / pay-and-display	218,952	14.8
no_stopping	No stopping / rush hour	205,041	13.8
permit	Permit parking	184,248	12.4
safety	Hydrant, fire route, accessible, sidewalk, bike lane	90,611	6.1
time_limit	Over the time limit	78,935	5.3
other	Other	74,577	5

Q-0122·Section: year-2020·11 ms·5 rows

Top infraction codes in 2020

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/1403663,1) AS pct FROM tickets WHERE year=2020 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2020 — 5 rows
code	description	tickets	pct
3	PARK ON PRIVATE PROPERTY	338,199	24.1
207	PARK MACHINE-REQD FEE NOT PAID	215,036	15.3
5	PARK-SIGNED HWY-PROHIBIT DY/TM	204,658	14.6
29	PARK PROHIBITED TIME NO PERMIT	161,727	11.5
9	STOP-SIGNED HWY-PROHIBIT TM/DY	69,285	4.9

Q-0123·Section: year-2020·27 ms·5 rows

Most-ticketed streets (public) in 2020

SELECT s.street_name, round(sum(CASE WHEN t.segment_id_alt IS NULL THEN 1 ELSE 0.5 END),0) AS tickets FROM tickets t JOIN segments s ON s.segment_id=t.segment_id WHERE t.year=2020 AND t.is_public GROUP BY s.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets (public) in 2020 — 5 rows
street_name	tickets
Queen St W	22,872
Yonge St	19,023
Bloor St W	16,042
Dundas St W	12,250
Danforth Ave	12,142

Q-0124·Section: year-2020·2 ms·2 rows

Data-quality notes for 2019-2020

SELECT year, rows, round(100*match_rate,1) AS match_pct, invalid_time, notes FROM years WHERE year IN (2019,2020)

Data-quality notes for 2019-2020 — 2 rows
year	rows	match_pct	invalid_time	notes
2,019	2,213,939	95.2	1,959	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1959 tickets have no valid time.
2,020	1,403,663	95.2	1,300	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 1300 tickets have no valid time.

Q-0125·Section: year-2019·14 ms·5 rows

Top infraction codes in 2019

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2213939,1) AS pct FROM tickets WHERE year=2019 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2019 — 5 rows
code	description	tickets	pct
3	PARK ON PRIVATE PROPERTY	456,389	20.6
5	PARK-SIGNED HWY-PROHIBIT DY/TM	334,575	15.1
207	PARK MACHINE-REQD FEE NOT PAID	286,578	12.9
29	PARK PROHIBITED TIME NO PERMIT	280,619	12.7
2	PARK - LONGER THAN 3 HOURS	144,374	6.5

Q-0126·Section: year-2019·22 ms·5 rows

Most-ticketed public street blocks in 2019

SELECT s.street_name, s.from_street, s.to_street, sum(CASE WHEN t.segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END) AS tickets FROM (SELECT segment_id AS sid, segment_id_alt FROM tickets WHERE year=2019 AND is_public AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt AS sid, segment_id_alt FROM tickets WHERE year=2019 AND is_public AND segment_id_alt IS NOT NULL) t JOIN segments s ON s.segment_id=t.sid GROUP BY 1,2,3 ORDER BY tickets DESC LIMIT 5

Most-ticketed public street blocks in 2019 — 5 rows
street_name	from_street	to_street	tickets
Yorkville Ave	Bellair St	Hazelton Ave	6,536
Queen St W	Peter St	Spadina Ave	6,259
Edward St	Yonge St	Bay St	6,107
University Ave	Armoury St	Dundas St W	5,453
Bloor St W	Avenue Rd	Bedford Rd	4,772

Q-0127·Section: year-2019·11 ms·1 row

Hour-band shares and average fine 2019

SELECT round(100.0*sum(CASE WHEN hour BETWEEN 9 AND 13 THEN 1 ELSE 0 END)/count(hour),1) AS pct_9_to_14, round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS pct_0_to_5, count(*)-count(hour) AS no_time, round(108424170.0/2213939,2) AS avg_fine FROM tickets WHERE year=2019

Hour-band shares and average fine 2019 — 1 row
pct_9_to_14	pct_0_to_5	no_time	avg_fine
32.7	20.4	1,959	48.97

Q-0128·Section: year-2021·12 ms·5 rows

Top infraction codes in 2021

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/1481709,1) AS pct, round(sum(fine)) AS fines FROM tickets WHERE year=2021 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2021 — 5 rows
code	description	tickets	pct	fines
3	PARK ON PRIVATE PROPERTY	322,893	21.8	9,686,490
5	PARK-SIGNED HWY-PROHIBIT DY/TM	198,678	13.4	9,933,550
29	PARK PROHIBITED TIME NO PERMIT	184,102	12.4	5,523,060
207	PARK MACHINE-REQD FEE NOT PAID	180,821	12.2	5,424,630
9	STOP-SIGNED HWY-PROHIBIT TM/DY	101,236	6.8	10,123,500

Q-0129·Section: year-2021·23 ms·5 rows

Most-ticketed blocks in 2021

WITH w AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS wt FROM tickets WHERE year=2021 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2021 AND segment_id_alt IS NOT NULL) SELECT s.street_name, s.from_street, s.to_street, round(sum(wt)) AS tickets FROM w JOIN segments s ON s.segment_id=w.sid GROUP BY ALL ORDER BY tickets DESC LIMIT 5

Most-ticketed blocks in 2021 — 5 rows
street_name	from_street	to_street	tickets
Bayview Ave	Blythwood Rd	—	5,322
Grand Trunk Cres	Lower Simcoe St	Bremner Blvd	5,124
Keele St	The Pond Rd	York Blvd	4,454
University Ave	Armoury St	Dundas St W	4,421
Leslie St	Esther Shiner Blvd	Old Leslie Ny Hospital Ramp	4,372

Q-0130·Section: year-2021·11 ms·1 row

Hour-band shares and geocode match rate 2021

SELECT round(100.0*sum(CASE WHEN hour BETWEEN 9 AND 13 THEN 1 ELSE 0 END)/count(hour),1) AS pct_9_to_14, round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS pct_0_to_5, (SELECT match_rate FROM years WHERE year=2021) AS match_rate, round(100.0*(SELECT match_rate FROM years WHERE year=2021),1) AS match_pct FROM tickets WHERE year=2021

Hour-band shares and geocode match rate 2021 — 1 row
pct_9_to_14	pct_0_to_5	match_rate	match_pct
35.6	21.6	0.96	95.7

Q-0131·Section: year-2022·37 ms·1 row

Key figures for 2022 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2022

Key figures for 2022 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,022	1,821,887	96,347,560	1,481,709	23

Q-0132·Section: year-2022·10 ms·24 rows

Tickets by hour of day in 2022

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2022
                group by h.hour order by h.hour

Tickets by hour of day in 2022 — first 20 of 24 rows
hour	tickets
0	70,593
1	73,789
2	87,875
3	91,032
4	67,403
5	10,318
6	29,650
7	57,022
8	87,435
9	113,901
10	112,872
11	138,908
12	127,654
13	114,917
14	84,210
15	82,244
16	90,498
17	78,070
18	70,789
19	72,069

Q-0133·Section: year-2022·14 ms·8 rows

Infraction family mix in 2022

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2022
                group by f.family, f.label order by tickets desc

Infraction family mix in 2022 — 8 rows
family	label	tickets	pct
private_property	Private property	421,533	23.1
meter	Unpaid meter / pay-and-display	345,570	19
no_parking	No parking (signed hours)	307,509	16.9
permit	Permit parking	272,333	14.9
no_stopping	No stopping / rush hour	234,707	12.9
time_limit	Over the time limit	86,656	4.8
safety	Hydrant, fire route, accessible, sidewalk, bike lane	76,913	4.2
other	Other	76,666	4.2

Q-0134·Section: year-2023·44 ms·1 row

Key figures for 2023 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2023

Key figures for 2023 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,023	2,256,360	122,409,230	1,821,887	23.8

Q-0135·Section: year-2023·12 ms·24 rows

Tickets by hour of day in 2023

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2023
                group by h.hour order by h.hour

Tickets by hour of day in 2023 — first 20 of 24 rows
hour	tickets
0	84,848
1	87,492
2	101,555
3	109,205
4	85,210
5	22,061
6	34,963
7	63,588
8	100,358
9	137,277
10	133,838
11	163,563
12	158,550
13	138,580
14	104,503
15	104,911
16	118,375
17	100,220
18	92,322
19	96,174

Q-0136·Section: year-2023·15 ms·8 rows

Infraction family mix in 2023

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2023
                group by f.family, f.label order by tickets desc

Infraction family mix in 2023 — 8 rows
family	label	tickets	pct
private_property	Private property	485,006	21.5
meter	Unpaid meter / pay-and-display	427,495	18.9
no_parking	No parking (signed hours)	382,216	16.9
permit	Permit parking	347,843	15.4
no_stopping	No stopping / rush hour	317,033	14.1
time_limit	Over the time limit	109,484	4.9
other	Other	95,397	4.2
safety	Hydrant, fire route, accessible, sidewalk, bike lane	91,886	4.1

Q-0137·Section: year-2024·34 ms·1 row

Key figures for 2024 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2024

Key figures for 2024 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,024	2,147,159	145,978,460	2,256,360	-4.8

Q-0138·Section: year-2024·10 ms·24 rows

Tickets by hour of day in 2024

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2024
                group by h.hour order by h.hour

Tickets by hour of day in 2024 — first 20 of 24 rows
hour	tickets
0	80,033
1	81,933
2	96,688
3	105,115
4	81,873
5	20,780
6	33,443
7	58,558
8	98,215
9	140,464
10	131,018
11	165,688
12	160,792
13	138,835
14	103,592
15	99,746
16	105,430
17	89,249
18	81,111
19	84,983

Q-0139·Section: year-2024·14 ms·8 rows

Infraction family mix in 2024

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2024
                group by f.family, f.label order by tickets desc

Infraction family mix in 2024 — 8 rows
family	label	tickets	pct
private_property	Private property	411,881	19.2
meter	Unpaid meter / pay-and-display	387,761	18.1
no_parking	No parking (signed hours)	378,662	17.6
permit	Permit parking	328,695	15.3
no_stopping	No stopping / rush hour	312,614	14.6
time_limit	Over the time limit	128,144	6
other	Other	99,986	4.7
safety	Hydrant, fire route, accessible, sidewalk, bike lane	99,416	4.6

Q-0140·Section: year-2022·14 ms·5 rows

Top infraction codes in 2022

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/1821887,1) AS pct FROM tickets WHERE year=2022 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2022 — 5 rows
code	description	tickets	pct
3	PARK ON PRIVATE PROPERTY	378,837	20.8
207	PARK MACHINE-REQD FEE NOT PAID	284,719	15.6
29	PARK PROHIBITED TIME NO PERMIT	263,451	14.5
5	PARK-SIGNED HWY-PROHIBIT DY/TM	258,854	14.2
9	STOP-SIGNED HWY-PROHIBIT TM/DY	98,682	5.4

Q-0141·Section: year-2022·35 ms·5 rows

Most-ticketed streets in 2022 (public streets)

WITH w AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS wt FROM tickets WHERE year=2022 AND segment_id IS NOT NULL AND is_public UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2022 AND segment_id_alt IS NOT NULL AND is_public) SELECT s.street_name, round(sum(wt),0) AS tickets FROM w JOIN segments s ON s.segment_id=w.sid GROUP BY s.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets in 2022 (public streets) — 5 rows
street_name	tickets
Queen St W	35,592
Yonge St	24,472
Danforth Ave	20,789
Bloor St W	19,171
Dundas St W	18,931

Q-0142·Section: year-2022·1 ms·1 row

Data-quality notes for 2022

SELECT year, match_rate, invalid_time, notes, round(100.0*match_rate,1) AS match_pct FROM years WHERE year=2022

Data-quality notes for 2022 — 1 row
year	match_rate	invalid_time	notes	match_pct
2,022	0.96	963	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 963 tickets have no valid time.	96.2

Q-0143·Section: year-2023·20 ms·5 rows

Top infraction codes in 2023

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/sum(count(*)) over (),1) AS pct, sum(fine) AS fines FROM tickets WHERE year=2023 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2023 — 5 rows
code	description	tickets	pct	fines
3	PARK ON PRIVATE PROPERTY	435,620	19.3	14,403,855
207	PARK MACHINE-REQD FEE NOT PAID	366,917	16.3	11,007,510
29	PARK PROHIBITED TIME NO PERMIT	331,429	14.7	9,942,870
5	PARK-SIGNED HWY-PROHIBIT DY/TM	317,607	14.1	15,880,250
9	STOP-SIGNED HWY-PROHIBIT TM/DY	130,968	5.8	13,096,700

Q-0144·Section: year-2023·34 ms·5 rows

Most-ticketed blocks in 2023

WITH t AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2023 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2023 AND segment_id_alt IS NOT NULL) SELECT s.street_name, s.from_street, s.to_street, round(sum(w),0) AS tickets FROM t JOIN segments s ON s.segment_id=t.sid GROUP BY 1,2,3 ORDER BY tickets DESC LIMIT 5

Most-ticketed blocks in 2023 — 5 rows
street_name	from_street	to_street	tickets
Keele St	The Pond Rd	York Blvd	40,580
Yorkville Ave	Bellair St	Hazelton Ave	8,352
Edward St	Yonge St	Bay St	8,149
Cumberland St	Bellair St	—	5,782
Queen St W	Peter St	Spadina Ave	5,455

Q-0145·Section: year-2023·2 ms·2 rows

Data-quality notes for 2022-2023

SELECT year, rows, round(100*match_rate,1) AS match_pct, invalid_time, notes FROM years WHERE year IN (2022,2023)

Data-quality notes for 2022-2023 — 2 rows
year	rows	match_pct	invalid_time	notes
2,022	1,821,887	96.2	963	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 963 tickets have no valid time.
2,023	2,256,360	96.2	696	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 696 tickets have no valid time.

Q-0146·Section: year-2024·15 ms·5 rows

Top infraction codes in 2024

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/2147159,1) AS pct, sum(fine) AS fines FROM tickets WHERE year=2024 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2024 — 5 rows
code	description	tickets	pct	fines
3	PARK ON PRIVATE PROPERTY	365,060	17	27,378,150
207	PARK MACHINE-REQD FEE NOT PAID	329,296	15.3	12,051,560
29	PARK PROHIBITED TIME NO PERMIT	319,967	14.9	11,369,355
5	PARK-SIGNED HWY-PROHIBIT DY/TM	312,790	14.6	17,415,950
9	STOP-SIGNED HWY-PROHIBIT TM/DY	134,151	6.2	14,405,120

Q-0147·Section: year-2024·28 ms·5 rows

Most-ticketed blocks in 2024

WITH w AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS wt FROM tickets WHERE year=2024 AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2024 AND segment_id_alt IS NOT NULL) SELECT s.street_name, s.from_street, s.to_street, round(sum(wt),0) AS tickets FROM w JOIN segments s ON s.segment_id=w.sid GROUP BY ALL ORDER BY tickets DESC LIMIT 5

Most-ticketed blocks in 2024 — 5 rows
street_name	from_street	to_street	tickets
Keele St	The Pond Rd	York Blvd	35,691
Yorkville Ave	Bellair St	Hazelton Ave	8,059
Edward St	Yonge St	Bay St	6,299
Cumberland St	Bellair St	—	5,866
Thorncliffe Park Dr	—	—	5,728

Q-0148·Section: year-2024·1 ms·1 row

Data quality notes for 2024

SELECT year, rows, round(100*match_rate,1) AS match_pct, invalid_time, notes FROM years WHERE year=2024

Data quality notes for 2024 — 1 row
year	rows	match_pct	invalid_time	notes
2,024	2,147,159	96.5	252	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 252 tickets have no valid time.

Q-0149·Section: year-2023·17 ms·1 row

Overnight and late-morning shares; private-property share on top block

SELECT round(100.0*count(*) FILTER (WHERE hour BETWEEN 0 AND 4)/count(hour),1) AS overnight_pct, round(100.0*count(*) FILTER (WHERE hour BETWEEN 9 AND 13)/count(hour),1) AS late_morning_pct, (SELECT round(100.0*count(*) FILTER (WHERE family='private_property')/count(*),1) FROM tickets WHERE year=2023 AND segment_id=(SELECT segment_id FROM segments WHERE street_name='Keele St' AND from_street='The Pond Rd' AND to_street='York Blvd' LIMIT 1)) AS keele_private_pct FROM tickets WHERE year=2023

Overnight and late-morning shares; private-property share on top block — 1 row
overnight_pct	late_morning_pct	keele_private_pct
20.8	32.4	98.7

Q-0150·Section: year-2025·37 ms·1 row

Key figures for 2025 and the change from the previous year

with y as (select year, count(*) as tickets, cast(sum(fine) as bigint) as fines_total
                           from tickets group by year)
                select year, tickets, fines_total, lag(tickets) over (order by year) as prev_tickets,
                       round(100.0 * (tickets - lag(tickets) over (order by year))
                             / lag(tickets) over (order by year), 1) as change_pct
                from y qualify year = 2025

Key figures for 2025 and the change from the previous year — 1 row
year	tickets	fines_total	prev_tickets	change_pct
2,025	2,051,175	164,705,020	2,147,159	-4.5

Q-0151·Section: year-2025·10 ms·24 rows

Tickets by hour of day in 2025

select h.hour::integer as hour, count(t.hour) as tickets
                from range(24) as h(hour) left join tickets t on t.hour = h.hour and t.year = 2025
                group by h.hour order by h.hour

Tickets by hour of day in 2025 — first 20 of 24 rows
hour	tickets
0	68,302
1	68,582
2	87,914
3	98,680
4	79,320
5	19,832
6	32,653
7	55,998
8	91,971
9	134,826
10	123,741
11	153,776
12	157,398
13	140,095
14	103,711
15	104,395
16	110,014
17	86,507
18	77,791
19	83,296

Q-0152·Section: year-2025·14 ms·8 rows

Infraction family mix in 2025

select f.family, f.label, count(*) as tickets,
                       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
                from tickets t join families f using (family) where t.year = 2025
                group by f.family, f.label order by tickets desc

Infraction family mix in 2025 — 8 rows
family	label	tickets	pct
private_property	Private property	435,711	21.2
no_parking	No parking (signed hours)	343,650	16.8
meter	Unpaid meter / pay-and-display	340,656	16.6
no_stopping	No stopping / rush hour	321,923	15.7
permit	Permit parking	273,629	13.3
time_limit	Over the time limit	139,565	6.8
safety	Hydrant, fire route, accessible, sidewalk, bike lane	100,345	4.9
other	Other	95,696	4.7

Q-0153·Section: year-2025·14 ms·5 rows

Top infraction codes in 2025

SELECT code, any_value(description) AS description, count(*) AS tickets, round(100.0*count(*)/sum(count(*)) over (),1) AS pct FROM tickets WHERE year=2025 GROUP BY code ORDER BY tickets DESC LIMIT 5

Top infraction codes in 2025 — 5 rows
code	description	tickets	pct
3	PARK ON PRIVATE PROPERTY	387,901	18.9
5	PARK-SIGNED HWY-PROHIBIT DY/TM	286,967	14
29	PARK PROHIBITED TIME NO PERMIT	273,337	13.3
207	PARK MACHINE-REQD FEE NOT PAID	270,929	13.2
9	STOP-SIGNED HWY-PROHIBIT TM/DY	144,105	7

Q-0154·Section: year-2025·41 ms·5 rows

Most-ticketed public street locations in 2025

SELECT loc2 AS street, count(*) AS tickets FROM tickets WHERE year=2025 AND is_public GROUP BY loc2 ORDER BY tickets DESC LIMIT 5

Most-ticketed public street locations in 2025 — 5 rows
street	tickets
81 BAY ST	3,425
160 MARINE PARADE DR	3,031
15 MARINE PARADE DR	2,839
115 DANFORTH AVE	2,481
1 BRIMLEY ROAD SOUTH	2,393

Q-0155·Section: year-2025·3 ms·1 row

Data-quality notes for 2025

SELECT year, rows, match_rate, invalid_time, notes, (SELECT max(date) FROM tickets WHERE year=2025) AS last_date FROM years WHERE year=2025

Data-quality notes for 2025 — 1 row
year	rows	match_rate	invalid_time	notes	last_date
2,025	2,051,175	0.96	422	Complete records only: incomplete City records (e.g. out-of-province plates, tickets paid before data entry) are not in the open data; the City calls the impact on trends insignificant. 422 tickets have no valid time.	2025-12-31

Q-0156·Section: year-2025·35 ms·5 rows

Most-ticketed streets (public) in 2025

WITH s AS (SELECT segment_id AS sid, CASE WHEN segment_id_alt IS NULL THEN 1.0 ELSE 0.5 END AS w FROM tickets WHERE year=2025 AND is_public AND segment_id IS NOT NULL UNION ALL SELECT segment_id_alt, 0.5 FROM tickets WHERE year=2025 AND is_public AND segment_id_alt IS NOT NULL) SELECT g.street_name, round(sum(w),0) AS tickets FROM s JOIN segments g ON g.segment_id=s.sid GROUP BY g.street_name ORDER BY tickets DESC LIMIT 5

Most-ticketed streets (public) in 2025 — 5 rows
street_name	tickets
Yonge St	44,881
Queen St W	33,271
Queen St E	21,814
Danforth Ave	21,440
College St	21,176

Q-0157·Section: year-2025·13 ms·1 row

Hour-band shares and average fine 2025

SELECT round(100.0*sum(CASE WHEN hour BETWEEN 9 AND 13 THEN 1 ELSE 0 END)/count(hour),1) AS pct_9_to_14, round(100.0*sum(CASE WHEN hour BETWEEN 0 AND 4 THEN 1 ELSE 0 END)/count(hour),1) AS pct_0_to_5, round(avg(fine),2) AS avg_fine FROM tickets WHERE year=2025

Hour-band shares and average fine 2025 — 1 row
pct_9_to_14	pct_0_to_5	avg_fine
34.6	19.6	80.3