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