In Part 1, we discussed general information about decentralized and centralized exchanges (DEX and CEX) and the differences between them in terms of market diversity, security, privacy, and scalability. If you want to know more or need to review, please take a look at Part 1.

DEX is a new product built on blockchain technology  that provides DeFi services. Due to its novel operating system, there have been many concerns surrounding it. Therefore, it is worth exploring data analytics to gain an accurate understanding of its performance.

Now, let's get our hands dirty.

Let's start with a broad high level objectives

• I want to make a dashboard - for viewers who have no or a little knowledge of DEX - to learn more about what the DEX is; and

• I want to make a dashboard - for viewers who have no or a little knowledge of DEX - to learn how the it perform itself; and

• I want to make a dashboard - for viewers who have no or a little knowledge of DEX - to learn how it performs compared to other DEX.

OBJECTIVE 1 - DEX INTRODUCTION

I personally spend some time on several popular forums to find out what users really want to know about the topic. Here are some findings related to what users want to know about DEX:

• The core mechanism of DEX

• The comparison between DEX and CEX

• Examples of DEX

• The most DEX used

• The benefit of using DEX

The answers for all above questions can be found in the DEX and CEX Introduction (Part 1).

OBJECTIVE 2 - DEX PERFORMANCETo learn about Dex Performance, we cover:

• DEX Transactions and Volume: daily, weekly, monthly

• DEX Transactions and Volume Growth Rate: daily, weekly, monthly

• DEX Transactions and Volume over time

• New vs Returning Users

OBJECTIVE 3: COMPARING THE PERFORMANCE OF EACH DEXTo gain insight into the performance of each DEX compared to each other, several KPIs are recommended:

• Market share by transactions and volume on a daily, weekly, and monthly basis

• Ranking of all DEXs based on volume and transactions on a daily, weekly, and monthly basis

Here is high level objectives map and dashboard plan:

You can find the link to the report at the end of this post. If you wish to share it, please include a credit or link to my report and this post. Thank you for your interest and cooperation.

OBJECTIVE 2 - DEX PERFORMANCE

• DEX Daily/Weekly/Monthly Volume

  select sum(usd_amount)/1e9 as billion_value from dex.trades
  where block_time > now() - interval '24' hour/'7' day/'30' day
  and category = 'DEX'

• DEX Daily/Weekly/Monthly Volume Growth Rate

  With diff_value as (select a.billion_value, b.a_billion_value from (
  	select sum(usd_amount)/1e9 as billion_value from dex.trades
  	where block_time > now() - interval '24' hour and category = 'DEX')
  JOIN 
  	(select sum(usd_amount)/1e9 as a_billion_value from dex.trades 
  	where block_time > now() - interval '48' hour and category = 'DEX')
  on 1=1)
  
  select round(((2*billion_value - a_billion_value)/(a_billion_value - billion_value))*100,2) as growth from diff_value

=> There has been a slight increase in DEX compared to the previous day and week; however, this month has experienced a strong drop of more than 50% in volume compared to last month.

• DEX Volume Over Time

  select date_trunc('month', block_time) as month, sum(usd_amount)/1e9 as billion_value from dex.trades
  where category = 'DEX'
  group by 1

=> From the middle of 2021, we have witnessed an increase in the volume of all DEXs. At times, we have reached a peak of over $100 billion in total. Now, we have seen a huge drop from the peak. However, we can still see a growing trend in total volume over time.

Now let's take a look at transaction figures.

• DEX Daily/Weekly/Monthly Transactions

  select count(*) from dex.trades
  where block_time > now() - interval '24' hour and category = 'DEX'

• DEX Daily/Weekly/Monthly Transaction Growth Rate

  with diff_value as (select a.volume_24, b.volume_48 from (select cast(count(*) as NUMERIC) as volume_24 from dex.trades
  where block_time > now() - interval '24' hour and category = 'DEX') as a
  JOIN
  (select cast(count(*) as NUMERIC) as volume_48 from dex.trades
  where block_time > now() - interval '48' hour and category = 'DEX') as b
  on 1=1)
  
  select round(((2*volume_24 - volume_48)/(volume_48 - volume_24))*100,2) as growth from diff_value

=> Even though there has been an increase in transactions this week compared to last week, the number of all transactions monthly is decreasing.

• DEX Transactions Over Time

  select date_trunc('month', block_time) as month, count(*) as total_tranctions from dex.trades
  where category = 'DEX'
  group by 1

=> The number of transactions per month has also experienced a decrease. DEX used to have over 3 million transactions per month from June 2021 to the end of 2022, which is quite impressive.

• DEX % New vs. Returning Monthly Active Users (MAU)

  With orders as (select date_trunc('month', block_time) as month, tx_from as users from dex."trades")
  , mau as (select date_trunc('month', block_time) as month, count(distinct tx_from) as mau from dex."trades"
   group by 1)
  , buyer_cohort as (select users, min(month) as cohort from orders
   group by 1)
  , cohort_agg as (select cohort, count(*) as cohort_size from buyer_cohort
   group by 1)
  , comp_table as (select c.cohort, c.cohort_size as new, m.mau - c.cohort_size as returning from cohort_agg as c
  LEFT JOIN mau m on c.cohort = m.month)
  
  select * from comp_table

=> We can see that users keep returning.

• DEX has seen a leap in both volume and number of transactions since 2020. Recently, these indicators have experienced a decrease, perhaps because we are in a bear market. However, the figures are still higher than those before 2020.

• Users keep returning to use DEX. There is a fluctuation over time, however, you can clearly see the growing trend of returning users.

OBJECTIVE 3 - HOW DID EACH OF DEX PERFORM COMPARED TO EACH OTHER?

• DEX Monthly Transactions and Volume

  select project, count(project) as number_of_transactions, date_trunc('month', block_time) as month from dex.trades
  where category = 'DEX'
  group by 1,3
  order by month DESC, number_of_transactions DESC
  
  select project, sum(usd_amount) as Volume, date_trunc('month', block_time) as month from dex.trades
  where category = 'DEX'
  group by 1,3

=> Uniswap, Curve, and DODO make up most of the DEX volume and number of transactions, especially Uniswap. It appears that Uniswap is the most popular DEX among users.

• Market share and rank of each DEX

  select row_number() over (order by c.volume desc) as Rank, c.project as Project, c.volume as "7-Day Volum ($)" from(select project, round(sum(usd_amount)) as volume from dex."trades" t
  where category  = 'DEX'and block_time > now() - interval '7 days'
  group by project
  order by volume desc) as c;

=> Looking at the DEX 24-hour, 7-day, and 30-day volumes, we can see that over 50% of the volume is from Uniswap. The second and third places belong to Curve and DODO, respectively; however, their combined volume is only around one-third of Uniswap's. This is an interesting finding that warrants further exploration in another post.

From mid 2021, we can see that DEXs have grown very quickly in terms of the number of transactions and volume. They are now attracting more and more new users and keeping them returning. Among DEXs, Uniswap, Curve, and DODO take over 75% of the market share. The link to the full report is available only for subscribed members.

It has been a long post.I hope you have had a fun time reading with me.If you are interested in this type of post, please leave a comment and subscribe for more to come.Thank you so much for your interest in math, technology, and science.

Charlotte.