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