The avatar for @espinielli
Enrico Spinielli
Interested in dataviz and looking forward to a world where human brains are freed from being occupied with boring, repetitive, manual, click-copy-paste tasks.
Public
Edited
Sep 6, 2024
1 fork
Importers
1 star
Datasets
Departure, Arrival, Internal and Overflight (DAIO) flights per Country FIR in Europe
European Traffic (Airline) 2019-2021State Traffic 2019-2020
Also listed in…
Aviation
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Plot.plot({
marks: [
Plot.lineY(daio_country, { x: "entry_date", y: "flt_d" }),
Plot.ruleY([0])
]
})
Insert cell
Insert cell
Plot.plot({
marks: [
Plot.lineY(daio_country, { x: "entry_date", y: "flt_o" }),
Plot.ruleY([0])
]
})
Insert cell
Insert cell
Insert cell
daio
WITH
l_o AS (
SELECT
MAX(entry_date) AS entry_date,
country_icao_code,
country_name,
SUM(flt_o) / 7 AS flt_o_avg
FROM
daio
WHERE
${latest_minus_seven_date} < entry_date AND entry_date <= ${latest_date}
GROUP BY
country_icao_code, country_name),
r_o AS (
SELECT
MAX(entry_date) as entry_date,
country_icao_code,
country_name,
SUM(flt_o) / 7 AS flt_o_avg
FROM
daio
WHERE
${reference_minus_seven_date} < entry_date AND entry_date <= ${reference_date}
GROUP BY
country_icao_code, country_name)

SELECT
f1.entry_date AS latest_date,
f0.entry_date AS reference_date,
f1.country_icao_code AS id,
f1.country_name AS state,
f1.flt_o_avg AS flt_o_avg_latest,
f0.flt_o_avg AS flt_o_avg_preC19,
(f1.flt_o_avg - f0.flt_o_avg) AS delta,
ROUND(100 *(f1.flt_o_avg - f0.flt_o_avg) / f0.flt_o_avg) AS variation
FROM
l_o f1, r_o f0
WHERE
f0.country_icao_code = f1.country_icao_code
Insert cell
Insert cell
latest_minus_seven_date = moment.utc(latest_date).subtract(7, "days").toDate()

Insert cell
Insert cell
reference_minus_seven_date = moment
.utc(reference_date)
.subtract(7, "days")
.toDate()
Insert cell
Insert cell
Insert cell
flt_o_moving_average
SELECT * FROM flt_o_mvg_avg
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more