Analyzing Ethereum, Bitcoin, and 1200+ other Cryptocurrencies using PostgreSQL

Cryptocurrencies are fueling a modern day gold rush. Can data help us better understand this evolving market?
Update: Thank you everyone for making this #1 on Hacker News!
Read the HN discussion here.
Lately it seems like money has been growing on trees.
With trade volumes reaching billions of dollars a day and market caps hitting tens of billions of dollars, it’s no wonder that cryptocurrencies fuel the gold rush of the modern day.
We live in the age of digital currencies, with cryptocurrencies birthed within the decade. Yet already, there are more than a thousand cryptocurrencies in the market and an initial coin offering (ICO) almost daily.
As we embrace this new, proliferous market, it’s important that we try to understand what’s going on. There are many risks to observe at both the micro-level (e.g., personal investments) and macro-level (e.g., prevention of market crashes and major loss of capital). That’s where we come in.
We’re data people. Specifically, we’re the developers of TimescaleDB, a new open source time-series database built up from PostgreSQL. And we thought it would be insightful (and fun) to analyze the cryptocurrency market using PostgreSQL and TimescaleDB (plus R for data visualization).
For this analysis*, we looked at historical OHLCV price data on over 1200 cryptocurrencies (as of 6/26/2017; courtesy of CryptoCompare). While our current dataset represents only a daily record of rates, TimescaleDB scales easily to much finer-grained historical data. With the constant influx of new coins and exchanges, TimescaleDB can provide a reliable foundation for time-series data in the cryptocurrency market.
Here’s what you should take away from this post:
Several high-level insights into the cryptocurrency market
A better understanding of how TimescaleDB + PostgreSQL make time-series data analysis easier
Instructions on how to load this dataset yourself and draw your own insights (and perhaps find your own arbitrage opportunities!)
*Please note that nothing in this analysis should be construed as financial advice. If you’d like to draw your own conclusions, here are instructions to install TimescaleDB and download this cryptocurrency dataset.
So if you had invested $100 in Bitcoin 7 years ago, it would be worth…
Let’s start with some good old-fashioned FOMO. If you know anything about cryptocurrencies, you’ve probably heard of Bitcoin, the “granddaddy” of all cryptocurrencies. Turns out that if you had invested $100 in Bitcoin in July 2010, it would be worth over $5,000,000 today.
Bitcoin has had a pretty nice run since then (although taking a small dip recently):
![9B9DB53A-857D-43B2-8E5A-C6B4CA044B2B.png()
Using PostgreSQL, we’ve queried BTC’s prices at 2-week intervals, analyzing the rates for USD exchanges. (Note that “time_bucket” and “last” in this query are special TimescaleDB time-series data analysis functions not in PostgreSQL.)