Titanic casualties

titanic_casualties.csv

titanic_casualties.sql

--
-- Database: DuckDB 0.8.1
--
-- You can run this at: https://shell.duckdb.org/
--
-- Source for my `titanic_casualties.csv`: 
-- 
-- * https://en.wikipedia.org/wiki/Sinking_of_the_Titanic#Casualties_and_survivors
--

DROP TABLE IF EXISTS titanic_deaths;

CREATE TABLE titanic_deaths AS
SELECT *
FROM read_csv_auto('https://gist.githubusercontent.com/sacundim/73bd069669edaca11e21b9f25aaa5309/raw/13aba76b286759ef055435f73606b876e875226f/titanic_casualties.csv');

SELECT 
	Category,
	sum("Number onboard") AS "Number onboard",
	100.0 * sum("Number onboard") 
		/ (SELECT sum("Number onboard") FROM titanic_deaths)
		AS "% of onboard",
	sum("Number lost") AS "Number lost",
	100.0 * sum("Number lost") 
		/ (SELECT sum("Number lost") FROM titanic_deaths)
		AS "% of total lost",
	100.0 * sum("Number lost")
		/ sum("Number onboard")
		AS "% of group lost"
FROM titanic_deaths
GROUP BY Category
ORDER BY "% of group lost" DESC;
--
-- Result:
--
-- ┌──────────────┬────────────────┬────────────────────┬─────────────┬────────────────────┬───────────────────┐
-- │ Category     ┆ Number onboard ┆ % of onboard       ┆ Number lost ┆ % of total lost    ┆ % of group lost   │
-- ╞══════════════╪════════════════╪════════════════════╪═════════════╪════════════════════╪═══════════════════╡
-- │ Crew         ┆            908 ┆ 40.827338129496404 ┆         696 ┆  45.97093791281374 ┆ 76.65198237885463 │
-- │ Third Class  ┆            706 ┆ 31.744604316546763 ┆         528 ┆  34.87450462351387 ┆ 74.78753541076487 │
-- │ Second Class ┆            285 ┆  12.81474820143885 ┆         167 ┆ 11.030383091149274 ┆ 58.59649122807018 │
-- │ First Class  ┆            325 ┆ 14.613309352517986 ┆         123 ┆  8.124174372523118 ┆ 37.84615384615385 │
-- └──────────────┴────────────────┴────────────────────┴─────────────┴────────────────────┴───────────────────┘


SELECT 
	Category,
	Passengers,
	sum("Number onboard") AS "Number onboard",
	100.0 * sum("Number onboard") 
		/ (SELECT sum("Number onboard") FROM titanic_deaths)
		AS "% of onboard",
	sum("Number lost") AS "Number lost",
	100.0 * sum("Number lost") 
		/ (SELECT sum("Number lost") FROM titanic_deaths)
		AS "% of total lost",
	100.0 * sum("Number lost")
		/ sum("Number onboard")
		AS "% of group lost"
FROM titanic_deaths
GROUP BY Category, Passengers
ORDER BY "% of group lost" DESC;
--
-- Result:
--
-- ┌──────────────┬────────────┬────────────────┬────────────────────┬─────────────┬─────────────────────┬────────────────────┐
-- │ Category     ┆ Passengers ┆ Number onboard ┆ % of onboard       ┆ Number lost ┆ % of total lost     ┆ % of group lost    │
-- ╞══════════════╪════════════╪════════════════╪════════════════════╪═════════════╪═════════════════════╪════════════════════╡
-- │ Second Class ┆ Men        ┆            168 ┆  7.553956834532374 ┆         154 ┆  10.171730515191545 ┆  91.66666666666667 │
-- │ Third Class  ┆ Men        ┆            462 ┆  20.77338129496403 ┆         387 ┆  25.561426684280054 ┆  83.76623376623377 │
-- │ Crew         ┆ Men        ┆            885 ┆   39.7931654676259 ┆         693 ┆   45.77278731836196 ┆  78.30508474576271 │
-- │ First Class  ┆ Men        ┆            175 ┆  7.868705035971223 ┆         118 ┆   7.793923381770146 ┆  67.42857142857143 │
-- │ Third Class  ┆ Children   ┆             79 ┆  3.552158273381295 ┆          52 ┆  3.4346103038309117 ┆  65.82278481012658 │
-- │ Third Class  ┆ Women      ┆            165 ┆  7.419064748201439 ┆          89 ┆   5.878467635402906 ┆  53.93939393939394 │
-- │ First Class  ┆ Children   ┆              6 ┆ 0.2697841726618705 ┆           1 ┆ 0.06605019815059446 ┆ 16.666666666666668 │
-- │ Second Class ┆ Women      ┆             93 ┆  4.181654676258993 ┆          13 ┆  0.8586525759577279 ┆ 13.978494623655914 │
-- │ Crew         ┆ Women      ┆             23 ┆ 1.0341726618705036 ┆           3 ┆ 0.19815059445178335 ┆ 13.043478260869565 │
-- │ First Class  ┆ Women      ┆            144 ┆  6.474820143884892 ┆           4 ┆ 0.26420079260237783 ┆ 2.7777777777777777 │
-- │ Second Class ┆ Children   ┆             24 ┆  1.079136690647482 ┆           0 ┆                   0 ┆                  0 │
-- └──────────────┴────────────┴────────────────┴────────────────────┴─────────────┴─────────────────────┴────────────────────┘