Published
Edited
Jun 2, 2022
1 fork
// The table name depends on whether we're querying the public or private repository.
tableName = datasource === "Public" ? '"neumark/revolut-sample:latest"."file_1"' : '"neumark/revolut:latest"."file_1"'
query = datasource === "Public" ? q : (queryString) => db.query(queryString);
topByTotalSpent = query(`SELECT
"Description" AS business,
CAST(SUM("Amount")*-1 AS DOUBLE PRECISION) as total_spent
FROM
${tableName}
WHERE "Type" = 'CARD_PAYMENT'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;`)
Plot.plot({
style: { paddingLeft: 20, overflow: "visible" },
width: 1200,
height: 500,
x: {
domain: d3.sort(topByTotalSpent, d => d).map(d => d.business)
},
y: {
grid: true
},
marks: [
Plot.barY(topByTotalSpent, {y: "total_spent", x: "business"}),
Plot.ruleY([0])
]
})
// If you're using your own bank account statement, you probably need to add some businesses to the patterns below.
categories = ({
"groceries": [
new RegExp("Spar "),
new RegExp("^Cba Pri"),
new RegExp("^Penny "),
new RegExp("Lidl .*"),
new RegExp("Tesco"),
new RegExp("Auchan.*"),
new RegExp("Aldi .*"),
"Family Market",
"Sayed Parviz Kft",
new RegExp("szatyorbolt"),
new RegExp("E'lelmiszer")
],
"pets": [
"Tutivet Allatorvosi",
new RegExp("petissimo.hu"),
new RegExp("Zooplus Ag"),
new RegExp("Fressnapf "),
new RegExp("www\\.nrdogs\\.hu")
],
"furniture": [
new RegExp("Ikea"),
new RegExp("Mo:max"),
new RegExp("Diego"),
new RegExp("^Jysk"),
],
"coffee": [
"Harminckettesek Tere",
"Koronaherceg Kft.",
"Lipo'ti Pe'kse'g E's K",
new RegExp("^Rauf "),
new RegExp("^Sumup .*mtrm Roast$"),
"Muterem Kavezo",
new RegExp("outside The Bo$"),
new RegExp("^Starbucks"),
"Addicted To Caffeine",
new RegExp("Kavezo"),
"Budai 3"
],
"pharmacy": [
"Benu Hajnal Gyo'gyszer",
"Harom Sas Bt.",
new RegExp("Gyogyszertar"),
"Fehe'rva'r Gyo'gyszert"
],
"household": [
new RegExp("Rossmann "),
new RegExp("^Dm ")
],
"restaurant": [
"Padthai Wokbar",
"Jk-Gold Kft",
new RegExp("Delivery Hero Hungary"),
"Hummus Bar",
new RegExp("[p|P]izza"),
new RegExp("netpincer")
]
})
categorizedByTotalSpend = getCategoryTotals(categorise(await query(`
SELECT
"Description" AS business,
-- Note: the CAST is needed so the total_spent column contains numbers not strings
-- We multiply by -1 to make the amounts positive since spending shows up as a negative
-- value in our account history.
CAST(SUM("Amount")*-1 AS DOUBLE PRECISION) as total_spent
FROM
${tableName}
-- Filter transactions past date when we adopted our family dog.
WHERE "Type" = 'CARD_PAYMENT' AND "Started Date" > '2020-12-08'::DATE
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100;`), categories))
Plot.plot({
style: { paddingLeft: 50, overflow: "visible" },
width: 1000,
x: {
domain: d3.sort(categorizedByTotalSpend, d => -d.total).map(d => d.category)
},
y: {
grid: true
},
marks: [
Plot.barY(categorizedByTotalSpend, {y: "total", x: "category"}),
Plot.ruleY([0])
]
})
addTooltips(Plot.plot({
y: {
grid: true
},
marks: [
Plot.line(
await query(`
WITH weekly_transactions AS (SELECT
t.week_start,
u.*
FROM (
SELECT * FROM generate_series(
'2018-01-01'::DATE,
'2022-06-01'::DATE,
'1 week'
) AS week_start
) t
JOIN (
SELECT
"Description" AS business,
"Started Date" as ts,
-- Note: the CAST is needed so the total_spent column contains numbers not strings
-- Multiply by -1 to make the amounts positive since spending shows up as a negative
-- value in the account statement.
CAST("Amount" * -1 AS DOUBLE PRECISION) as amount
FROM
${tableName}
WHERE
"Type" = 'CARD_PAYMENT'
) u ON
u.ts > t.week_start AND
u.ts <= t.week_start + interval '1 week')
SELECT
week_start::DATE as week,
SUM(amount) as amount
FROM weekly_transactions
GROUP BY 1
ORDER BY 1
`),
{marker: 'circle', x: "week", y: "amount", title: d => `${d.week.toISOString().split('T')[0]}: ${d.amount}`})
]
}))
sumByKey = (arr, keyField='week', valueField='amount', keyEncode=(x)=>x, keyDecode=(x)=>x) => [...(arr.reduce((acc, row) => {
const key = keyEncode(row[keyField]);
acc.set(key, (acc.get(key) ?? 0) + row[valueField]);
return acc;
}, new Map()).entries())].map(([key, value]) => ({[keyField]:keyDecode(key), [valueField]:value}))
weeklyCategorySpend = Object.entries(categorise(await query(`
WITH weekly_transactions AS (SELECT
t.week_start,
u.*
FROM (
SELECT * FROM generate_series(
'2018-01-01'::DATE,
'2022-06-01'::DATE,
'1 week'
) AS week_start
) t
JOIN (
SELECT
"Description" AS business,
"Started Date" as ts,
-- Note: the CAST is needed so the total_spent column contains numbers not strings
-- Multiply by -1 to make the amounts positive since spending shows up as a negative
-- value in the account statement.
CAST("Amount" * -1 AS DOUBLE PRECISION) as amount
FROM
${tableName}
WHERE
"Type" = 'CARD_PAYMENT'
) u ON
u.ts > t.week_start AND
u.ts <= t.week_start + interval '1 week')
SELECT
week_start::DATE as week,
business,
SUM(amount) as amount
FROM weekly_transactions
GROUP BY 1, 2
ORDER BY 1
`), categories)).flatMap(
([category, weeks]) => sumByKey(weeks).map(
({week, amount}) => ({week, amount, category}))).sort(
(a, b) => a.week - b.week)
addTooltips(Plot.plot({
color: {
legend: true
},
width: 1000,
style: { paddingLeft: 50, overflow: "visible" },
y: {
grid: true,
},
x: {
type: "band",
tickFormat: d => d.toLocaleString("en", {month: "narrow"}),
label: null
},
marks: [
Plot.barY(weeklyCategorySpend, {
order: "sum",
x: "week",
y: "amount",
fill: "category",
title: d => `${d.week.toISOString().split('T')[0]} - ${d.category}: ${d.amount}`}),
Plot.ruleY([0])
]
}))
addTooltips(Plot.plot({
color: {
legend: true
},
width: 1000,
style: { paddingLeft: 50, overflow: "visible" },
y: {
grid: true,
},
x: {
type: "band",
tickFormat: d => d.toLocaleString("en", {month: "narrow"}),
label: null
},
marks: [
Plot.barY(
Object.entries(categorise(await query(`
WITH monthly_transactions AS (SELECT
t.month_start,
u.*
FROM (
SELECT * FROM generate_series(
'2018-01-01'::DATE,
'2022-06-01'::DATE,
'1 month'
) AS month_start
) t
JOIN (
SELECT
"Description" AS business,
"Started Date" as ts
FROM
${tableName}
WHERE
"Type" = 'CARD_PAYMENT'
) u ON
u.ts > t.month_start AND
u.ts <= t.month_start + interval '1 month')
SELECT
month_start::DATE as month,
business,
COUNT(0)::INTEGER as purchases
FROM monthly_transactions
GROUP BY 1, 2
ORDER BY 1
`), categories)).flatMap(
([category, months]) => sumByKey(
months,
'month',
'purchases',
// These are needed because Javascript cant === compare Date objects. Instead, we use getTime() to get
// a numeric unix timestamp which can be compared. For charting Date objects work better though, so
// the last argument converts the timestamp back to a Date.
x => x.getTime(),
x => {return new Date(x)}
).map(
({month, purchases}) => ({month, purchases, category}))).sort(
(a, b) => a.month - b.month)
, {
order: "sum",
x: "month",
y: "purchases",
fill: "category",
title: d => `${d.month.toISOString().split('T')[0].slice(0,-3)} ${d.category}: ${d.purchases}`}),
Plot.ruleY([0])
]
}))
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.
