db = FileAttachment("0000196080__0000198959@1.db").sqlite()
db.describe()
addresses = db.query(`SELECT * FROM addresses`)
minersWithOwnerAddresses = db.query(
`SELECT miners.id as minerId, owner_id AS ownerId, address AS ownerAddress FROM miners ` +
`INNER JOIN addresses on miners.owner_id = addresses.id`)
Inputs.table(minersWithOwnerAddresses)
magikOwner = minersWithOwnerAddresses.find(({ minerId }) => minerId === 'f02620').ownerAddress
fundedMagik = db.query(`
WITH RECURSIVE
funded_magik(id, address, funded_from) AS (
SELECT id, address, funded_from FROM addresses WHERE address = $1
UNION
SELECT addresses.id, addresses.address, addresses.funded_from
FROM addresses, funded_magik
WHERE addresses.address = funded_magik.address
OR funded_magik.funded_from = addresses.address
)
SELECT * FROM funded_magik;`, [ magikOwner ])
stratify = d3.stratify()
.id(d => d["address"])
.parentId(d => d["funded_from"])
fundedMagikRoot = stratify(fundedMagik)
fundedMagikGraph = graph(fundedMagikRoot, {label: d => d.data.id})
db.query(`
SELECT miner_address.id, miner_address.address, miners.owner_id AS funded_from_id, owner_address.address AS funded_from, miners.id AS miner_id
FROM addresses AS miner_address, addresses AS owner_address, miners
WHERE miners.id = miner_address.id
AND miners.id = $1
AND miners.owner_id = owner_address.id
GROUP by miner_address.id, miner_address.address, funded_from_id, miner_id
`, [ 'f02620' ])
fundedMagikWithMinerId = db.query(`
WITH RECURSIVE
funded_magik(id, address, funded_from, miner_id) AS (
SELECT
miner_address.id,
miner_address.address,
owner_address.address AS funded_from,
miners.id AS miner_id
FROM addresses AS miner_address, addresses AS owner_address, miners
WHERE miners.id = miner_address.id
AND miners.id = $1
AND miners.owner_id = owner_address.id
GROUP by miner_address.id, miner_address.address, owner_address.address, miner_id
UNION
SELECT addresses.id, addresses.address, addresses.funded_from, NULL AS miner_id
FROM addresses
INNER JOIN funded_magik
ON funded_magik.funded_from = addresses.address
)
SELECT * FROM funded_magik;`, [ 'f02620' ])
allMinersWithFunders = db.query(`
SELECT addresses.id, address, funded_from, miners.id AS miner_id
FROM addresses, miners
WHERE miners.owner_id = addresses.id
GROUP by addresses.id, address, funded_from, miner_id
`)
allFundedWithMinerId = db.query(`
WITH RECURSIVE
funded(id, address, funded_from, miner_id) AS (
SELECT
miner_address.id,
miner_address.address,
owner_address.address AS funded_from,
miners.id AS miner_id
FROM addresses AS miner_address, addresses AS owner_address, miners
WHERE miners.id = miner_address.id
AND miners.owner_id = owner_address.id
GROUP by miner_address.id, miner_address.address, owner_address.address, miner_id
UNION
SELECT addresses.id, addresses.address, addresses.funded_from, NULL AS miner_id
FROM addresses
INNER JOIN funded
ON funded.funded_from = addresses.address
)
SELECT * FROM funded
`)
roots = allFundedWithMinerId.filter(({ funded_from }) => !funded_from).sort(sortIdRecords)
Inputs.table(roots, { columns: [ "id", "address" ] })
joinedAllFundedWithMinerId = allFundedWithMinerId
.map(record => ({
...record,
funded_from: record.funded_from || 'root'
}))
.concat([{
id: "root",
address: "root",
funded_from: null,
miner_id: null
}])
reachable = {
const addressIndex = d3.index(joinedAllFundedWithMinerId, d => d.id)
const fundedFromIndex = d3.group(joinedAllFundedWithMinerId, d => d.funded_from)
const start = addressIndex.get('root')
const reachable = []
function walk (from) {
reachable.push(from)
// console.log('Walking', from.id)
const fundedNodes = fundedFromIndex.get(from.address)
// console.log('Funded Nodes', fundedNodes)
if (fundedNodes) {
for (const funded of fundedNodes) {
walk(funded)
}
}
}
walk(start)
return reachable
}
reachableTree = stratify(reachable)
/* Too big!! graph(reachableTree, {label: d => d.data.id + (d.data.miner_id ? ` (SP: ${d.data.miner_id})` : '') }) */
leavesWithPower = reachable.filter(({ miner_id }) => minerPowerDailyAverageReport.miners[miner_id] && minerPowerDailyAverageReport.miners[miner_id].qualityAdjPower)
reachableWithPower = {
const addressIndex = d3.index(reachable, d => d.address)
const filtered = new Map()
function walkUp (node) {
// console.log('WalkUp', node)
filtered.set(node.id, {
...node,
qualityAdjPower: minerPowerDailyAverageReport.miners[node.miner_id] &&
minerPowerDailyAverageReport.miners[node.miner_id].qualityAdjPower
})
if (node.funded_from) {
// console.log('funded_from', node.funded_from, addressIndex)
walkUp(addressIndex.get(node.funded_from))
}
}
for (const leaf of leavesWithPower) {
walkUp(leaf)
}
return [...filtered.values()].sort(sortIdRecords)
}
reachableWithPowerTree = stratify(reachableWithPower)
shortCircuit1 = true
!shortCircuit1 && graph(reachableWithPowerTree, {label: d => d.data.id + (d.data.miner_id ? ` (SP: ${d.data.miner_id} - ${bytes(d.data.qualityAdjPower, { mode: 'binary' })})` : '') })
reachableWithPowerAndRegions = reachableWithPower.map(record => {
const newRecord = record
if (record.miner_id) {
const regions = minerRegionsReport.minerRegions.filter(({ miner }) => miner === record.miner_id)
if (regions.length > 0) {
newRecord.regions = regions.map(({ region }) => region)
}
}
return newRecord
})
reachableWithPowerAndRegionsTree = stratify(reachableWithPowerAndRegions)
shortCircuit2 = false
!shortCircuit2 && graph(reachableWithPowerAndRegionsTree, {
label: d =>
(d.data.miner_id ?
`SP: ${d.data.miner_id} - ${bytes(d.data.qualityAdjPower, { mode: 'binary' })}` : d.data.id) +
(d.data.regions ?
` - ${d.data.regions.join(', ')}` : '')
})
minerPowerDailyAverageReport = (await fetch(`${minerPowerDailyAverageLatestBucketUrl}/miner-power-daily-average-latest.json`)).json()
minerRegionsReport = (await fetch(`${geoIpLookupsBucketUrl}/miner-regions-latest.json`)).json()
sortIdRecords = ({ id: minerA }, { id: minerB }) => Number(minerA.slice(1)) - Number(minerB.slice(1))
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.
