# The query of fortune

Bitcoin is the future of humanity! Don't believe me? Well, it doesn't matter as long as you feel that fear of missing out. People are getting rich with this simple trick, so why shouldn't you, right? Well, my friend, today is your lucky day! This case study will assist you with your first exploratory analyses into this world of ~~sure poverty~~ richness. So, grab your tulips and Enron actions and let's begin!

The raw material of every analysis is data. Luckily for your, all data have already been neatly processed out of the wild, wild web into a cozy CSV by me. This file is attached to this case study and please, warn me if you have any trouble opening it. This CSV file contains information on a [OHLC chart](https://en.wikipedia.org/wiki/Open-high-low-close_chart) for some trade pairs at the step of *1 minute*. Take a look at the first few line of the file. Every line starts with two columns corresponding to the *quote* and *base* of the trade pair respectively. After that, we have a *Unix timestamp* in seconds and the values of opening, high, low, close and volume for the corresponding *1 minute* period. The "volume" columns has been compromised and so we are not going to use it.

Yout first task is as follows: create a database called `casestudy` in your local PostgreSQL instance. If you don't have one, you can get the latest version at [the PostgreSQL website](https://postgresql.org); it's open source. After that, create a table called `coin_data` which should contain the *validated* content of the CSV. This table should:
* contain all fields (columns) defined in the CSV and nothing else.
* define a primary key.
* define all `unique`, `not null` and `check` which you find necessary.
* define important indexes which you find relevant.
Maybe, just maybe... I might have purposefully corrupted some CSV lines because I am evil. Do not let these lines make their way to you table!

For this first part, you must deliver the `create table` which defines the table along with the `create index` commands that you find relevant for your analyses.

But this is not all! We will still need one more tiny table to tell us which currencies are are cryptocurrencies and which are _fiat_ currencies. For this, just execute the following commands:

create table crypto_currencies (currency text primary key);
insert into crypto_currencies (currency) values
    ('BTC'), -- bitcoin
    ('BCH'), -- bitcoin cash
    ('BTG'), -- bitcoin gold
    ('ETH'), -- ethereum
    ('XLM'), -- lumen
    ('XRP'); -- ripple
grant select on crypto_currencies to public;

This will define a table that lists the code of all cryptocurrencies. Of course, this table will be useful later on.

Ok! So, with such beautiful tables as these, let's have some basic database fun with the data. Do the following, as the first step: try to find out the OHLC series at the scale of *1 day*, instead of *1 minute*. How would you do that? For this timescale, you should, _in a single SQL statement_ answer the following questions for each currency pair of the form crypto / _fiat_ (remember that not all pairs are in this format):
* What is the greatest _open_ price, with opening being defined as midnight of each day, GMT?
* What is the greatest relative intra-day variation?
* What was the smallest price of the cryptocurrency this year? And the greatest?

Very well, lastly, let's see how much money you could have earned during a given period investing on Bitcoin with dolar as a base (the BTCUSD pair). Suppose you have entered the game with 1kUSD in `2018-01-01` and have just held to your Bitcoins until they became most valuable and _immediately_ reverted them all back to dollar (good luck with pulling that off in real life!). About this scenario,
* Build the query that returns the greatest value of the Bitcoin against the dollar since that date. Then, find what would be your percentage return over investment (ROI).
* Create an index that will accelerate your query. You can use the `EXPLAIN` keyword to get a hint of what is going on.

You can search the internet to solve this case study. Just make sure the answers are your own (we know a cheater when we see one). Don't forget to hand your answers in before the due date in a easily readable format. By the way, the data provided here is public and you can use it after this case study for any purposes you see fit. If you want to get rich trading Bitcoins, go ahead. I dare you!