# A Query da Fortuna -- Modo Hard Bitcoin está em alta! Segundo nosso amigo, o Estagiário Anarquista, criptomoedas são o próximo plano existencial da raça humana. E como vocês não querem ficar de fora desta jornada mística, vocês vão precisar compar algumas ~~tulipas~~ criptomoedas. No entanto, como bons analistas que vocês são, vocês querem tomar uma decisão acertada no investimento do seu rico dinheirinho. Este estudo de caso vai guiar vocês com as suas primeiras análises exploratórias na direção do ~~empobrecimento certo~~ sucesso. A matéria bruta de toda análise são dados. Por sorte, no seu caso, todos os dados já foram processados da internet para um arquivo CSV por mim. Este arquivo será mandado por Slack. Ele contem informações para um gráfico [OHLC](https://en.wikipedia.org/wiki/Open-high-low-close_chart ) de alguns pares de moedas de uma certa bolsa de criptomoedas no passo de *1 minuto*. Dê uma explorada nas poucas primeiras linhas do arquivo. Toda linha começa com duas colunas correspondentes ao par; elas estão listadas na ordem _cota_ (a moeda cujo preço estamos expressando) e _base_ (a moeda de referência). Depois disso, temos um *timestamp* (número de segundos desde `1970-01-01`) e os valores de _abertura_, _alta_, _baixa_, _fechamento_ e _volume_ do período. Para este estudo de caso, não vamos usar volume porque esse dado está comprometido. Sua primeira tarefa é a segunte: crie uma base de dados chamada `casestudy` na sua instância de PostgreSQL local. Em seguida, crie uma tabela chamada `coin_data` que deverá conter o conteúdo *validado* do CSV. Esta tabela deverá: * conter todos os campos do CSV e nada mais. * definir sua chave primária (dica: ela é _composta_). * definir os _constraints_ de unicidade, de não-nulabilidade e de checagem que os dados exigem. (e.g.: a máxima de um certo minuto não pode ser menor que o fechamento, pode?) Talvez... só talvez... eu tenha propositalmente corrompido algumas linhas do CSV porque eu sou mau. Não deixe que essas linhas entrem na sua tabela! Desta parte, você deverá entregar o comando `create table` que definine a tabela, mais os comandos de criação `create index` de quaisquer índices que você julgar importante para a execução das próximas análises. Isso ainda não é tudo. Vamos precisar de mais uma pequena tabelinha para nos dizer quais das moedas são criptomoedas, em oposição à que são moedas _fiat_ (moedas "de verdade"). Execute os seguintes comandos: ```sql 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; ``` Iso vai definir uma tabela que lista o nome de todas as criptomoedas. É claro, esta tabela vai ser útil mais para a frente. Ok! Então, com tabelas tão bonitas quanto estas, vamos dar um brincadinha básica com os dados. Faça o seguinte, como um primeiro passo: tente descobrir o OHLC para cada par na escala de *1 dia*, não mais na escala de *1 minuto*. Como você faria isso? Para essa escala de tempo, você deverá, _em uma query só_ (vale CTEs ilimitadas) responder as seguintes perguntas para *cada* par de moedas do tipo criptomoeda / _fiat_ (nem todos os pares têm este formato): * Qual o maior preço _de abertura,_ sendo abertura na meia-noite de cada dia, GMT? * Qual a maior variação relativa intra-diária? * Qual o menor preço neste ano? E qual o maior? (use `case ... when ... end` e `null`s ao seu favor.) Muito bem, por fim, vamos ver quanto de dinheiro você poderia ter ganho em uma certa época investindo em Bitcoin com base em dólar. Suponha que você tenha entrado na brincadeira com 1kUSD em '2018-08-01' e quer saber qual é o máximo de dinheiro que esses seus 1kUSD convertidos em Bitcoin poderiam ter obtido em dólar. Sobre essa situação, * Monte a query que retorna o maior valor do Bitcoin contra o dólar a partir dessa data. * Crie um índice para dar aquela acelerada na sua query. Leitura fortemente recomendada: * [OHLC charts](https://en.wikipedia.org/wiki/Open-high-low-close_chart) * [How to convert from Unix epoch to date?](https://stackoverflow.com/questions/16609722/postgresql-how-to-convert-from-unix-epoch-to-date) * [Convert Timestamp without timezone to integer Postgres](https://stackoverflow.com/questions/28155683/convert-timestamp-without-timezone-to-integer-postgres) * [`insert into ...`](http://www.postgresqltutorial.com/postgresql-insert/) * [`COPY`](http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/)