[SQL] 실전 With as, Case when then, Join on 구문 (DUNE)

온 체인 데이터를 다루는 DUNE을 통해 SQL의 with as, case when then, left on 구문이 어떻게 사용되는지 알아보자.

 

/* 1번 테이블 */
WITH
  ranked_evt AS (
    SELECT
      d.tx_hash,
      COUNT(*) as cnt
    FROM
      dex."trades" d
    WHERE
      (
        tx_to = '\x881d40237659c251811cec9c364ef91dc08d300c'
        OR tx_to = '\xe66b31678d6c16e9ebf358268a790b763c133750'
      )
      AND usd_amount IS NOT NULL
      AND date_trunc('week', block_time) >= '2021-06-28'
    GROUP BY 1
  )
  
  
 /* 2번 테이블 */
  SELECT
  date_trunc('week', block_time),
  CASE
    WHEN tx_to = '\x881d40237659c251811cec9c364ef91dc08d300c' THEN 'Metamask'
    WHEN tx_to = '\xe66b31678d6c16e9ebf358268a790b763c133750' THEN 'Coinbase'
    ELSE 'N/A'
  END,
  SUM(usd_amount / b.cnt) AS "Volume",
  SUM(
    (usd_amount / b.cnt) * (
      CASE
        WHEN tx_to = '\x881d40237659c251811cec9c364ef91dc08d300c' THEN 0.00875
        WHEN tx_to = '\xe66b31678d6c16e9ebf358268a790b763c133750' THEN 0.01
        ELSE 0
      END
    ) / (
      1 - (
        CASE
          WHEN tx_to = '\x881d40237659c251811cec9c364ef91dc08d300c' THEN 0.00875
          WHEN tx_to = '\xe66b31678d6c16e9ebf358268a790b763c133750' THEN 0.01
          ELSE 0
        END
      )
    )
  ) AS "Revenue",
  
  COUNT(DISTINCT tx_from) AS "Traders",
  COUNT(DISTINCT dex."trades".tx_hash) AS "Trades",
  SUM(usd_amount / b.cnt) / COUNT(DISTINCT tx_from) AS "Volume Per Trader",
  CAST(COUNT(DISTINCT dex."trades".tx_hash) AS Float) / CAST(COUNT(DISTINCT tx_from) AS Float) AS "Trades Per Trader"

FROM
  dex."trades"
JOIN ranked_evt b ON dex."trades".tx_hash = b.tx_hash
WHERE
  (
    tx_to = '\x881d40237659c251811cec9c364ef91dc08d300c'
    OR tx_to = '\xe66b31678d6c16e9ebf358268a790b763c133750'
  )
  AND date_trunc('week', block_time) >= '2021-06-28'
GROUP BY
  1,
  2

1번 테이블을 보면

- WITH AS () 구문으로 ranked_evt의 새로운 테이블을 만들었다.  

ranked_evt의 WHERE 조건문 내용이 usd_amount IS NOT NULL이다. 이를 통해 usd_amount의 값이 비어있는 않은 값들만 뽑아냈다. 뒤에서 usd_amount / b.cnt 나누는 식이 있는데 cnt의 값이 없다면 에러를 내기 때문이다.

 

- WHERE (조건절)을 만족하는 경우  dex."trades"에서 tx_hash과 조건을 만족하는 tx_hash를 카운트해서 2개의 열을 만들었다. 

tx_hash

cnt (count)

 

2번 테이블을 보면

- "Volume", "Revenue", "Traders", "Trades", "Volume Per Trader", "Trades Per Trader"의 열(column)이 생성했다.

Volume

Revenue

Traders

Trades

Volume Per Trader

Trades Per Trader

JOIN ranked_evt b ON dex."trades".tx_hash = b.tx_hash을 통해 트랜잭션 해시가 동일한 ranked_evt 테이블을 b로 가져와 2번 테이블에 붙였다. 칼럼이 어떤 내용으로 구성됐는지 살펴보겠다.

 

-  SUM(usd_amount / b.cnt) AS "Volume" 은 총 거래금액이다.

DUNE 공식 문서에 있는 usd_amount = USD value of the trade at time of execution을 통해 usd_amount가 거래를 실행하는 시점의 USD 값임을 알 수 있다. block_time을 기준으로 행(row)이 구성되기 때문에 usd_amount는 하나의 블록이 가지는 usd 값이 된다. 여러 개의 트랜잭션이 모여 있어 하나의 블록이 되므로 usd_amount를 1번 테이블에서 구했던 조건을 만족하는 tx_hash의 갯수로 나눠줘야  tx_hash 당 거래금액이 나오게 된다. 

 

-  "Revenue"는 총 수익으로 (트랜잭션 당 거래금액 * 수수료 / 1 - 수수료)의 식으로 구성된다.

CASE WHEN THEN ELSE END의 구조를 사용했다

CASE WHEN a THEN a1 ELSE c END // a이면 a1로 바꾸고, 그 외에는 c로 바꾼다. 메타 마스크의 스왑 수수료가 0.00875이고, 코인베이스의 스왑 수수료가 0.01이기 때문에 각각의 트랜잭션 주소에 따라 수수료를 부과하였다.

 

출처

https://dune.com/robkasinow/Metamask-In-App-DEX-Swaps

Metamask vs Coinbase Swaps