Instadapp User Wallets

instadapp-userwallets.js

import { CREATED_EVENT, INSTADAPP_REGISTRY } from "./constants";

export const CDAI_CONTRACT = "\\xf5dce57282a584d2746faf1593d3121fcac444dc"
export const CETH_CONTRACT = "\\x4ddc2d193948926d02f9b1fe9e1daa0718270ed5"


cube(`UserWallets`, {
    sql: `SELECT
  block_id,
  block_signed_at,
  block_height,
  '0x' || encode(tx_hash, 'hex') AS "tx_hash",
  "tx_offset",
  "log_offset",
  '0x' || substr(encode(e.topics[1], 'hex'), 25) AS "logged_sender",
  '0x' || substr(encode(e.topics[2], 'hex'), 25) AS "logged_owner",
  '0x' || substr(encode(e.data, 'hex'), 25) AS "logged_proxy",
  e.data AS "log_data",
 COALESCE(b.balance, 0) as eth_balance
 
FROM
  live.block_log_events e
 LEFT JOIN 
  (SELECT 
   account, value AS balance FROM batch.trace_balance_events  
   order by block_id desc, emit_seq desc 
   limit 1
  ) b
  ON substr(e.data, 13) = b.account  

    WHERE
e.topics[1] = '${CREATED_EVENT}'
AND e.sender = '${INSTADAPP_REGISTRY}'  
` ,

    joins: {
        UserTransactions: {
            relationship: `hasMany`,
            sql: `${UserTransactions}.to_address = ${UserWallets}.logged_proxy`
        },
        CompoundDai: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundDai}.key_path and ${CompoundDai}.rn = 1`
        },
        CompoundEth: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundEth}.key_path and ${CompoundEth}.rn = 1`
        },
        CompoundBat: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundBat}.key_path and ${CompoundBat}.rn = 1`
        },
        CompoundRep: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundRep}.key_path and ${CompoundRep}.rn = 1`
        },
        CompoundUsdc: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundUsdc}.key_path and ${CompoundUsdc}.rn = 1`
        },
        CompoundWbtc: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundWbtc}.key_path and ${CompoundWbtc}.rn = 1`
        },
        CompoundZrx: {
            relationship: `hasOne`,
            sql: `('"' || encode(substr(log_data, 13), 'hex') || '"')::jsonb = ${CompoundZrx}.key_path and ${CompoundZrx}.rn = 1`
        }
    },

    measures: {
        count: {
            type: `count`,
            drillMembers: [Created, walletAddress, walletLink]
        },
        growth_rate: {
            type: `number`,
            format: `percent`,
            sql: `COALESCE(  (${count} * 1.0 / lag( NULLIF( ${count}, 0) , 1) OVER ()) - 1.0 , 0)`
        },
        eth_balance: {
            type: `sum`,
            sql: `eth_balance`,
            format: `currency`,
        }
    },

    dimensions: {
        blockId: {
            sql: `block_id`,
            type: `number`,
            primaryKey: true
        },
        Created_date: {
            sql: `date_trunc('day', ${CUBE}.block_signed_at)`,
            type: `time`
        },
        Created_week: {
            sql: `date_trunc('week', ${CUBE}.block_signed_at)`,
            type: `time`
        },
        Created_month: {
            sql: `date_trunc('month', ${CUBE}.block_signed_at)`,
            type: `time`
        },
        Created: {
            sql: `${CUBE}.block_signed_at`,
            type: `time`
        },
        walletAddress: {
            sql: `logged_proxy`,
            type: `string`
        },
        walletLink: {
            sql: `'https://etherscan.io/address/' || logged_proxy`,
            type: `string`,
            format: {
                label: `Etherscan`,
                type: `link`
            }
        }
    }
});