Skip to content

Instantly share code, notes, and snippets.

@leegee

leegee/query.sql

Created March 26, 2024 13:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save leegee/e62d79c154f50efad68aa5e825255247 to your computer and use it in GitHub Desktop.
Save leegee/e62d79c154f50efad68aa5e825255247 to your computer and use it in GitHub Desktop.
Download ZIP
UFO shapes by decade, from Muffon via Kaggle
Raw
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;
Raw
shapes-by-decade.csv
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment