Analyzing Bitcoin, Ethereum, and 4100+ other cryptocurrencies using PostgreSQL and TimescaleDB

After a Crypto Winter in 2018, cryptocurrencies today are resurging. How can data help us better understand the Crypto Revival?

When Satoshi Nakamoto first published the Bitcoin whitepaper in 2008, they probably didn’t foresee the world of hodlers, lambos, buidlers, bitcoin maximalist carnivores, and n00bs asking “wen moon” in telegram channels, that their actions would create. In 11 years, crypto has gone from something completely esoteric to something seemingly everyone has heard about.

2019 has been a big year for crypto, so far. Some of the highlights include: the SEC approving it’s first token sale, the IRS tracking down crypto tax-evaders, university endowments investing in crypto and even Facebook announcing it’s own cryptocurrency. We also just this month saw over $1 Billion in Bitcoin transferred in a single transaction. All this, and more, indicates a revived interest in the crypto markets, since the highs of 2017 and lows of 2018, by everyone from institutional investors and banks to lay people trying to side hustle.

With the crypto markets once again awash with speculation and hype, it’s important to leverage all the tools at our disposal in order to make sense of the noise. Sometimes reading articles and email newsletters isn’t enough. You have to go directly to the data.

As the developers of TimescaleDB, an open-source time-series database powered by PostgreSQL, we’re data-driven people. So we thought it would be interesting to take a data-driven approach to analyzing the crypto market. For this analysis, we used PostgreSQL and TimescaleDB to analyze market data about Bitcoin, Ethereum and 4196 other cryptocurrencies, and used Tableau to visualize our results.

This post shares many high level insights about the crypto market since its inception and during recent years. We answer questions like:

• How has the price of Bitcoin and Ethereum changed in the past several years?
• Which new cryptocurrencies have been the most profitable in the past 3 months?
• What are the cryptocurrencies on the rise?
• What was the best day to “day-trade” Bitcoin?
• What countries have the highest trading volume of BTC today?
• Why is Bitcoin a terrible way to pay for pizza?

...and many more, as we dive into analysis of topics like Bitcoin and Ethereum price, new coin growth, trading volume and daily returns.

We also share how powerful SQL is as a query language for analyzing time-series data, how TimescaleDB and PostgreSQL further simplify time-series data analysis, and how using the two with Tableau visualizations can surface interesting insights from your data.

For the technically curious, you can learn how to create the dataset we used for this analysis, load it, and draw insights from it, in this companion tutorial post . In the tutorial, you will find step by step instructions on how to create the dataset using Python (including all code we used for the analysis), how to load the data into Timescale Cloud, a cloud-hosted version of TimescaleDB (with $300 in free credits for getting started easily), and how to connect your database in the cloud to Tableau to recreate the analysis and produce graphs.

About the data used for this analysis

For this analysis, we used historical OHLCV price data for over 4100 cryptocurrencies from 7/17/2010 to 9/16/2019, courtesy of CryptoCompare and their wonderful API. While the dataset we used only includes daily data, TimescaleDB easily scales to handle data from much finer grained time periods.

Some of you may recall that we did a similar analysis on crypto back in 2017, but so much has happened since then, including the addition of almost 3000 more cryptocurrencies and Bitcoin hitting nearly ~$20k in price, that we had to revisit this topic. Where applicable, we’ve included graphs to focus on recent history, from 2017-2019, updating the analysis from our previous post.

DISCLAIMER: At Timescale, we help companies harness the power of time series data to make sense of the past, monitor the present, and predict the future. However, nothing in this analysis should be construed as financial advice and we take no liability for your actions as a result of using the information contained in this post. You’re welcome to draw your own conclusions using the tools and data and take your own risks accordingly.

So if you’d invested $100 in Bitcoin 9 years ago, today it’d be worth…

When analyzing cryptocurrencies, we have to start with the original: Bitcoin. For any beginners, Bitcoin can be thought of as digital gold, because Bitcoin has built-in scarcity (only 21 million BTC will ever be produced), can be almost infinitely divided without losing its unit value, and is difficult to counterfeit. (As an aside, for those looking for an introduction to Bitcoin and other cryptocurrencies, two good places to start are The Princeton Bitcoin Book and The Internet of Money.)

Looking at historical BTC-USD prices since 2010, we see that BTC prices have slowly increased, with an almost exponential increase taking place between 2014 and 2018.

