-
Notifications
You must be signed in to change notification settings - Fork 6
/
queries.sql
71 lines (60 loc) · 1.67 KB
/
queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
.headers on
.mode csv
.output results/total_by_year.csv
SELECT
year,
COUNT(*) AS launch_count
FROM launches
WHERE launch_type = 'O'
GROUP BY year
ORDER BY year;
.output results/failures_by_year.csv
SELECT
year,
COUNT(*) as total,
COUNT(CASE WHEN launch_status = 'S' THEN tag END) AS success,
COUNT(CASE WHEN launch_status = 'F' THEN tag END) AS failure,
COUNT(CASE WHEN launch_status = 'U' THEN tag END) AS unknown
FROM launches
GROUP BY year
ORDER BY year;
.output results/success_by_year.csv
SELECT
year,
COUNT(*) as total,
COUNT(CASE WHEN launch_status = 'S' THEN tag END) AS success,
COUNT(CASE WHEN launch_status = 'F' THEN tag END) AS failure,
COUNT(CASE WHEN launch_status = 'U' THEN tag END) AS unknown
FROM launches
WHERE launch_type = 'O'
GROUP BY year
ORDER BY year;
.output results/class_by_year.csv
SELECT
year,
COUNT(*) as total,
COUNT(CASE WHEN class = 'A' THEN tag END) AS academic,
COUNT(CASE WHEN class = 'B' THEN tag END) AS business,
COUNT(CASE WHEN class = 'C' THEN tag END) AS military,
COUNT(CASE WHEN class = 'D' THEN tag END) AS government
FROM
launches,
orgs
WHERE launches.agency = orgs.code
GROUP BY year
ORDER BY year;
.output results/total_by_country_by_year.csv
SELECT
year,
COUNT(*) as total,
COUNT(CASE WHEN state_code = 'US' THEN tag END) AS united_states,
COUNT(CASE WHEN state_code = 'SU' OR state_code = 'RU' THEN tag END) AS russia,
COUNT(CASE WHEN state_code = 'CN' THEN tag END) AS china,
COUNT(CASE WHEN state_code = 'IN' THEN tag END) AS india
FROM
launches,
orgs
WHERE launches.agency = orgs.code
GROUP BY year
ORDER BY year;
.output stdout