The avatar for @udg-master-data-science
UdG | Master's in Data Science
Public
Edited
Aug 30, 2024
Fork of Exercise 4: Look ma! It's interactive
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
variety_lineplot = Plot.plot({
width: width,
height: 420,
marginRight: 120,
x: {label: "Date"},
y: {label: "Acumulated revenue per week"},
marks: [
Plot.lineY(daily_orders_categorized, Plot.binX({y: 'sum'}, {
filter: (d) => (selectedVariety==="All" || d.variety === selectedVariety),
x: "date",
y: "revenue",
z: "size",
curve: "basis",
stroke: "#C0C0C0",
tip: x,
interval: d3.utcWeek
})),
Plot.lineY(daily_orders_categorized, Plot.binX({y: 'sum'}, {
filter: (d) => (selectedVariety!=="All" && d.variety === selectedVariety && d.size === selectedSize),
x: "date",
y: "revenue",
stroke: "red",
strokeWidth: 3,
curve: "basis",
interval: d3.utcWeek
})),
Plot.dot(
daily_orders_categorized,
Plot.selectLast(
Plot.binX(
{ y: "sum" },
{
filter: (d) => (selectedVariety!=="All" && d.variety === selectedVariety && d.size === selectedSize),
x: "date",
y: "revenue",
z: "size",
curve: "basis",
interval: d3.utcWeek,
fill: "red",
r: 5
}
)
)
),
Plot.text(
daily_orders_categorized,
Plot.selectLast(
Plot.binX(
{ y: "sum" },
{
filter: (d) => (selectedVariety!=="All" && d.variety === selectedVariety && d.size === selectedSize),
x: "date",
y: "revenue",
z: "size",
curve: "basis",
interval: d3.utcWeek,
fill: "red",
dx: 10,
textAnchor: "start",
text: d => d[0].size,
fontWeight: "bold"
}
)
)
)
]
})
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
stores
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
daily_orders_product
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
daily_orders
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
Insert cell
Insert cell
monthly_revenue_by_product
Insert cell
daily_orders_product
SELECT TRIM(SUBSTRING(name, 1, INSTR(name, 'Pizza') - 1)) as variety, TRIM(SUBSTRING(name, INSTR(name, 'Pizza') + 5)) as size, category, YEAR(order_date) as year, SUM(revenue) as total_revenue
FROM daily_orders_product
WHERE YEAR(order_date) BETWEEN ${yearMin} AND ${yearMax}
GROUP BY variety, size, year, category
ORDER BY year, variety, size
Insert cell
daily_orders_product
SELECT
TRIM(SUBSTRING(name, 1, INSTR(name, 'Pizza') - 1)) as variety,
TRIM(SUBSTRING(name, INSTR(name, 'Pizza') + 5)) as size,
category,
SUM(CASE WHEN YEAR(order_date) = 2020 THEN revenue ELSE 0 END) as rev2020,
SUM(CASE WHEN YEAR(order_date) = 2021 THEN revenue ELSE 0 END) as rev2021,
SUM(CASE WHEN YEAR(order_date) = 2022 THEN revenue ELSE 0 END) as rev2022,
SUM(CASE WHEN YEAR(order_date) = 2020 THEN orders ELSE 0 END) as ord2020,
SUM(CASE WHEN YEAR(order_date) = 2021 THEN orders ELSE 0 END) as ord2021,
SUM(CASE WHEN YEAR(order_date) = 2022 THEN orders ELSE 0 END) as ord2022

FROM daily_orders_product
GROUP BY variety, size, category
ORDER BY variety, size, category;
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
daily_orders_product
SELECT TRIM(SUBSTRING(name, 1, INSTR(name, 'Pizza') - 1)) as variety, TRIM(SUBSTRING(name, INSTR(name, 'Pizza') + 5)) as size, category, SUM(revenue) as total_revenue
FROM daily_orders_product
WHERE DAYOFWEEK(order_date) IN (${weekdayList}) AND YEAR(order_date) BETWEEN ${yearMin} AND ${yearMax}
GROUP BY variety, size, category
ORDER BY variety, size, total_revenue
Insert cell
ventas_week = weekday_revenue_product
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
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