--Query 1
-- BTC 7 day prices
SELECT time_bucket('7 days', time) as period,      
	last(closing_price, time) AS last_closing_price
FROM btc_prices
WHERE currency_code = 'USD'
GROUP BY period
ORDER BY period

So to answer our original question (and probably create some FOMO), if you bought $100 worth of Bitcoin on 16 September 2010, the price of 1BTC was $0.0619, meaning $100 would have bought you 1615.5088853 BTC. Fast forward 9 years, that $100 would have grown to $16,476,736.67! Queue the lambos! (But of course you would have probably sold when BTC hit $100 back in 29 July 2013 :)).

Generating insights like this from time-series data takes no more than a 5 line SQL query thanks to TimescaleDB’s special timebucket and last functions, which are special functions exclusively created for TimescaleDB to simplify time-series analysis.

Why Bitcoin is a terrible way to pay for pizza

One thing that’s evident from the data is that Bitcoin is volatile. Bitcoin’s price volatility may mean that its main use case is being a store of value rather than a means of exchange. The Bitcoin Pizza Guy, who paid 10,000 BTC for a 2 pizzas back in 2010, would probably agree, as those 10,000BTC are worth over a $100,000,000 today!

Moreover, the time period between 2017 and 2019 saw a lot of ups and downs, so let’s zoom in on that period below:

As Figure 2 shows, 2017-2019 has arguably been the most exciting and perhaps also the most painful time in the Bitcoin market, with Bitcoin prices reaching a high of nearly $20,000, before crashing to under $7,000 three months later. That decline continued throughout 2018, with some calling it the first crypto bear market and others a “Crypto Winter”. However, the recent run in price made by Bitcoin since April 2019 may be the first indication of the end of the Crypto Winter, with BTC prices reaching over $12,000 in June 2019.

The best day to “day-trade” Bitcoin? February 26, 2014.

In order to better understand Bitcoin’s volatility, let’s look at the daily return on BTC, as a factor of the previous day’s rate. This is simple to do using PostgreSQL window functions, as shown in the query below.

--Query 2
-- BTC daily return
SELECT time,
	closing_price / lead(closing_price) over prices AS daily_factor
FROM (  
 SELECT time,         
  closing_price  
FROM btc_prices  
WHERE currency_code = 'USD'  
GROUP BY 1,2
) sub window prices AS (ORDER BY time DESC)

From Figure 3.1, we can see large amounts of volatility in BTC price from 2010, culminating in a huge spike in daily return factor in February 2014. In fact, within just 7 days we saw the day with the lowest daily return factor — 0.428 on 20 February 2014 — and the highest ever daily return factor — 4.368 on 26 February 2014! It’s no wonder that stablecoins, or price-stable cryptocurrencies are being looked into as alternatives to use as a means of exchange within apps or for everyday transactions. Imagine paying for everything you buy in Bitcoin and the headaches that daily and weekly BTC price volatility causes.

Once again, let’s zoom in on the 2017-2019 time period:

From Figure 3.2, we see that Bitcoin’s most profitable day since the start of 2017 occurred recently on 25 August 2019, with a daily return of 1.99 times the previous day’s rate. The day with the biggest loss went to 16 January 2018, with a daily return factor of 0.8276, with 14 September 2017 coming in second biggest loss, with a daily return factor of 0.8379.

Bitcoin’s top countries by trading volume: US, Japan, South Korea, and... Poland!?

Cryptofever has taken the world by storm, with lots of adoption taking place outside of the USA, most notably in places like Europe and Asia. We can get a sense for how crypto is being adopted in different regions of the world by looking at Bitcoin trading volume in different fiat currencies over time.

--Query3
-- BTC trading volumes by currency 
SELECT time_bucket('14 days', time) as period,
	currency_code,       
    	sum(volume_btc)
FROM btc_prices
GROUP BY currency_code, period
ORDER BY period;

From figure 4 above, we can see that China saw huge amounts of bitcoin trading volume before government intervention which made buying Bitcoin illegal in mid 2017. We can more clearly see how drastic this effect was by looking at Figure 5 below, which is a bar graph version of Figure 4, showing volume of BTC trade in different fiat by year:

From figure 5, we can more clearly see the rise in Chinese (CNY) Bitcoin trading activity and how government intervention in 2017 brought that to a halt. Moreover, we can see Japan (JPY) and South Korea (KRW) overtaking Europe with respect to Bitcoin trading volume, with more volume than the Euro (EUR) during 2017 and 2018. This confirms the USA, Japan and South Korea as the world’s 3 largest bitcoin markets.

