Blochain.info has a really cool tool that gives users the ability to visualize transactions. Given the database that the Bitcoin Updater builds, I wanted to see if I could take this concept, but also include the dates at which each transaction was added to an approved block. After feeding it a random transaction hash, the dendrogram from the website returns this:
With just a few SQL strokes, I was able to reproduce this result as well, including the dates from the block it belongs to:
SELECT Incoming.prev_out AS 'Current Transaction', Curr_Block.TIME AS 'Current Time', Outputs.`Redeemed at input` AS 'Next Transaction', Next_Block.TIME AS 'Next Time', Outputs.amount AS 'Next Value' FROM Incoming JOIN TRANSACTION AS Next_Transaction ON Next_Transaction.hash = Incoming.Transaction_hash JOIN TRANSACTION AS Curr_Transaction ON Curr_Transaction.hash = Incoming.prev_out JOIN Block AS Next_Block ON Next_Block.hash = Next_Transaction.Block_hash JOIN Block AS Curr_Block ON Curr_Block.hash = Curr_Transaction.Block_hash JOIN Outputs ON Outputs.`Transaction Hash` = Incoming.prev_out WHERE Incoming.prev_out = 'b08ff6b529c09337de8e4e09ac6e7e1cd3697ecddab76f253a40f0d105c0ac8e' GROUP BY `Next Transaction`
The Outputs table I am using is a view:
SELECT TRANSACTION.hash AS 'Transaction Hash', Outgoing.n AS 'Index', IFNULL(Incoming.Transaction_hash, 'Not yet redeemed') AS 'Redeemed at input', Outgoing.VALUE AS 'Amount', TRIM(REPLACE(REPLACE(REPLACE(Outgoing.scriptPubKey, 'OP_CHECKSIG', ''), 'OP_EQUALVERIFY', ''), 'OP_DUP OP_HASH160', '')) AS 'To Address', '???' AS 'Type', Outgoing.scriptPubKey AS 'ScriptPubKey' FROM TRANSACTION JOIN Outgoing ON Outgoing.Transaction_hash = TRANSACTION.hash LEFT OUTER JOIN Incoming ON Incoming.prev_out = Outgoing.Transaction_hash AND Incoming.n = Outgoing.n
How cool would it be to have a colossal dendrogram visualizing the entire transaction chain? What if one of the axis were based upon time? Would we be able to see the evolution of the currency from its infancy to the complex multi-organ behemoth it is today? … definitely. So stay tuned!
PS: I will soon be releasing a tool that will allow you to download and archive any website that has an RSS feed thanks to Google Reader. I’m still figuring out what kind of format would be the most useful to the most people. I may or may not be using this to scrape the entire official bitcoin forums and finding a relationship between post frequency to market events to blockchain events.