leegee · March 26, 2024 13:48
diff --git a/query.sql b/query.sql
 COPY (
    SELECT
        TO_CHAR(DATE_TRUNC('decade', datetime), 'YYYY') || 's' AS decade,
        shape,
        COUNT(*) AS shape_count
    FROM
        sightings
    WHERE shape IS NOT NULL and shape != 'unknown'
    GROUP BY
        DATE_TRUNC('decade', datetime),
        shape
    ORDER BY
        DATE_TRUNC('decade', datetime),
        shape_count DESC
 ) TO '/shapes-by-decade.csv' WITH CSV HEADER;
diff --git a/shapes-by-decade.csv b/shapes-by-decade.csv
	COPY (
	SELECT
	TO_CHAR(DATE_TRUNC('decade', datetime), 'YYYY') \|\| 's' AS decade,
	shape,
	COUNT(*) AS shape_count
	FROM
	sightings
	WHERE shape IS NOT NULL and shape != 'unknown'
	GROUP BY
	DATE_TRUNC('decade', datetime),
	shape
	ORDER BY
	DATE_TRUNC('decade', datetime),
	shape_count DESC
	) TO '/shapes-by-decade.csv' WITH CSV HEADER;
decade	shape	shape_count
1900s	other	1
1910s	cigar	2
1910s	disk	1
1920s	disk	2
1930s	disk	2
1930s	oval	2
1930s	light	2
1930s	cigar	1
1930s	fireball	1
1930s	cylinder	1
1930s	rectangle	1
1930s	sphere	1
1930s	triangle	1
1940s	disk	30
1940s	circle	9
1940s	cigar	8
1940s	sphere	7
1940s	light	7
1940s	oval	7
1940s	other	6
1940s	formation	5
1940s	cylinder	4
1940s	rectangle	3
1940s	fireball	2
1940s	triangle	1
1940s	flash	1
1940s	chevron	1
1950s	disk	125
1950s	circle	47
1950s	light	45
1950s	cigar	33
1950s	oval	27
1950s	other	26
1950s	sphere	23
1950s	fireball	20
1950s	cylinder	10
1950s	triangle	8
1950s	formation	7
1950s	diamond	7
1950s	egg	4
1950s	teardrop	3
1950s	chevron	2
1950s	rectangle	2
1950s	cone	1
1950s	flash	1
1950s	changing	1
1960s	disk	318
1960s	light	150
1960s	circle	133
1960s	sphere	104
1960s	cigar	95
1960s	other	94
1960s	oval	90
1960s	triangle	58
1960s	fireball	35
1960s	cylinder	24
1960s	formation	23
1960s	changing	16
1960s	egg	14
1960s	diamond	12
1960s	rectangle	9
1960s	chevron	9
1960s	flash	6
1960s	teardrop	4
1960s	cone	2
1960s	cross	2
1970s	disk	546
1970s	light	292
1970s	circle	234
1970s	triangle	196
1970s	oval	175
1970s	sphere	149
1970s	other	147
1970s	cigar	124
1970s	fireball	59
1970s	rectangle	54
1970s	cylinder	41
1970s	formation	33
1970s	chevron	28
1970s	changing	27
1970s	egg	23
1970s	diamond	18
1970s	flash	13
1970s	teardrop	10
1970s	cone	8
1970s	cross	5
1970s	delta	1
1980s	disk	291
1980s	triangle	265
1980s	light	227
1980s	other	201
1980s	sphere	162
1980s	circle	154
1980s	oval	100
1980s	cigar	88
1980s	rectangle	63
1980s	fireball	57
1980s	formation	47
1980s	cylinder	43
1980s	chevron	41
1980s	changing	40
1980s	diamond	38
1980s	egg	28
1980s	flash	10
1980s	teardrop	8
1980s	cone	7
1980s	cross	2
1990s	light	1565
1990s	triangle	1101
1990s	other	752
1990s	fireball	692
1990s	disk	622
1990s	circle	603
1990s	sphere	562
1990s	oval	348
1990s	formation	271
1990s	cigar	227
1990s	changing	179
1990s	rectangle	134
1990s	cylinder	133
1990s	diamond	126
1990s	chevron	124
1990s	flash	121
1990s	egg	102
1990s	teardrop	74
1990s	cone	33
1990s	cross	18
1990s	delta	6
1990s	crescent	2
1990s	round	2
1990s	pyramid	1
1990s	dome	1
1990s	hexagon	1
1990s	changed	1
1990s	flare	1
2000s	light	8622
2000s	triangle	4002
2000s	circle	3398
2000s	other	2907
2000s	sphere	2355
2000s	disk	2351
2000s	fireball	2203
2000s	oval	1834
2000s	formation	1222
2000s	changing	1094
2000s	cigar	1055
2000s	flash	694
2000s	cylinder	645
2000s	rectangle	639
2000s	diamond	600
2000s	chevron	505
2000s	teardrop	420
2000s	egg	420
2000s	cone	171
2000s	cross	117
2010s	light	5655
2010s	fireball	3139
2010s	circle	3030
2010s	triangle	2233
2010s	sphere	2024
2010s	other	1515
2010s	oval	1150
2010s	disk	925
2010s	formation	849
2010s	changing	605
2010s	flash	482
2010s	cigar	424
2010s	rectangle	392
2010s	cylinder	382
2010s	diamond	377
2010s	chevron	242
2010s	teardrop	231
2010s	egg	168
2010s	cone	94
2010s	cross	89