Furthermore, if we remove USD, CNY, JPY, KRW and EUR from the list of fiats we can get a sense for the trend in Bitcoin adoption outside the largest markets, as shown in Figure 6 below:

Note how South Africa (ZAR) has seen rising BTC trade volumes since 2015, the dramatic increase in trading volume from Hong Kong (HKD) in 2017 and subsequent decrease, and that the currency with the highest trade volume outside the big 5 is none other than the Polish Zloty (PLN)!

Figure 6.1 shows trading volumes of BTC in PLN since 2014:

Now if you’d bought $100 worth of Ethereum in 2015, today it’d be worth...

Ethereum is popularly regarded as the cryptocurrency with the second largest interest base after Bitcoin, however, it is fundamentally different from Bitcoin. While Bitcoin is considered to be digital gold, Ether is more like fuel (gas) that runs transactions on the Ethereum network.

Since the first currency with which you could buy Ethereum was Bitcoin, let’s take a look at historical ETH prices in BTC, shown in Figure 7 below:

-- Q4
-- ETH prices in BTC in 7 day intervals
SELECT  
	time_bucket('7 days', time) AS time_period,   
    last(closing_price, time) AS closing_price_btc
FROM crypto_prices
WHERE currency_code='ETH'
GROUP BY time_period
ORDER BY time_period

Figure 7 shows us Ethereum (ETH) closing prices since 3 August 2015 in weekly intervals, expressed in BTC. Notice that 2017 was a rollercoaster year for ETH, with the currency seeing it’s an all time high of 0.1402 BTC on 12 June 2017 and then crashing back down to 0.0288 BTC 4 December 2017, less than 6 months later.

Let’s take a look at recent Ethereum prices by zooming in on the period since 2017 in Figure 8 below:

From Figure 8 above, it seems that ETH then went on another bull run in early 2018, with prices reaching 0.1052 BTC in the period around 22 January 2018. Since then, it seems like ETH prices have been trending downward, with the price on 16 September 2019 reaching 0.0188 BTC. While that’s not great for investors, it may prove to be a blessing for developers and decentralized application users in Ethereum’s ecosystem, as gas costs would be cheaper, perhaps decreasing the barriers to adoption.

Crypto Convertibles (not the car kind)

Since most people don’t think about prices in BTC (yet), and given how volatile BTC is, it’s useful to also examine ETH prices expressed different fiat currencies. There are two ways this could be done: First by looking at ETH prices directly in different fiat currencies, like we did for for Bitcoin and USD in Figure 1 above. Secondly, we could convert ETH prices in BTC to fiat currency prices, by looking at that day’s BTC to fiat exchange rate. For illustration purposes, we’ll use the second technique. While this may seem like a strange choice, it’s worth noting that conversions from one cryptocurrency to another and then to a fiat currency are fairly common in the cryptocurrency trading world. This is because many exchanges support buying cryptocurrencies with other cryptocurrencies (mainly BTC and ETH), but not all crypto currencies are purchasable directly with fiat currency.

In order to examine ETH prices in different fiat currencies in PostgreSQL, we joined two tables and used filters, as the code below illustrates:

--Q5
-- ETH prices in fiat
SELECT time_bucket('7 days', c.time) AS time_period,  
	last(c.closing_price, c.time) AS last_closing_price_in_btc,
    last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'USD') AS last_closing_price_in_usd, 
    last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'EUR') AS last_closing_price_in_eur,
    last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'CNY') AS last_closing_price_in_cny,
    last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'JPY') AS last_closing_price_in_jpy,  
    last(c.closing_price, c.time) * last(b.closing_price, c.time) FILTER (WHERE b.currency_code = 'KRW') AS last_closing_price_in_krw
FROM crypto_prices c
JOIN btc_prices b   
	ON time_bucket('1 day', c.time) = time_bucket('1 day', b.time)
WHERE c.currency_code = 'ETH'
GROUP BY time_period
ORDER BY time_period

One thing to notice is that the Figures 9.1-9.4 all the same shape, since they are another expression of the ETH price in BTC. The main difference in the graphs is the scale of the Y axis, as this reflects the respective currency’s BTC exchange rate. This is as a result of the decision to use the BTC-Fiat exchange rate for this conversion, rather than direct ETH-Fiat prices. When plotted all on the same axis, we get Figure 10 below:

