Feb 282012

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:

Haha! Business.

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'
JOIN    Outgoing
ON      Outgoing.Transaction_hash = TRANSACTION.hash
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.

 Posted by at 11:03 pm
Feb 212012

Having the Bitcoin blockchain and historical market data in a searchable database can now be available to you! Simply assemble the little program I made, run it, and in 24-30 hours you too can reap the sweet sweet useless awesome benefits as I have. Check it out here!

Want to build a histogram of rounded transaction values to the nearest whole Bitcoin for the year 2011? Of course you do and now you can!

It looks like people have an affinity for nice, even, round amounts: e.g. 100, 150, 200 when sending Bitcoin to one another. But who doesn’t? (This query took 4 hours to run so APPRECIATE IT)

FROM        Outgoing
ON          TRANSACTION.hash = Transaction_hash
JOIN        Block
ON          Block.hash = TRANSACTION.Block_hash
WHERE       TIME BETWEEN '2011-1-1' AND '2012-1-1'

Want to see if there is a relationship between the amount of trades on the US market to the value of Bitcoin to the USD? Well say no more!

It looks like people got pretty interested in trading when the value shot up!

SELECT      symbol AS 'Market', 
            DATE(TIME) AS 'Date', 
            ROUND(AVG(price),2) AS 'Average Price in USD', 
            ROUND(SUM(amount)) AS '# of Trades', 
            ROUND(SUM(price * amount),2) AS 'Bitcoin Traded'
FROM        Trade
JOIN        Market
ON          Market.symbol = Trade.Market_Symbol
WHERE       symbol = 'mtgoxUSD'
GROUP BY    DATE(TIME), '# of Trades', 'Average Price in USD'

Let me know what you think! You can access the data points and interactive charts here (Google has a slight bug trying to embed a 2 vertical axis chart, which is why the second graph is just an image) -> https://docs.google.com/spreadsheet/ccc?key=0AjEiltOWxrwvdE5Xb1hMbTFZS09oeGFWQS1IUm9iV1E

Feb 072012

In order to learn more about Bitcoin, I thought I would start by storing the chain itself into a normal mysql database for a school project I am doing in an effort to familiarize myself with it more for the Visualizer. (Hint: nothing is better than tying in something you are interested about to a class you are taking.) I was asked to put together a quick little presentation about comparing blockchain statistics to Bitcoin market statistics to give the class some ideas on what to do their semester long project on. Here is the Powerpoint I plan on presenting tomorrow. Enjoy!

 Posted by at 12:50 am