Crypto Analytics Project

Project Introduction

I've been assigned the task of analyzing cryptocurrency data as a data engineer. My manager has provided CSV files containing crucial information required to address inquiries from company executives. This project will walk you through the process of loading and analyzing the data, step by step, to derive insights and answer pertinent business questions.

Table of Contents

• Task 1 - Setting up the database.
• Task 2 - Answering business questions.
  • 1. How many buy and sell transactions are there for Bitcoin?
  • 2. For each year, calculate the following buy and sell metrics for bitcoin:
  • 3. What was the monthly total quantity purchased and sold for Ethereum in 2020?
  • 4. Who are the top 3 members with the most bitcoin quantity?
• Tools Used
• Project Structure
• Acknowledgement

Task 1 - Setting up the database.

Using psql (an interactive terminal-based front-end to PostgreSQL), I created a user named 'cryptoverse_admin' with the attributes CREATEDB and CREATEROLE.

This command will create a new user with the specified privileges, along with a password.

CREATE USER cryptoverse_admin WITH CREATEDB CREATEROLE PASSWORD **********;

Using the user from the first step, I created a database called metaverse.

This command creates a new database named metaverse with cryptoverse_admin as its owner.

CREATE DATABASE metaverse OWNER cryptoverse_admin;

Below shows the list of databases with their respective owners. In psql, you can get the list of databases using the \l meta-command.

Peep metaverse and cryptoverse_admin. 😉

I proceeded to create a schema in the metaverse database called raw.

This command will create a new schema named raw in the metaverse database.

CREATE SCHEMA raw;

Below shows the list of all database schemas with their respective owners. In psql, you can get the list of all schemas using the \dn meta-command.

Peep the raw schema and cryptoverse_admin. 😉

Data Importation

I used DBeaver UI, a SQL client software application and a database administration tool, to add the Members, Prices, and Transactions tables.

Sneak peak into the various table imported tables.

Members table

The Members table contains information about users registered on the cryptocurrency platform.

Prices table

The Prices table records historical price data for various cryptocurrencies.

Transactions table

The Transactions table tracks all transactions executed on the cryptocurrency platform.

Task 2 - Answering business questions.

1. How many buy and sell transactions are there for Bitcoin?

-- Number of buy and sell transactions for Bitcoin

SELECT 
    txn_type, 
    COUNT(txn_type) AS transaction_count
FROM 
    raw.transactions
WHERE 
    ticker = 'BTC'
GROUP BY 
    txn_type;

Result of SQL Query

txn_type	transaction_count
SELL	2,044
BUY	10,440

2. For each year, calculate the following buy and sell metrics for bitcoin:

• Total transaction count
• Total quantity
• Average quantity

/* For each year, calculate the following buy and sell metrics for Bitcoin:
a. Total transaction count
b. Total quantity
c. Average quantity */

SELECT 
   EXTRACT(YEAR FROM txn_date::DATE) AS txn_year,
   txn_type,
   COUNT(txn_id) AS txn_count,
   SUM(quantity) AS total_quantity,
   AVG(quantity) AS average_quantity
FROM 
   raw.transactions
WHERE 
   ticker = 'BTC'
GROUP BY 
   txn_year, txn_type
ORDER BY 
   txn_year, txn_type;

Result of SQL Query

3. What was the monthly total quantity purchased and sold for Ethereum in 2020?

-- Monthly total quantity purchased and sold for Ethereum in 2020

SELECT 
    EXTRACT(MONTH FROM txn_date::DATE) AS calendar_month,
    SUM(quantity) FILTER (WHERE txn_type = 'BUY') AS buy_quantity,
    SUM(quantity) FILTER (WHERE txn_type = 'SELL') AS sell_quantity
FROM 
    raw.transactions
WHERE 
    ticker = 'ETH' 
    AND txn_date BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY 
    calendar_month
ORDER BY 
    calendar_month;

Result of SQL Query

4. Who are the top 3 members with the most bitcoin quantity?

-- Who are the top 3 members with the most bitcoin quantity?

with buy as(
select
	member_id,
	sum(quantity) as bought_btc
from raw.transactions
where txn_type = 'BUY' and ticker = 'BTC'
group by member_id
)

, sell as (
select 
	member_id
	,sum(quantity) as sold_btc
from raw.transactions
where txn_type = 'SELL' and ticker = 'BTC'
group by member_id
)

select 
	members.first_name
	,buy.bought_btc - sell.sold_btc as total_quantity
from buy 
full join sell using (member_id)
join raw.members
using (member_id)
order by total_quantity desc
limit 3;

Result of SQL Query

Top 3 Members with the Most Bitcoin Quantity.

First Name	Total Quantity
Nandita	3775
Leah	3649
Ayush	3554

Tools Used

• Dbeaver (version 24.0.0)
• SQL Shell (psql)

Project Structure

README.md: This file serves as the project documentation, providing an overview of the project, its purpose, and other relevant information.

crypto-analytics.py: This file contains a Python script with each SQL statement added to a variable representing the corresponding business question number.

crypto-analytics.sql: This file contains all the SQL statements written in the README.md file, with each SQL statement representing the corresponding business question.

members.csv: This file contains information about the members or users registered on the cryptocurrency platform.

prices.csv: This file contains historical price data for various cryptocurrencies.

transactions.csv: This file tracks all transactions executed on the cryptocurrency platform.

Acknowledgement

A heartfelt thank you to Altschool Africa for providing me with the necessary skillset to tackle this project.