Fortunately, you can now directly purchase ETH using fiat currency on many exchanges. So let’s look at historical ETH prices in USD,in Figure 11 below:

-- ETH prices in USD
SELECT time_bucket('7 days', time) as period,       
	last(closing_price, time) AS last_closing_price
FROM eth_prices
WHERE currency_code = 'USD'
GROUP BY period
ORDER BY period

Figure 11 above tells a similar story to that of Figure 8, as we can see the ETH bull run to $1,359 on 8 January 2018. ETH prices in USD have been trending downward since then, with the price on 16 September 2019 falling to $199.

So to answer our original question, if you bought $100 worth of Ethereum on 16 September 2015, the price of 1ETH was around $0.89, meaning $100 would have bought you 112.3596 ETH. That $100 would now have grown to $22,359.55! However, the best time to sell would’ve been during the peak Jan 2018, your 112.3596 ETH would’ve been worth $152 696.63! This just goes to show how important it is to time the market!

(One piece of analysis which we didn’t do, but encourage readers to do, is to examine the developer activity in the Ethereum ecosystem (eg Github commits/ issues) and see if that correlates to price in some way.)

Tracking 4000+ other cryptocurrencies, starting from inception

While we can’t see when exactly coins ICO’d or first got listed on exchanges, we can track the date a coin first got added to CryptoCompare as a proxy for its launch date. This allows us to track the launch of different coins over time, as seen in Figure 11.

--Q6
--Crypto by date of first data
SELECT ci.currency_code, min(c.time)
FROM currency_info ci JOIN crypto_prices c ON ci.currency_code = c.currency_code
AND c.closing_price > 0
GROUP BY ci.currency_code
ORDER BY min(c.time) DESC

It’s easy to conclude that a bull run in BTC prices might have fueled a massive increase in developer activity in the crypto space. The evidence for this comes from the the 738 new cryptos released in 2017, a year where BTC almost 10x’ed its BTC price between Jan(1 BTC = $2,435 on Jan 1) and December (1 BTC = $19,345 on Dec 16). With bitcoin prices steadily increasing, it’s no wonder that hundreds of developers tried their hand at creating their own cryptocurrency in the hopes of maybe building the next Bitcoin.

However, despite the dramatic crash in BTC prices throughout most of 2018, it’s interesting to see the amount of new cryptocurrencies launched in 2018 year being the highest ever, with 771 new cryptocurrency projects launching that year.

Figure 12 shows us the result of examining the number of new cryptos launched by day.

--Q7
-- Number of new cryptocurrencies by day
SELECT day, COUNT(code)
FROM (  
	SELECT min(c.time) AS day, ci.currency_code AS code  
    FROM currency_info ci JOIN crypto_prices c ON ci.currency_code = c.currency_code  
    AND c.closing_price > 0  
   GROUP BY ci.currency_code  
   ORDER BY min(c.time))a
GROUP BY day
ORDER BY day DESC

Two days in particular are super interesting. On 2 December 2014, we saw data for 81 cryptocurrencies being added to Cryptocompare and on 26 May 2017, we saw a whopping 134 new cryptos being added on that day!

These coins had higher transaction volumes than Bitcoin this month (Hint: It’s not Bitcoin Cash)

With over 4000+ cryptocurrencies out there and new ones coming out everyday, it can be hard to pick which ones are worth paying attention to. One helpful metric for spotting coins on the rise is the transaction volume. In Figure 11, we looked at the transaction volume for all 4054 coins in our dataset over the 14 day period from September 2 to 16 2019.

--Q8
--Crypto transaction volume during a certain time period
SELECT 'BTC' as currency_code,      
	sum(b.volume_currency) as total_volume_in_usd
FROM btc_prices b
WHERE b.currency_code = 'USD'
AND now() - date(b.time) < INTERVAL '14 day'
GROUP BY b.currency_code
UNION
SELECT c.currency_code as currency_code,      
	sum(c.volume_btc) * avg(b.closing_price) as total_volume_in_usd
FROM crypto_prices c JOIN btc_prices b ON date(c.time) = date(b.time)
WHERE c.volume_btc > 0
AND b.currency_code = 'USD'
AND now() - date(b.time) < INTERVAL '14 day'
AND now() - date(c.time) < INTERVAL '14 day'
GROUP BY c.currency_code
ORDER BY total_volume_in_usd DESC 

