2012 February » tl;dr

Giant Dendrogram on the Way!

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:

http://blockchain.info/tree/2677300

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'
 
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.

Blockchain Downloader and Market Updater

Bitcoin No Responses »

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)

SELECT      ROUND(VALUE), 
            COUNT(VALUE)
FROM        Outgoing
 
JOIN        TRANSACTION
ON          TRANSACTION.hash = Transaction_hash
 
JOIN        Block
ON          Block.hash = TRANSACTION.Block_hash
 
WHERE       TIME BETWEEN '2011-1-1' AND '2012-1-1'
 
GROUP BY    ROUND(VALUE)
 
ORDER BY    VALUE

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'
 
ORDER BY    TIME

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

Mining Some Stats on the Bitcoin Blockchain

Bitcoin No Responses »

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!