Analyzing Ethereum, Bitcoin and more than 1200 other cryptocurrencies using PostgreSQL
- Transfer
Cryptocurrencies are the driving force behind the new gold rush. The author suggests using data analysis to better understand this emerging market.
Recently, there is a feeling that money grows on trees.
The volume of stock trading reaches millions of dollars, and market capitalization - billions . It is time to talk about the gold rush, fueled by the emergence of an increasing number of new cryptocurrencies.
We live in an era of digital currencies. Appearing less than 10 years ago, the concept of cryptocurrency has already become widespread today. Despite such a short time, there are already over a thousand different cryptocurrencies on the market, and ICOs occur almost every day .
As we get used to the new, rapidly gaining market, it is important to try to understand what is happening to it. There are many risks, both at the micro level (for example, personal investment) and at the macro level (for example, preventing market crashes or large capital losses). This is where we come into play.
We are engaged in data, and more precisely, I represent the development team of TimescaleDB , a new open source database based on PostgreSQL for working with time series. We thought it would be useful and exciting to analyze the cryptocurrency market using PostgreSQL and TimescaleDB (and R to visualize the received data).
In the course of the work, we analyzed historical data on OHLC-charts.for more than 1200 cryptocurrencies, courtesy of us CryptoCompare (the last sample date - June 26 of this year). The step in this particular dataset is 24 hours, but it should be noted that TimescaleDB scales easily to work with much shorter time periods. Our product, in particular, is perfect for playing the role of a basic tool for working with the growing flow of data on new coins and exchanges.
Here is what you can learn for yourself from this post:
Disclaimer: this analysis should not be considered as a recommendation for those or other financial transactions. If you want to conduct your own analysis, pay attention to the instruction , which describes the installation of TimescaleDB and the download of CryptoCompare data .
Let's start with the good old syndrome of lost profits . If you are at least familiar with cryptocurrencies, you have probably heard about Bitcoin, the “great-grandfather” of all cryptocurrencies. It turns out that if in July 2010 you invested $ 100 in it, today this amount would increase to $ 5 million.
During this time, Bitcoin showed a rather pleasant dynamics (even taking into account the relatively recent collapse):
BTC to US dollar exchange rate at the time of closing the stock exchange over the past 7 years.
Using PostgreSQL, we requested BTC price readings at two-week intervals, analyzing the exchange rate to the US dollar on the stock exchanges. Note: time_bucket and last in this query are TimescaleDB's own functions (not available in PostgreSQL) used to analyze time series.
However, it cannot be said that everything at BTC was always smooth. Let's take a closer look at the daily course volatility and do the calculations using the powerful PostgreSQL window functions :
The rate of division of the current day BTC / USD rate of the previous (7-year period)
Due to the relative immaturity of the market, the Bitcoin rate is subject to significant fluctuations. Stable value growth in general speaks of the success of cryptocurrency, but there is one history in its history that is clearly out of the general picture, a period of sharp growth observed in early 2014. Having reviewed this period in more detail, we note major jumps in February-March 2014. Investors who invested in BTC at the peak of the market had to wait considerably, because the course soon stabilized, and soon did not reach the indicators at which the sale of bitcoins purchased at that time became a profitable business.
The rate of division of the BTC / USD of the current day by the rate of the previous (2014)
Cryptocurrency market is an international phenomenon. Studying trading volumes in terms of currencies, we noticed something interesting:
BTC trade volumes in various fiat currencies over the past 7 years (two-week intervals, composite bars)
In 2014, there was a small jump in the value of Bitcoin in China, caused by the alleged devaluation of the yuan and the weakening of the domestic stock market of the country. This was followed by the boom of 2016 and early 2017: the Chinese currency dominated bitcoin trading.
The share of yuan in Bitcoin trade for the last year (two-week intervals)
In just a few months, this figure has dropped sharply.
What is the reason? Here it is time to go beyond the analysis of numerical data and do the good old research. Our conclusions well illustrate the fact that it is impossible to rely only on quantitative data to study the market.
In early 2017, the People’s Bank of China put into effect decrees restricting the activities of cryptocurrency exchanges. Already in February, the two largest stock exchanges in the country (OKCoin and Huobi.com) suspended the withdrawal of cryptocurrency in exchange for foreign currency, and by the middle of this year the flow of Chinese transactions had disappeared. At the same time, Japan became the leader in the number of transactions with Bitcoins. It even went so far that Bitcoin was recognized as a legal currency in April 2017.
The amount of BTC in various currencies after a sharp decline in the share of BTC / CNY transactions in 2017. BTC / JPY - new leader in terms of volume (two-week intervals)
Despite the fact that the “Bitcoin train went away” back in 2010, there is no cause for concern. Many observers agree that even against the background of its high volatility, the Ethereum price drops even look impressive (and the recent “correction” is another proof of this). Let's look at the prices of Ethereum in Bitcoin equivalent (as it is usually accepted to quote):
The rate of ETH to BTC at the close of the trading day for the last 3 years
However, as we know, Bitcoin itself is not marked by noticeable stability, which reduces the usefulness of the above chart. Let's look at ETH prices in fiat currencies, with the help of daily BTC exchange prices for fiat currencies. (To do this, we use the useful properties of the Postgres JOINs and several clever filters):
ETH rate against three fiat currencies at the close of the trading day in the last 3 years
In the first year of its existence, ETH exceeded any of the annual BTC growth rates for the entire history of the latter. An impressive 530% increase in the average closing price compared to the previous year is a good start. In general, by 2017, the cumulative growth rate for all these years fell in 2017 compared to 2016 to 200%. However, even this result still looks impressive for any other asset. As for the last half year, now prices for ETH have grown by 3000%. Therefore, if you invested $ 100 in ETH in January of this year (almost 7 months ago), today their value would be already $ 3 thousand.
Expressing the value of ETH in stable currencies (USD, EUR, CNY), we see that all three charts have the same shape. In the past six months, there has been a clear increase in all currency equivalents, except for BTC. The ETH / BTC rate chart, being similar to fiat currency charts, is much more prone to BTC cost fluctuations. As a result, attempts to express the price of ETH in BTC create an improbable impression of instability first. Obviously, the BTC is still too young a currency to consider as its base.
We hope that this brief overview of the BTC and ETH trends has enabled you to better understand the chaotic world of cryptocurrencies. So, what do we do with the other 1200 cryptocurrencies?
Well, for starters, let's use our data set to track its origin:
Important note: Our data set contains information at the time of its collection, which could have happened after the ICO.
The timestamp of the first entry for each cryptocurrency (descending)
The market is constantly evolving, and besides, judging by the daily growth of new cryptocurrencies, it is also constantly expanding. The list published above contains only 20 of the newest tokens as of June 26, and allows you to get an idea of how many new cryptocurrencies appear every week.
Let's calculate the number of new cryptocurrencies that appear every day based on the first date of the pricing information on them:
Number of new cryptocurrencies per day for the last 4 years
Number of new cryptocurrencies per day for the current year
The number of new cryptocurrencies by dates, daily statistics (descending)
When we request information about the first appearance of data on cryptocurrencies (to track their “age”), it becomes noticeable that the market is not only a group of investors, there is another category of its participants — the creators digital assets. Just recently, on May 25–28, according to our recruitment, there was a large influx of new coins — over 300 new tokens in less than a week. (Since our set only records price information on cryptocurrencies, information about their appearance may not correspond to the dates of the ICO.)
Cryptocurrency today is so much that it becomes difficult to distinguish between sensible and doubtful. How can you determine which ones are worthy of attention? Here you have one of the metrics: the total volume of exchange transactions for the last week.
Total volume of transactions for 10 main cryptocurrencies in US dollars for the last week (descending)
Total volume of transactions for 10 main cryptocurrencies in US dollars for the last week (descending)
A small explanation of this request: data on BTC and other cryptocurrencies live in different tables. Therefore, we have to combine these two queries with UNION. Earlier, we also decided that we want to receive quotes in fiat currencies (for example, in dollars), and not in BTC. Therefore, the second half of the query combines the data with a BTC table for converting BTC to USD.
Leaders in terms of operations were, oddly enough, Bitcoin and Ethereum. But the following members of the charts - Litecoin (LTC) , Ripple (XRP) , and Ethereum Classic (ETC)go almost on equal footing. Litecoin has been on the market for five years, is almost identical to Bitcoin and is often considered a key player in the market. Ripple, which is positioned as a bank coin for representatives of the international commercial market and works for a more specific audience, is also considered a promising and gaining momentum. It is also interesting that in the top five there is not only ETH, but also ETC, which allows us to say that the market today is strongly focused on Ethereum.
Another way to “sweep through” a long list of cryptocurrencies is to analyze their profitability, for example, the indicator of total daily profit. In our set there are price data for more than 1200 cryptocurrencies. If you look at the largest rate increase per day, you can identify the leaders of intraday trading.
The highest rates of cryptocurrency intraday price difference (in descending date)
Let's define the cryptocurrency with the largest daily profitability indicator. To calculate the daily profit, we will use the window function again, and to search for the cryptocurrency that brought the most income on each single day we use the last functionfrom the TimescaleDB set.
Conclusion over the past three months shows the quantitative superiority of AMIS (a 168-fold increase in value on June 15). This cryptocurrency showed the biggest increase in 15 different days. However, after looking more closely at it, we note that high growth is due to equally high price fluctuations: the cost of AMIS is often rolled back to zero level after each increase.
The closing price of the day for AMIS in the last five months.
Another leader of this sample, YOVI, showed the best result 3 times, but is also subject to similar unreliable trends as AMIS:
The closing price of the day for YOVI in the last five months
Despite the instability of this pair of trends, they nevertheless, they look more promising compared to ETH, the cost of which steadily dropped in the first year of its existence (2015):
ETH day closing price for 2015
(Repeated disclaimer: TimescaleDB does not support any of these cryptocurrencies and is not responsible for your investment in them and any possible losses associated with them.)
In this material, we made several conclusions from an open data set on cryptocurrency, demonstrating the strengths of PostgreSQL and TimescaleDB. Nevertheless, it should be remembered that the cryptocurrency market will inevitably change next month, next week or even tomorrow.
However, if you want to independently study this set and carry out your analysis, the appropriate instruction for downloading data and installing TimescaleDB is available at your service .
If you want to learn more about TimescaleDB and how it improves PostgreSQL's efficiency in working with time series, we recommend that you familiarize yourself with the technical post .
Recently, there is a feeling that money grows on trees.
The volume of stock trading reaches millions of dollars, and market capitalization - billions . It is time to talk about the gold rush, fueled by the emergence of an increasing number of new cryptocurrencies.
We live in an era of digital currencies. Appearing less than 10 years ago, the concept of cryptocurrency has already become widespread today. Despite such a short time, there are already over a thousand different cryptocurrencies on the market, and ICOs occur almost every day .
As we get used to the new, rapidly gaining market, it is important to try to understand what is happening to it. There are many risks, both at the micro level (for example, personal investment) and at the macro level (for example, preventing market crashes or large capital losses). This is where we come into play.
We are engaged in data, and more precisely, I represent the development team of TimescaleDB , a new open source database based on PostgreSQL for working with time series. We thought it would be useful and exciting to analyze the cryptocurrency market using PostgreSQL and TimescaleDB (and R to visualize the received data).
In the course of the work, we analyzed historical data on OHLC-charts.for more than 1200 cryptocurrencies, courtesy of us CryptoCompare (the last sample date - June 26 of this year). The step in this particular dataset is 24 hours, but it should be noted that TimescaleDB scales easily to work with much shorter time periods. Our product, in particular, is perfect for playing the role of a basic tool for working with the growing flow of data on new coins and exchanges.
Here is what you can learn for yourself from this post:
- A few valuable general conclusions about the state of affairs in the cryptocurrency market.
- Better understanding of how a bunch of TimescaleDB + PostgreSQL can simplify data analysis by time series.
- Get instructions for self-loading this dataset, discover new trends (and maybe even make your own strategy for entering the market with their help!).
Disclaimer: this analysis should not be considered as a recommendation for those or other financial transactions. If you want to conduct your own analysis, pay attention to the instruction , which describes the installation of TimescaleDB and the download of CryptoCompare data .
So, if 7 years ago you invested $ 100 in Bitcoin, now it would be worth ...
Let's start with the good old syndrome of lost profits . If you are at least familiar with cryptocurrencies, you have probably heard about Bitcoin, the “great-grandfather” of all cryptocurrencies. It turns out that if in July 2010 you invested $ 100 in it, today this amount would increase to $ 5 million.
During this time, Bitcoin showed a rather pleasant dynamics (even taking into account the relatively recent collapse):
-- BTC USD prices by two week intervals SELECT time_bucket('14 days', time) asperiod,
last(closing_price, time) AS last_closing_price
FROM btc_prices
WHERE currency_code = 'USD'GROUPBY currency_code, periodORDERBYperiod;BTC to US dollar exchange rate at the time of closing the stock exchange over the past 7 years.
Using PostgreSQL, we requested BTC price readings at two-week intervals, analyzing the exchange rate to the US dollar on the stock exchanges. Note: time_bucket and last in this query are TimescaleDB's own functions (not available in PostgreSQL) used to analyze time series.
We hope that you did not buy bitcoins in February 2014 ...
However, it cannot be said that everything at BTC was always smooth. Let's take a closer look at the daily course volatility and do the calculations using the powerful PostgreSQL window functions :
-- Daily BTC returns by daySELECTtime,
closing_price / lead(closing_price) over prices AS daily_factor
FROM (
SELECTtime,
closing_price
FROM btc_prices
WHERE currency_code = 'USD'GROUPBY1,2
) sub window prices AS (ORDERBYtimeDESC);The rate of division of the current day BTC / USD rate of the previous (7-year period)
Due to the relative immaturity of the market, the Bitcoin rate is subject to significant fluctuations. Stable value growth in general speaks of the success of cryptocurrency, but there is one history in its history that is clearly out of the general picture, a period of sharp growth observed in early 2014. Having reviewed this period in more detail, we note major jumps in February-March 2014. Investors who invested in BTC at the peak of the market had to wait considerably, because the course soon stabilized, and soon did not reach the indicators at which the sale of bitcoins purchased at that time became a profitable business.
The rate of division of the BTC / USD of the current day by the rate of the previous (2014)
Goodbye China, hello Japan
Cryptocurrency market is an international phenomenon. Studying trading volumes in terms of currencies, we noticed something interesting:
-- BTC trading volumes by currency SELECT time_bucket('14 days', time) asperiod,
currency_code,
sum(volume_btc)
FROM btc_prices
GROUPBY currency_code, periodORDERBYperiod;BTC trade volumes in various fiat currencies over the past 7 years (two-week intervals, composite bars)
In 2014, there was a small jump in the value of Bitcoin in China, caused by the alleged devaluation of the yuan and the weakening of the domestic stock market of the country. This was followed by the boom of 2016 and early 2017: the Chinese currency dominated bitcoin trading.
The share of yuan in Bitcoin trade for the last year (two-week intervals)
In just a few months, this figure has dropped sharply.
What is the reason? Here it is time to go beyond the analysis of numerical data and do the good old research. Our conclusions well illustrate the fact that it is impossible to rely only on quantitative data to study the market.
In early 2017, the People’s Bank of China put into effect decrees restricting the activities of cryptocurrency exchanges. Already in February, the two largest stock exchanges in the country (OKCoin and Huobi.com) suspended the withdrawal of cryptocurrency in exchange for foreign currency, and by the middle of this year the flow of Chinese transactions had disappeared. At the same time, Japan became the leader in the number of transactions with Bitcoins. It even went so far that Bitcoin was recognized as a legal currency in April 2017.
The amount of BTC in various currencies after a sharp decline in the share of BTC / CNY transactions in 2017. BTC / JPY - new leader in terms of volume (two-week intervals)
And now let's discuss what it would be like if you invested $ 100 at ETH in January 2017 ...
Despite the fact that the “Bitcoin train went away” back in 2010, there is no cause for concern. Many observers agree that even against the background of its high volatility, the Ethereum price drops even look impressive (and the recent “correction” is another proof of this). Let's look at the prices of Ethereum in Bitcoin equivalent (as it is usually accepted to quote):
-- ETH prices in BTC by two week intervalsSELECT time_bucket('14 days', c.time) asperiod,
last(c.closing_price, c.time) AS last_closing_price_in_btc
FROM crypto_prices c
WHERE c.currency_code = 'ETH'GROUPBYperiodORDERBYperiod;The rate of ETH to BTC at the close of the trading day for the last 3 years
However, as we know, Bitcoin itself is not marked by noticeable stability, which reduces the usefulness of the above chart. Let's look at ETH prices in fiat currencies, with the help of daily BTC exchange prices for fiat currencies. (To do this, we use the useful properties of the Postgres JOINs and several clever filters):
-- ETH prices in BTC, USD, EUR, and CNY by two week intervalsSELECT time_bucket('14 days', c.time) asperiod,
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
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'GROUPBYperiodORDERBYperiod;ETH rate against three fiat currencies at the close of the trading day in the last 3 years
In the first year of its existence, ETH exceeded any of the annual BTC growth rates for the entire history of the latter. An impressive 530% increase in the average closing price compared to the previous year is a good start. In general, by 2017, the cumulative growth rate for all these years fell in 2017 compared to 2016 to 200%. However, even this result still looks impressive for any other asset. As for the last half year, now prices for ETH have grown by 3000%. Therefore, if you invested $ 100 in ETH in January of this year (almost 7 months ago), today their value would be already $ 3 thousand.
Expressing the value of ETH in stable currencies (USD, EUR, CNY), we see that all three charts have the same shape. In the past six months, there has been a clear increase in all currency equivalents, except for BTC. The ETH / BTC rate chart, being similar to fiat currency charts, is much more prone to BTC cost fluctuations. As a result, attempts to express the price of ETH in BTC create an improbable impression of instability first. Obviously, the BTC is still too young a currency to consider as its base.
What about the other 1200 cryptocurrencies?
We hope that this brief overview of the BTC and ETH trends has enabled you to better understand the chaotic world of cryptocurrencies. So, what do we do with the other 1200 cryptocurrencies?
Well, for starters, let's use our data set to track its origin:
Important note: Our data set contains information at the time of its collection, which could have happened after the ICO.
-- Currencies sorted by first time we have data for themSELECT 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 > 0GROUPBY ci.currency_code
ORDERBYmin(c.time) DESC; currency_code | min
---------------+------------------------
CIR | 2017-06-26 20:00:00+00
MDC | 2017-06-26 20:00:00+00
WBTC | 2017-06-26 20:00:00+00
NET | 2017-06-26 20:00:00+00
NAS2 | 2017-06-26 20:00:00+00
TPAY | 2017-06-26 20:00:00+00
MRSA | 2017-06-26 20:00:00+00
XCI | 2017-06-26 20:00:00+00
PAY | 2017-06-19 20:00:00+00
SNM | 2017-06-18 20:00:00+00
LGD | 2017-06-17 20:00:00+00
SNT | 2017-06-16 20:00:00+00
IOT | 2017-06-12 20:00:00+00
QRL | 2017-06-09 20:00:00+00
MGO | 2017-06-09 20:00:00+00
CFI | 2017-06-08 20:00:00+00
VERI | 2017-06-07 20:00:00+00
EQT | 2017-06-07 20:00:00+00
ZEN | 2017-06-05 20:00:00+00
BAT | 2017-05-31 20:00:00+00The timestamp of the first entry for each cryptocurrency (descending)
The market is constantly evolving, and besides, judging by the daily growth of new cryptocurrencies, it is also constantly expanding. The list published above contains only 20 of the newest tokens as of June 26, and allows you to get an idea of how many new cryptocurrencies appear every week.
Let's calculate the number of new cryptocurrencies that appear every day based on the first date of the pricing information on them:
-- Number of new currencies with data sorted by daySELECTday, COUNT(code)
FROM (
SELECTmin(c.time) ASday, ci.currency_code AS code
FROM currency_info ci JOIN crypto_prices c ON ci.currency_code = c.currency_code
AND c.closing_price > 0GROUPBY ci.currency_code
ORDERBYmin(c.time)
)a
GROUPBYdayORDERBYdayDESC;Number of new cryptocurrencies per day for the last 4 years
Number of new cryptocurrencies per day for the current year
day | count
------------------------+-------
2017-06-26 20:00:00+00 | 8
2017-06-19 20:00:00+00 | 1
2017-06-18 20:00:00+00 | 1
2017-06-17 20:00:00+00 | 1
2017-06-16 20:00:00+00 | 1
2017-06-12 20:00:00+00 | 1
2017-06-09 20:00:00+00 | 2
2017-06-08 20:00:00+00 | 1
2017-06-07 20:00:00+00 | 2
2017-06-05 20:00:00+00 | 1
2017-05-31 20:00:00+00 | 5
2017-05-28 20:00:00+00 | 29
2017-05-27 20:00:00+00 | 13
2017-05-26 20:00:00+00 | 32
2017-05-25 20:00:00+00 | 303
2017-05-16 20:00:00+00 | 1
2017-05-15 20:00:00+00 | 7The number of new cryptocurrencies by dates, daily statistics (descending)
When we request information about the first appearance of data on cryptocurrencies (to track their “age”), it becomes noticeable that the market is not only a group of investors, there is another category of its participants — the creators digital assets. Just recently, on May 25–28, according to our recruitment, there was a large influx of new coins — over 300 new tokens in less than a week. (Since our set only records price information on cryptocurrencies, information about their appearance may not correspond to the dates of the ICO.)
Leaders and chasing cryptocurrency world
Cryptocurrency today is so much that it becomes difficult to distinguish between sensible and doubtful. How can you determine which ones are worthy of attention? Here you have one of the metrics: the total volume of exchange transactions for the last week.
-- 1200+ crypto currencies by total transaction volume (in btc) over the last monthSELECT'BTC'as currency_code,
sum(b.volume_currency) as total_volume_in_usd
FROM btc_prices b
WHERE b.currency_code = 'USD'ANDnow() - date(b.time) < INTERVAL'8 day'GROUPBY b.currency_code
UNIONSELECT 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 ONdate(c.time) = date(b.time)
WHERE c.volume_btc > 0AND b.currency_code = 'USD'ANDnow() - date(b.time) < INTERVAL'8 day'ANDnow() - date(c.time) < INTERVAL'8 day'GROUPBY c.currency_code
ORDERBY total_volume_in_usd DESC; currency_code | total_volume_in_usd
---------------+---------------------
BTC | 2040879023.54
ETH | 1617388472.94011
LTC | 287613541.293571
XRP | 269417667.514443
ETC | 165712729.612886
ANS | 126377042.5269
SC | 111623857.796786
DASH | 86875922.3588143
ZEC | 78836728.2129428
BTS | 69459051.5958428Total volume of transactions for 10 main cryptocurrencies in US dollars for the last week (descending)
Total volume of transactions for 10 main cryptocurrencies in US dollars for the last week (descending)
A small explanation of this request: data on BTC and other cryptocurrencies live in different tables. Therefore, we have to combine these two queries with UNION. Earlier, we also decided that we want to receive quotes in fiat currencies (for example, in dollars), and not in BTC. Therefore, the second half of the query combines the data with a BTC table for converting BTC to USD.
Leaders in terms of operations were, oddly enough, Bitcoin and Ethereum. But the following members of the charts - Litecoin (LTC) , Ripple (XRP) , and Ethereum Classic (ETC)go almost on equal footing. Litecoin has been on the market for five years, is almost identical to Bitcoin and is often considered a key player in the market. Ripple, which is positioned as a bank coin for representatives of the international commercial market and works for a more specific audience, is also considered a promising and gaining momentum. It is also interesting that in the top five there is not only ETH, but also ETC, which allows us to say that the market today is strongly focused on Ethereum.
The most profitable cryptocurrencies
Another way to “sweep through” a long list of cryptocurrencies is to analyze their profitability, for example, the indicator of total daily profit. In our set there are price data for more than 1200 cryptocurrencies. If you look at the largest rate increase per day, you can identify the leaders of intraday trading.
-- Top crypto by daily return, by daySELECTtime,
last(currency_code,daily_factor),
max(daily_factor)
FROM (
SELECT currency_code,
time,
closing_price,
lead(closing_price) over (partitionBY currency_code ORDERBYtimeDESC) AS prev_day_closing_price,
closing_price / lead(closing_price) over (partitionBY currency_code ORDERBYtimeDESC) AS daily_factor
FROM crypto_prices) q
GROUPBYtimeORDERBYtimeDESC; time | last | daily_return
------------------------+--------+------------------
2017-06-26 20:00:00+00 | CIN | 40.1428571428571
2017-06-25 20:00:00+00 | KC | 38
2017-06-24 20:00:00+00 | VOYA | 14.2747252747253
2017-06-23 20:00:00+00 | PAY | 3.18506315211422
2017-06-22 20:00:00+00 | YOVI | 119.607843137255
2017-06-21 20:00:00+00 | ION | 7.97665369649805
2017-06-20 20:00:00+00 | TES | 5.25157232704403
2017-06-19 20:00:00+00 | KNC | 150000
2017-06-18 20:00:00+00 | ZNY | 22.5217391304348
2017-06-17 20:00:00+00 | YOVI | 22.0590746115759
2017-06-16 20:00:00+00 | LTD | 9.50207468879668
2017-06-15 20:00:00+00 | AMIS | 168758.782201405
2017-06-14 20:00:00+00 | JANE | 6
2017-06-13 20:00:00+00 | YOVI | 690.636254501801
2017-06-12 20:00:00+00 | U | 5.21452145214522
2017-06-11 20:00:00+00 | JANE | 6
2017-06-10 20:00:00+00 | WGO | 3.58744394618834
2017-06-09 20:00:00+00 | BNT | 5000
2017-06-08 20:00:00+00 | XNC | 52.7704485488127
2017-06-07 20:00:00+00 | CBD | 14.3243243243243
2017-06-06 20:00:00+00 | CC | 72
2017-06-05 20:00:00+00 | BLAZR | 7.38461538461538
2017-06-04 20:00:00+00 | GREXIT | 13.0833333333333
2017-06-03 20:00:00+00 | EPY | 4.29880478087649
2017-06-02 20:00:00+00 | YOVI | 1257.67790262172
2017-06-01 20:00:00+00 | FCN | 8.57142857142857
2017-05-31 20:00:00+00 | EPY | 348.611111111111
2017-05-30 20:00:00+00 | BST | 14.1441860465116
2017-05-29 20:00:00+00 | FCN | 45.3086419753086
2017-05-28 20:00:00+00 | NOO | 56536.231884058The highest rates of cryptocurrency intraday price difference (in descending date)
Let's define the cryptocurrency with the largest daily profitability indicator. To calculate the daily profit, we will use the window function again, and to search for the cryptocurrency that brought the most income on each single day we use the last functionfrom the TimescaleDB set.
Conclusion over the past three months shows the quantitative superiority of AMIS (a 168-fold increase in value on June 15). This cryptocurrency showed the biggest increase in 15 different days. However, after looking more closely at it, we note that high growth is due to equally high price fluctuations: the cost of AMIS is often rolled back to zero level after each increase.
The closing price of the day for AMIS in the last five months.
Another leader of this sample, YOVI, showed the best result 3 times, but is also subject to similar unreliable trends as AMIS:
The closing price of the day for YOVI in the last five months
Despite the instability of this pair of trends, they nevertheless, they look more promising compared to ETH, the cost of which steadily dropped in the first year of its existence (2015):
ETH day closing price for 2015
(Repeated disclaimer: TimescaleDB does not support any of these cryptocurrencies and is not responsible for your investment in them and any possible losses associated with them.)
So, it turns out that money is growing ... on Merkle trees ?
In this material, we made several conclusions from an open data set on cryptocurrency, demonstrating the strengths of PostgreSQL and TimescaleDB. Nevertheless, it should be remembered that the cryptocurrency market will inevitably change next month, next week or even tomorrow.
However, if you want to independently study this set and carry out your analysis, the appropriate instruction for downloading data and installing TimescaleDB is available at your service .
If you want to learn more about TimescaleDB and how it improves PostgreSQL's efficiency in working with time series, we recommend that you familiarize yourself with the technical post .