It’s surprising to see that Bitcoin, despite being the original cryptocurrency, did not have the largest transaction volume over the time period in question. That honor belonged to USD Tether, USD Tether -- a fiat backed stablecoin, with Ethereum coming in second and Litecoin, the proverbial silver to Bitcoin’s gold, coming in third. Bitcoin (BTC) had the fourth highest USD transaction volume in that 14 day period, followed by Rippple (XRP), a global payments system which has partnered with several banks and payment processors, and EOS, a smart contract platform.

What are the most profitable new cryptocurrencies?

Another way of making sense of the flood of new currencies is to look at how profitable coins are, as measured by total daily return. By honing in on the currencies with the highest increase in rate by day, we can gain a different perspective on which currencies might be worth looking into further.

One question to ask is which cryptocurrency has the highest daily return during a certain time period. Figures 14.1 and 14.2 below show cryptocurrencies sorted by their maximum daily return factor between June 16 and September 16 2019.

--Q9
--Top crypto by daily return
WITH   
	prev_day_closing AS (
SELECT   
	currency_code,   
    time,   
    closing_price,   
    LEAD(closing_price) OVER (PARTITION BY currency_code ORDER BY TIME DESC) AS prev_day_closing_price
FROM    
	crypto_prices  
),   
	daily_factor AS (
SELECT   
	currency_code,   
	time,   
	CASE WHEN prev_day_closing_price = 0 THEN 0 ELSE closing_price/prev_day_closing_price END AS daily_factor
FROM   
	prev_day_closing
)
SELECT   
	time,   
	LAST(currency_code, daily_factor) as currency_code,   
    MAX(daily_factor) as max_daily_factor
FROM   
	daily_factor
GROUP BY   
	time

From Figure 14.1 above, we see that Mixin (MIXI), an Ethereum based token, comes out on top with a maximum daily return factor of over 25 million times the previous day’s rate. BOMB, an experimental deflationary currency, had the second highest absolute daily return with a return factor of over 700,000.  We can get a better look at the rest of the data by removing these two outliers, as seen in Figure 14.2 below:

From Figure 14.2, we see other cryptos that have had big days in the past 3 months were, Double Eagle Coin (XDE2), Gold Reserve (XGR), SoulCoin (SOUL) and CCCoin (CCC).

Furthermore, it’s interesting to note the difference in order of magnitude between the coins with the top daily return in the past 3 months, with MIXI achieving a daily return factor in the millions, BOMB in the hundreds of thousands and the next highest coins in the tens of thousands.

Another interesting thing to look at is the amount of times a coin had the top daily return during a certain period of time. Figure 15 shows us the coins with the highest frequency of having the top daily return during the 3 months between 16 June and 16 September 2019.

The coins with the highest frequency of having the top daily return are MIXI (discussed above), with 5 unique days with the top daily return, Bitether (BTR), a cryptocurrency built on the Ethereum platform, with unique 3 days, IceChain (ICHX) coming in third, with two unique days.

Conclusion

In this post, we used the power of PostgreSQL and TimescaleDB to analyze a public cryptocurrency dataset of over 4100 cryptocurrencies over the time period 2010 to 2019. We examined time-series trends in Bitcoin and Ethereum prices, new coin growth, trading volume, daily returns, and more.

While our analysis aimed to provide a taste of what’s possible using PostgreSQL and TimescaleDB, we encourage you to take the tools we used and apply them to different crypto datasets and gain deeper insights!

We’ve created a companion tutorial post for those interested in re-creating this analysis or looking for a starting point to perform your own analysis. In the tutorial, you will find step by step instructions on how to create the dataset using Python (including all code we used for the analysis), how to load the data into Timescale Cloud and how to connect your database in the cloud to Tableau to recreate the analysis and produce graphs. If you do perform your own analysis, let us know what interesting insights you find!

Moreover, you can dig in to the technical side of Timescale and how we made PostgreSQL scalable for time-series data in this detailed post. If you’re interested in experiencing the power of Timescale for your time series data, sign up for Timescale Cloud and get $300 free in credits!

Please drop your comments below and share this post with others whom you think would enjoy it. For follow up questions or comments, reach out to us on Twitter (@TimescaleDB or @avthars), our community Slack channel, or reach out to me directly via email (avthar at timescale dot com).